If I understand right, you want to grant a group read permissions on every database on the server?
If that is the case, you can use something like:
exec sp_msforeachdb '
print ''Processing ?'';
if not exists (select * from sysusers where name = ''BUILTIN\Users'' )
CREATE User [BUILTIN\Users] For Login [BUILTIN\Users];
EXEC sp_addrolemember N''db_datareader'', N''BUILTIN\Users'';
Of course, replacing Builtin\User with your group. There are a couple of things to be aware of though:
This will give read permission to every database, including master and msdb. This may reveal more than you want to if you are not careful with it.
There may be problems if you have "phantom users". This does not happen often, but may occur if you do something like restoring a backup from a different server. Make sure you remove any "phantom users" before running a script like this.
Remember that any permissions that group had before will remain intact, so if you really want them to only have read permissions, then you will need to use something separate to remove other permissions.
Nov 10, 2009 at 09:24 PM