|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Is there an easier way to programitcally create a new database and login userI'm thinking there MUST be an easier way to programmatically create a new
database and login than the following steps we are doing below. If so, I would appreciate any info you could provide. Thanks, Tony 1) connect to master using sa credentials 2) Issue a "Create database X" command. 3.) call sp_dboption to set arithabort to on 4) call sp_helplogins to see if the provided login already exists 5) if not, call sp_addlogin providing username, password, and defdb 6) if the login already exists, then call sp_grantdbaccess providing the new login 7) call sp_addsrvrolemember to add the login to securityadmin 8) call sp_changedbowner to the new login user Seems like there should be a much easier way to perform such a common task. Hi, Tony
You can use many languages to connect to SQL Server and run a few commands. If you want to do this on the server, you can pass a SQL script to OSQL. If you want to do this from another computer, the first/easiest way that comes to mind is using VBScript to call SQL-DMO methods. Write something like this in a .VBS file and double-click on it: Set srv = CreateObject("SQLDMO.SQLServer2") srv.Connect "ServerName", "sa", "YourPassword" srv.ExecuteImmediate "CREATE DATABASE X" srv.ExecuteImmediate "..." Razvan If you are doing this a lot... You should put the whole mess in a stored
procedure, and simply exec the sp. If the login is fixed...you could create what you need , then detach it... When you need a new one, copy the detached files, attach them as a new database name and go.. Otherwise there is no simpler way... -- Show quoteWayne Snyder MCDBA, SQL Server MVP Mariner, Charlotte, NC I support the Professional Association for SQL Server ( PASS) and it''s community of SQL Professionals. "Tony" wrote: > I'm thinking there MUST be an easier way to programmatically create a new > database and login than the following steps we are doing below. If so, I > would appreciate any info you could provide. > > Thanks, > Tony > > 1) connect to master using sa credentials > 2) Issue a "Create database X" command. > 3.) call sp_dboption to set arithabort to on > 4) call sp_helplogins to see if the provided login already exists > 5) if not, call sp_addlogin providing username, password, and defdb > 6) if the login already exists, then call sp_grantdbaccess providing the new > login > 7) call sp_addsrvrolemember to add the login to securityadmin > 8) call sp_changedbowner to the new login user > > Seems like there should be a much easier way to perform such a common task. > > > No, this isn't Staples, and there is no "Easy button." These tasks are
separated for a reason. However, you can probably simplify your life by creating a stored procedure that does: ALTER PROCEDURE dbo.CreateDatabase @dbName SYSNAME, @loginName SYSNAME, @password VARCHAR(16) = NULL -- login may already exist AS BEGIN SET NOCOUNT ON; -- check if DB already exists: IF DB_ID(@dbName) IS NOT NULL BEGIN RAISERROR('The database %s already exists.', 11, 1, @dbName); RETURN -1; END -- create db EXEC('CREATE DATABASE '+@dbName); -- error handling here -- check if login already exists, which does not require -- you to return the entire resultset of sp_helplogins! IF SUSER_SID(@loginName) IS NULL BEGIN -- if not, sp_addlogin EXEC master.dbo.sp_addlogin @loginame = @loginName, @passwd = @password, @defdb = @dbName; --- error handling here END -- this eliminates the need for sp_grantdbaccess, -- whether you just created the login or not: EXEC('EXEC '+@dbName+'.dbo.sp_changedbowner @loginame = '''+@loginName+''';'); -- error handling here END GO -- sample calls EXEC dbo.CreateDatabase N'foobar', 'foo', 'bar'; GO -- this will error out: EXEC dbo.CreateDatabase N'foobar', 'foo'; GO EXEC dbo.CreateDatabase N'foobar2', 'foo'; GO Of course, you'll need to go through and add your own error-handling, and perhaps extra code if you want more security / rolemember / srvrolemember stuff, or mapping logns <-> users ... just giving you a working skeleton to start with. A Show quote "Tony" <tony***@spacecommand.net> wrote in message news:O68%23yC1FGHA.216@TK2MSFTNGP15.phx.gbl... > I'm thinking there MUST be an easier way to programmatically create a new > database and login than the following steps we are doing below. If so, I > would appreciate any info you could provide. > > Thanks, > Tony > > 3.) call sp_dboption to set arithabort to on > 4) call sp_helplogins to see if the provided login already exists > 5) if not, call sp_addlogin providing username, password, and defdb > 6) if the login already exists, then call sp_grantdbaccess providing the > new login > 7) call sp_addsrvrolemember to add the login to securityadmin > 8) call sp_changedbowner to the new login user > > Seems like there should be a much easier way to perform such a common > task. > > > ALTER PROCEDURE dbo.CreateDatabase Sorry, you'd probably want CREATE there... it took a couple of tries to get it right. A My application runs at various client sites and I must create the database
via ADO.NET on demand and then populate it with a start up schema. I hadn't thought about creating a stored procedure and then executing it but rather I had the individual commands written in C#. Thanks for the valuable input, Tony Show quote "Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartreb.noraa> wrote in message news:uVYIiy3FGHA.1312@TK2MSFTNGP09.phx.gbl... > No, this isn't Staples, and there is no "Easy button." These tasks are > separated for a reason. However, you can probably simplify your life by > creating a stored procedure that does: > > ALTER PROCEDURE dbo.CreateDatabase > @dbName SYSNAME, > @loginName SYSNAME, > @password VARCHAR(16) = NULL -- login may already exist > AS > BEGIN > SET NOCOUNT ON; > > -- check if DB already exists: > IF DB_ID(@dbName) IS NOT NULL > BEGIN > RAISERROR('The database %s already exists.', 11, 1, @dbName); > RETURN -1; > END > > -- create db > EXEC('CREATE DATABASE '+@dbName); > -- error handling here > > -- check if login already exists, which does not require > -- you to return the entire resultset of sp_helplogins! > IF SUSER_SID(@loginName) IS NULL > BEGIN > -- if not, sp_addlogin > EXEC master.dbo.sp_addlogin > @loginame = @loginName, > @passwd = @password, > @defdb = @dbName; > > --- error handling here > > END > > -- this eliminates the need for sp_grantdbaccess, > -- whether you just created the login or not: > EXEC('EXEC '+@dbName+'.dbo.sp_changedbowner @loginame = > '''+@loginName+''';'); > -- error handling here > END > GO > > -- sample calls > EXEC dbo.CreateDatabase N'foobar', 'foo', 'bar'; > GO > -- this will error out: > EXEC dbo.CreateDatabase N'foobar', 'foo'; > GO > EXEC dbo.CreateDatabase N'foobar2', 'foo'; > GO > > > > Of course, you'll need to go through and add your own error-handling, and > perhaps extra code if you want more security / rolemember / srvrolemember > stuff, or mapping logns <-> users ... just giving you a working skeleton > to start with. > > > > A > > > > "Tony" <tony***@spacecommand.net> wrote in message > news:O68%23yC1FGHA.216@TK2MSFTNGP15.phx.gbl... >> I'm thinking there MUST be an easier way to programmatically create a new >> database and login than the following steps we are doing below. If so, I >> would appreciate any info you could provide. >> >> Thanks, >> Tony >> >> 3.) call sp_dboption to set arithabort to on >> 4) call sp_helplogins to see if the provided login already exists >> 5) if not, call sp_addlogin providing username, password, and defdb >> 6) if the login already exists, then call sp_grantdbaccess providing the >> new login >> 7) call sp_addsrvrolemember to add the login to securityadmin >> 8) call sp_changedbowner to the new login user >> >> Seems like there should be a much easier way to perform such a common >> task. >> >> > >  |
|||||||||||||||||||||||
Other interesting topics