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

Question

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

Warren Peace

SSCertifiable

Points: 5881
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: 323518 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!

Warren Peace SSCertifiable Points: 5881 More actions · Answer 2

THAT WAS IT!!!!

I knew I was forgetting something simple...... :hehe:

Thanks a bunch!

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 3

As an alternative for you and others dropping by you can also fully qualify your proc reference like this:

SET @DUMMY = 'EXEC ' + @DB_Name + '.sys.sp_grantdbaccess ' +

CHAR(39) + 'SQLLab' + CHAR(39) + ';'

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato