April 9, 2009 at 12:36 pm
Here's my take on it. Has some injection-proofing (might not be enough).
CREATE DATABASE DBA ;
go
USE DBA ;
go
CREATE PROC DBCreate (@DBName_in SYSNAME)
AS
SET nocount ON ;
IF @DBName_in LIKE '%;%'
OR @DBName_in LIKE '%--%'
BEGIN
RAISERROR ('Possible injection attack detected.', 16, 1) ;
RETURN ;
END ;
IF EXISTS ( SELECT
*
FROM
master.sys.databases -- local server
WHERE
name = @DBName_in )
-- Must have created linked servers first.
-- If need be, can select from sys.servers where is_linked = 1, and use that
-- in dynamic SQL.
OR EXISTS ( SELECT
*
FROM
[LinkedServer1].master.sys.databases
WHERE
name = @DBName_in )
OR EXISTS ( SELECT
*
FROM
[LinkedServer2].master.sys.databases
WHERE
name = @DBName_in )
OR EXISTS ( SELECT
*
FROM
[LinkedServer3].master.sys.databases
WHERE
name = @DBName_in )
BEGIN
RAISERROR ('Database already exists on at least one linked server',
16, 1) ;
RETURN ;
END ;
DECLARE @sql NVARCHAR(MAX) ;
SELECT
@sql = 'create database ' + @DBName_in + ';'
EXEC (@SQL) ;
SELECT
@sql = 'use ' + @DBName_in + '; create user ' + CURRENT_USER
+ ' from login ' + CURRENT_USER + ';' ;
EXEC (@SQL) ;
Note that this assumes all the linked servers are SQL 2005/2008, not SQL 2000. If any are, the query to find if the database exists will need to be changed.
I set the whole thing to run in a database called DBA, since I don't like building code in the system databases.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 12:38 pm
Something that might make all of this a little simpler to manage would be to modify the model database so that it has the necessary roles already created in it, and then it's easy to make sure that the user can be easily added to them.
Can also populate model with any common items that all of these databases should have. Possibly some schemas, or base tables, maybe some synonyms, possibly a common function or two, that kind of thing. That way, each new database will have those properties already in it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 1:09 pm
GSquared
Your proc looks good but i get some errors as
Not all users can use select from sys.databases and so i want to exec proc as an id which is sysadmin.
and I dont want the proc to capture the current user, i want to explicitly give user name to assign fir reader and writer roles.
April 9, 2009 at 1:24 pm
Lowell (4/9/2009)
here's my version, where i figured i'd find and replace from an existing set of commands i know works fine:i greated a login named 'bob' with connect priviledges. this created the database, but fails when doing the rest...adding roles and users, then users to roles.
but i get an error stating this:
Msg 916, Level 14, State 1, Line 2
The server principal "sa" is not able to access the database "Special" under the current security context.
ALTER procedure MakeMeADatabase(@dbname varchar(128),@UserName varchar(128),@WithDevPriviledges int = 0)
WITH EXECUTE AS 'dbo'
...
This error probably means that you need "TRUSTWORTHY" set on the database that is holding the [MakeMeADatabase] proc. If you make it in master and run it from there I think that it will work.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 9, 2009 at 1:32 pm
Tara (4/9/2009)
GSquaredYour proc looks good but i get some errors as
Not all users can use select from sys.databases and so i want to exec proc as an id which is sysadmin.
and I dont want the proc to capture the current user, i want to explicitly give user name to assign fir reader and writer roles.
You should be able to add "With Execute As..." and whatever login has the right privileges (sysadmin) to it.
And it should be easy enough to add an input parameter for the desired username, or whatever other means you have for getting that.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 9, 2009 at 2:26 pm
GSquared pointed me in the right direction.
trustworthy was required, and i tweaked my version of the script until it works.
I created two new sql login users, "bob" and "jeff" with CONNECT to master privileges only.
try this:
--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
April 9, 2009 at 2:40 pm
So, do I get create for an assist at least? 😉
April 10, 2009 at 1:49 pm
GSquared
when i use
IF EXISTS ( SELECT name FROM master.sys.databases WHERE name = @DBName )
OR EXISTS ( SELECT name FROM Rev1.master.sys.databases WHERE name = @DBName )
OR EXISTS ( SELECT name FROM Rev2.master.sys.databases WHERE name = @DBName )
I get this error
Msg 15274, Level 16, State 1, Procedure DBCreateForSDB, Line 22
Access to the remote server is denied because the current security context is not trusted.
but if i use only from local server it works fine, i dont have any issues with linkserver too, it works fine.
also this part is not working for me
SELECT @sql= 'CREATE USER ' + @Username + ' FOR LOGIN ' + @Username + ';
EXEC sp_addrolemember N''db_datareader'', '+@Username+'
EXEC sp_addrolemember N''db_datawriter'', '+@Username+'
EXEC sp_addrolemember N''db_ddladmin'', '+@Username+'';
April 10, 2009 at 4:27 pm
Though i put in
WITH EXECUTE AS 'State\TPatel' which has sysadmin role still i get this error
Msg 262, Level 14, State 1, Line 1
CREATE DATABASE permission denied in database 'master'.
April 13, 2009 at 6:54 am
On the access error, you need to look at the linked server set up. It needs to be logging in with rights that can select from those tables.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 13, 2009 at 6:55 am
What error message does the second problem give you?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 13, 2009 at 7:35 am
Now everything looks working except that when i put in
EXECUTE as User its not working.
I doubt if this feature works all the time as this is the new in sql 2005.
April 13, 2009 at 3:18 pm
Are you puting the username in single-quotes? If so, don't.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2009 at 8:45 am
yes in single quotes like
WITH EXECUTE AS 'StateME\LTara'
if not then how?
April 14, 2009 at 10:41 am
can i use
WIT HEXECUTE AS 'sysadmin'
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply