t-sql to add a user to ALL DB's on the server....

Question

Post reply

t-sql to add a user to ALL DB's on the server....

Warren Peace

SSCertifiable

Points: 5871
More actions
July 5, 2011 at 12:58 pm

#145587

OK, I am testing a SQL Dev box. I P2V'd our Dev server into our Lab. Now I want to grant/add a user called "SQLLab" to ALL of the DB's on this DB server. I have the following script to iterate through all the db's...

DECLARE @DB_Name varchar(128)

DECLARE @DUMMY varchar(128)

DECLARE DBList CURSOR

FOR

SELECT name FROM sys.databases

WHERE name <> 'master' AND name <> 'model' AND name <> 'msdb' AND name <> 'Northwind' AND

name <> 'pubs' AND name <> 'tempdb'

ORDER BY name

OPEN DBList

FETCH NEXT FROM DBList

INTO @DB_Name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @DUMMY = 'USE [' + + @DB_Name + ']' + '; sp_grantdbaccess ' + CHAR(39) + 'SQLLab' + CHAR(39) + ';'

PRINT @DUMMY

EXEC (@DUMMY)

FETCH NEXT FROM DBList

INTO @DB_Name

END

CLOSE DBList

DEALLOCATE DBList

I'm pretty sure my scoping may be off or the t-sql isn't following the scope of the DB I wnat to get into in the "USE" statement. I get the following output on the first DB....

USE [_RESTORERECOVER]; sp_grantdbaccess 'SQLLab';

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near 'sp_grantdbaccess'.

I'm missing something but im drawing a blank....lol :crazy:

Viewing 4 posts - 1 through 3 (of 3 total)

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

Lowell SSC Guru Points: 323482 More actions · Answer 1

i think all you need is EXEC in from of the proc: multi line statements require EXECUTE procname, only when a command is standalone can you just put the name of the proc.

SET @DUMMY = 'USE [' + + @DB_Name + ']' + '; EXEC sp_grantdbaccess ' + CHAR(39) + 'SQLLab' + CHAR(39) + ';'

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!