March 11, 2010 at 10:00 am
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
----------------------------------------------------------------------------
March 11, 2010 at 10:07 am
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
March 11, 2010 at 10:22 am
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
----------------------------------------------------------------------------
May 27, 2010 at 11:13 am
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
----------------------------------------------------------------------------
May 27, 2010 at 11:28 am
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
May 27, 2010 at 12:16 pm
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
----------------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply