January 10, 2007 at 7:59 am
Any idea how to give Read only access to entire DB to one of the new user, I have aroound 200 DB.
Thanks
Nita
January 10, 2007 at 8:28 am
Nita
Make the user a member of the db_datareader and db_denydatawriter fixed server roles in the database. If you are asking how to do this for all databases, you can generate a script quite easily using the sp_MSforeachdatabase (undocumented) stored procedure, or by selecting from the sysdatabases table in master. Please post back if you're having difficulty doing this.
John
January 10, 2007 at 8:34 am
I am a NEW DBA, and I know how to add login and give permission but for this enitre 200 DB I don't know how to write script for this
Nita
January 10, 2007 at 9:18 am
Nita
Run this on a test server before you let it anywhere near production. It assumes you already have a login called MyUser and that you want the related user name in each database to be MyUser also. It does it all in one go without having to generate a script for you to run.
sp_MSforeachdb 'use ?
print ''?''
if user_id (''MyUser'') is null exec sp_grantdbaccess ''MyUser''
exec sp_addrolemember ''db_datareader'', ''MyUser''
exec sp_addrolemember ''db_denydatawriter'', ''MyUser'''
John
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply