August 23, 2005 at 9:27 am
Hi. I'm trying to write a script that will create a user in each database on a SQL server and then grant db_datareader and db_datawriter to that user in each database.
I'm new to TSQL but I gather that the script will need to get a list of all DBs from sysdatabases and then loop through it, changing to each DB, creating the user and then grant the permissions before moving on to the next.
Could somebody point me in the right direction on this? How would I do the loop? Which SPs might I use? Thanks.
Ryan
August 23, 2005 at 11:56 am
I'm not really sure you would need to do this in production, but here is a cursor example that does what you ask.
prior to doing this, you'd need to create the user 'webdev', and parameterize the hardcoded name to use a variable you pass in:
declare
@isql varchar(2000),
@dbname varchar(64)
declare c1 cursor for select name from master..sysdatabases where name not in ('master','model','msdb','tempdb')
open c1
fetch next from c1 into @dbname
While @@fetch_status <> -1
begin
select @isql = 'USE @dbname' + char(13) + 'EXEC sp_grantdbaccess ''webdev'', ''webdev'' '
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'USE @dbname' + char(13) + 'EXEC sp_addrolemember ''db_datareader'', ''webdev'' '
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
select @isql = 'USE @dbname' + char(13) + 'EXEC sp_addrolemember ''db_datawriter'', ''webdev'' '
select @isql = replace(@isql,'@dbname',@dbname)
print @isql
exec(@isql)
fetch next from c1 into @dbname
end
close c1
deallocate c1
Lowell
August 23, 2005 at 12:04 pm
Lowell, excellent. That was exactly what I was looking for. Thanks for your time.
August 24, 2005 at 7:11 am
You can also use this (unsupported by MS) :
EXEC SP_MSForEachDB 'Select ''?'' as DbName, count(*) as Total from ?.dbo.SysObjects'
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply