March 25, 2009 at 8:24 am
Hi
My business case
We want to start with "security" (this word becomes ridiculous within the next sentences!) in our development and test system. Since now all developers are sysadmin on our SQL Server (I know...). To start with this "security" my chief requested a script to create a login "development" on server and create a user with same name and "db_owner" rights to all databases... (and so the "security" is away ...sigh).
There are only some special databases which shall be excluded from this solution but that's not my current problem.
My problem
I need a script which creates this "development" account on all databases (ignore the excluded databases requirement).
I have the following script which works fine. But I really would like to remove the WHILE loop!
-- Table to get all statements
DECLARE @stmts TABLE (id INT IDENTITY, stmt NVARCHAR(MAX), PRIMARY KEY (id))
DECLARE @crlf CHAR(2)
SELECT @crlf = CHAR(13) + CHAR(10)
-- Get the statements for all databases
INSERT INTO @stmts
SELECT 'USE ' + QUOTENAME(name) + @crlf +
'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''development'')' + @crlf +
'BEGIN' + @crlf +
' CREATE USER [development] FOR LOGIN [development]' + @crlf +
' EXECUTE sp_addrolemember ''db_owner'', ''development''' + @crlf +
'END'
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')
DECLARE @stmt NVARCHAR(MAX)
DECLARE @id INT
-- Loop through all statements and execute them
WHILE EXISTS (SELECT TOP(1) 1 FROM @stmts)
BEGIN
SELECT TOP(1) @id = id, @stmt = stmt
FROM @stmts
PRINT @stmt
--EXECUTE sp_executesql @stmt
DELETE FROM @stmts WHERE Id = @Id
END
Another possible way I know would be to concatenate all statements into a variable and execute it:
DECLARE @crlf CHAR(2)
DECLARE @stmt NVARCHAR(MAX)
SELECT @stmt = '', @crlf = CHAR(13) + CHAR(10)
-- Get the statements for all databases
SELECT @stmt = @stmt + 'USE ' + QUOTENAME(name) + @crlf +
'IF NOT EXISTS (SELECT 1 FROM sys.database_principals WHERE name = ''development'')' + @crlf +
'BEGIN' + @crlf +
' CREATE USER [development] FOR LOGIN [development]' + @crlf +
' EXECUTE sp_addrolemember ''db_owner'', ''development''' + @crlf +
'END' + @crlf + @crlf
FROM sys.databases
WHERE name NOT IN ('master', 'msdb', 'tempdb', 'model')
PRINT @stmt
--EXECUTE sp_executesql @stmt
... but we currently have over 260 databases on this server and the statement will have a size of 120kb. May this become a problem?
Is it possible to execute all statements within a table (from my first script) without a loop? I remember there may be something with OPENQUERY but it seems not possible to use on local server and I don't want to create a linked server to local server.
I am looking for something like (poseudo code):
SELECT 1
FROM @stmts s CROSS APPLY EXECUTE(s.stmt)
-- OR
SELECT 1,
EXECUTE (s.stmt)
FROM @stmts
-- OR
SELECT 1,
(SELECT 1 FROM OPENQUERY(localServer, N'EXECUTE sp_executesql N''' + s.stmt + ''''))
FROM @stmts s
Is this possible?
Thanks for your advices!
Flo
March 25, 2009 at 8:40 am
There seems to be a problem with the forum...
Please use following link for replies: http://www.sqlservercentral.com/Forums/Topic683335-338-1.aspx
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply