Automating the creation of a database

Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]

> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser

> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader
so that others may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties, and confers no rights.
========================================
==============

Automating a Backup and Restore across Servers

Hi,

I have been experiencing a problem with a job I am trying to automate. My team requires two instances of a db on development. Every week, db1 is detached and reattached as db1_copy, and a new copy of db1 from production is copied over. I have used cursors for the dropusers, addlogins, grantdbaccess, addroles, and addrolemembers aspects of the restore process. Also, we use sql authentication and not windows authentication. The issue is that when the agent encounters a minor issue, ie. a login that already exists in that db, or adding a rolemember to a role that is already there, I get an error. I have ensure that at that step, the job simply proceeds because it is a minor step in the process (it happens during the script execution when done manually but I can override this because I am present and just let it run in one step instead of many). Is there any way to do this without adding a multitude of steps?

Thanks,

MariaHi Maria,
try if putting the statement

SET XACT_ABORT OFF

in the TSQL-stream of the step solves your problem.

automaticall render as pdf after parameter selection

I am using reporting services and have a report that requires three
parameters. After the parameters are selected and the report is generated by
clicking the View Report button, I want the report to default to pdf view
instead of having to select that format and export after running it. Is this
possible? This way users can print or save from the pdf view without having
to take the extra step of exporting.
Thank you for your help!
SharlynI'm also interested in it.
tnx.
Tom.sql