March 7, 2006 at 2:32 pm
Hello, newbie question here. I have been assigned to add a user account with read only access on a large number of db's on a single sql cluster. I have the script below, along with the list of db's, but cannot find anything under "recursive" or "loop" to allow me to make the script read from the list of db's and perform the action on every db in the list. I know I can manually edit the db name to reflect each db and then run it one at a time, but can anyone tell me what topics to look up in Books Online, or even give me a short explanation here on how this can be done?
Thanks much, trying to learn it from the botttom up!
go
go
GO
EXEC sp_addrolemember 'db_datareader', '<user>'
GO
March 7, 2006 at 4:31 pm
Personally I'd use the undocumented stored procedure sp_msforeachdb
this is a link which explains it use http://www.transactsql.com/html/sp_MSforeachdb.html
there are also procedure called sp_msforeachtable which can be very useful
hth
David
March 7, 2006 at 6:45 pm
Or, if you are a little confused by the plethora of single-quotes, the alternative of using a simple while loop is as follows:
if object_id('tempdb..#databases') is not null drop table #databases
select identity(int,1,1) id, name as dbname
into #databases
from master..sysdatabases
where name not in ('master','tempdb','model','msdb','pubs','northwind','distribution')
declare @id int, @maxid int, @dbname sysname, @sql varchar(1000)
select @id = min(id), @maxid = max(id)
from #databases
while (@id <= @maxid)
begin
select @dbname = dbname
from #databases
where id = @id
select @sql = 'use ' + @dbname + char(10)
+ 'EXEC sp_grantdbaccess ' + quotename('<user>','''') + ', ' + quotename('<user>','''') + char(10)
+ 'EXEC sp_addrolemember ' + quotename('db_datareader','''') + ', ' + quotename('<user>','''')
print @sql
-- exec (@sql)
select @id = @id + 1
end
To excecute the script directly, simply uncomment the exec (@sql) statement!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply