Geek Answers Handbook

Providing the same database role in all SQL Server 2008 R2 databases

Is there a way to provide the user with the same role in all databases? Server-level roles provide only specific server settings for users. I would like to provide the user read-only access to all databases on the server

+4
source share
1 answer

sp_MSforeachdb can do the trick for you:

 EXECUTE master.sys.sp_MSforeachdb ' use [?]; declare @user sysname = ''MyUserName'' if db_name() not in (''master'', ''model'', ''msdb'', ''tempdb'') begin if not exists ( select null from dbo.sysusers where name = @user ) begin print ''Adding '' + @user + '' to '' + db_name(); exec sp_grantdbaccess @user, @user; end; print ''Granting '' + @user + '' read access to '' + db_name(); exec sp_addrolemember db_datareader, @user; end; '
+1
source

More articles:


All Articles