create a stored procedure to create a database

  • I'd like to create a database using a stored procedure that accepts the database name. After some googling, it appears that the 'CREATE DATABASE' statement will not accept a variable as input (this was concerning SQL Server 2000). Is this true for 2k5? I've tried some things and keep getting an incorrect syntax error, but nothing specific about limitations on the 'CREATE DATABASE' command.

    If the above isn't true, how do I pass the database name into the procedure to be used as a file name, e.g.:

    create procedure uspCreate_Database @dbname varchar(30)

    AS

    CREATE DATABASE @db_name

    ( NAME = N@db_name, FILENAME = N'D:\MSSQL.1\MSSQL\DATA\''' + @db_name + '''.mdf' , SIZE = 2048KB , FILEGROWTH = 1024KB )

    Obviously, that doesn't work.

    Does anyone have a 'create database' stored procedure?

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Dan it's fairly simple, you just need to use dynamic SQL;

    I followed up on a similar thread where the user wanted a database and some specific roles to be created as well. that person wanted an end user to be able to create a database on demand, without giving them dbo permissions;

    this script example is much more than you asked for, but it might help:

    s---i had to do this to my server:

    ---ALTER DATABASE master SET TRUSTWORTHY ON;

    ALTER procedure MakeMeADatabase(@dbname varchar(128),@UserName varchar(128),@WithDevPriviledges int = 0)

    WITH EXECUTE AS 'dbo'

    AS

    DECLARE @SQLCmd varchar(max)

    BEGIN

    IF EXISTS(SELECT name FROM master.dbo.sysdatabases WHERE name = @dbname)

    BEGIN

    PRINT 'Database Already Exists,No Need To Create.';

    END

    ELSE

    BEGIN

    PRINT 'Creating Database.';

    set @SQLCmd = 'CREATE DATABASE ' + @DBName + ';';

    exec (@SQLCmd);

    END

    SET @SQLCmd='

    USE Whatever

    --create the Role for my Dev guys

    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverAdmins'' AND type = ''R'')

    BEGIN

    CREATE ROLE [WhateverAdmins]

    ALTER AUTHORIZATION ON SCHEMA::[db_ddladmin] TO [WhateverAdmins]

    ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverAdmins]

    ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverAdmins]

    END

    --create role for my normal users

    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''WhateverUsers'' AND type = ''R'')

    BEGIN

    CREATE ROLE [WhateverUsers]

    ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [WhateverUsers]

    ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [WhateverUsers]

    END

    --create the user if it does not exist yet

    IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N''MyDBUser'')

    CREATE USER [MyDBUser] FOR LOGIN [MyDBUser]

    --add the user to the role

    EXEC sp_addrolemember N''WhateverUsers'', N''MyDBUser''';

    If @WithDevPriviledges <> 0

    BEGIN

    SET @SQLCmd= @SQLCmd + '

    EXEC sp_addrolemember N''WhateverAdmins'', N''MyDBUser''';

    END

    SET @SQLCmd=Replace(@SQLCmd,'Whatever',@DBName);

    SET @SQLCmd=Replace(@SQLCmd,'MyDBUser',@UserName);

    PRINT @SQLCmd;

    EXEC (@SQLCmd); ----two roles should be in place now

    END

    GO

    MakeMeADatabase 'Special','bob'

    MakeMeADatabase 'Special','jeff',1

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks Lowell, makes a lot of sense. Didn't even think about doing it this way, either. This is goin' in the toolbox.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • I've finally finished my version of the stored procedure, and indeed, as you stated, Lowell, I need to change the TRUSTWORTHY property of the 'master' database to ON for this to complete. I tried adding that statement to the stored procedure but it complains that the user does not have permissions to alter the master database, even though I'm executing as 'dbo'. I don't want to leave my 'master' database in TRUSTWORTHY mode, either.

    If I have to manually issue the 'ALTER DATABASE master SET TRUSTWORTHY ON' command each time, I might as well just create the database myself, rather than having the user do it. Do you know of a way around this?

    Example:

    CREATE procedure [dbo].[uspTrustworthy]

    WITH EXECUTE AS 'dbo'

    AS

    DECLARE @SQLCmd varchar(max)

    BEGIN

    set @SQLCmd = 'ALTER DATABASE master SET TRUSTWORTHY ON'

    exec (@SQLCmd)

    END

    EXEC uspTrustworthy

    Results in:

    Msg 15247, Level 16, State 1, Line 1

    User does not have permission to perform this action.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.
  • Dan i poked around the net trying to find out the ramifications of why TRUSTWORTHY on master database might be a bad idea, and it just comes right back to EXECUTE AS and CLR stuff that are UNSAFE or EXTERNAL_ACCESS;

    i couldn't find anything that says no never do that, and i've already been using some CLR that needed unsafe permissions on some stuff on production.

    in my case, im not afraid that these are issues, but they might be in your case...

    maybe a scheduled job or service broker that scans a table for creating a database every minute?

    didn't try it but that would also take the permissions thing out of the picture, since the job would run as...whoever you assign, sa for example,.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Thanks again. I had done the same Googling considering the ramifications of the TRUSTWORTHY setting and saw similar results.

    Interesting idea with a monitoring job. I'll look at my options, just good to know that I wasn't missing something programmatically with the SQL statement.

    ----------------------------------------------------------------------------
    Sacramento SQL Server users group - http://sac.sqlpass.org
    Follow me on Twitter - @SQLDCH
    ----------------------------------------------------------------------------

    Yeah, well...The Dude abides.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply