automate adding users to MOST of my databases

Question

Post reply

automate adding users to MOST of my databases

george sibbald

SSC Guru

Points: 104210
More actions
March 21, 2008 at 6:51 am

#70894

Hi,
I am trying to automate a process where I add a user and set its permissions to most, but not all, of my databases on a server. As it is not all databases I am not using sp_msforeachdb, but a cursor.
the code needs to run in a different database context in each iteration, but I cannot get the code to change db context. here is my code so far, what am I missing (I cannot find a way to execute the 'go' statement I presume is missing)
declare helpdb_cursor cursor for
select name from master..sysdatabases where --my conditions
open helpdb_cursor
fetch next from helpdb_cursor
into @name
while @@fetch_status = 0
begin
set @statement = N'use [' + @name +']'
exec sp_executesql @statement
print @statement
--add user code in here
fetch next from helpdb_cursor
into @name
end
close helpdb_cursor
deallocate helpdb_cursor
---------------------------------------------------------------------

Viewing 7 posts - 1 through 6 (of 6 total)

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

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 1

-If you only want it in all new userdatabases, grant the users auth in model db.

- in your cursor solution, you should execute the "use db" and the other statements in a single exec !

Maybe this can be an alternative.

>> I do not encourage using the UNDOCUMENTED sp_MSforeachdb without a profound reason ! <<

DECLARE @rc int

DECLARE @command1 nvarchar(2000)

DECLARE @replacechar nchar(1)

DECLARE @command2 nvarchar(2000)

DECLARE @command3 nvarchar(2000)

DECLARE @precommand nvarchar(2000)

DECLARE @postcommand nvarchar(2000)

select @command1 = N'use ? Print ''Db [?]'' if ''?'' = ''master'' or ''?'' = ''msdb'' or ''?'' = ''tempdb'' begin print ''NoAction'' end else begin print ''Action'' end'

, @replacechar = N'?'

, @command2 =N''

, @command3 =N''

, @precommand =N''

, @postcommand =N''

-- Set parameter values

print @command1

EXEC @rc = [master].[dbo].[sp_MSforeachdb] @command1, @replacechar -- , @command2, @command3, @precommand, @postcommand

print 'Execution result:' + cast(@RC as varchar(15))

go

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

george sibbald SSC Guru Points: 104210 More actions · Answer 2

ALZDBA,

I put the code in one exec

set @statement = N'use [' + @name +']'+ " exec sp_grantdbaccess 'PRODUCTION\DCT_SQL_VALIDATION','DCT_SQL_VALIDATION'"

exec sp_executesql @statement

but it still refuses to switch database context!

---------------------------------------------------------------------

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 3

Johan has the right idea. The way your're executing things, you've changed context in one batch, but it goes back for the next batch (sp_executesql). You need to have things on one batch.

Johan Bijnens SSC Guru Points: 135381 More actions · Answer 4

I've noticed you use double quotes in your script in stead of two single quotes !!

This code executed fine at my testserver

Declare @name sysname

Declare @statement nvarchar(1000)

declare helpdb_cursor cursor for

select name from master..sysdatabases

where name not in ('master','msdb', 'tempdb', 'model' )

--my conditions

open helpdb_cursor

fetch next from helpdb_cursor

into @name

while @@fetch_status = 0

begin

-- set @statement = N'use [' + @name +']'

-- exec sp_executesql @statement

set @statement = N'use [' + @name +']'+ ' exec sp_grantdbaccess ''mydomain\myuser'',''xyz'''

exec sp_executesql @statement

print @statement

-- add user code in here

fetch next from helpdb_cursor

into @name

end

close helpdb_cursor

deallocate helpdb_cursor

go

/*

result:

------

use [Northwind] exec sp_grantdbaccess 'mydomain\myuser','xyz'

Granted database access to 'mydomain\myuser'.

*/

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

george sibbald SSC Guru Points: 104210 More actions · Answer 5

ALZDBA (3/21/2008)
I've noticed you use double quotes in your script in stead of two single quotes !!
*/
[/code]

ALZDBA (johan?)

I was getting a syntax error so used set quoted_identifier off and the double quotes. In fact I was just missing a closing quote at the end of the command, so thanks for that.

....and yes it is now working with the commands in one exec!. I misread the output because the user had already been added in manually to all the databases, and query analyser does not reflect the change of databases in the database pane when executed this way.

So I have learnt something today!

Happy Easter,

george

---------------------------------------------------------------------