November 3, 2010 at 9:24 am
I want to change\update a service account's permissions on all databases in an instance. I do not want to change or remove permissions for any other existing accounts. I would like the account to have the following permissions on all databases:
db_backupoperator
db_denydatareader
public
thanks in advance
November 3, 2010 at 9:31 am
Under the server, security. right click the user account -> properties. Once the screen opens up for the user account, go to User Mapping. You can then add that account and modify the security settings for each DB.
If you're looking for a method of running scripts across databases...
EXEC sp_MSForEachDB 'Use [?]; SELECT DB_NAME()'
That should give you an idea for doing so. Yet, if this is a one time thing for a user, the first explanation should be enough.
November 3, 2010 at 10:16 am
Public is already an inherited role for all users. Try the following
exec sp_msforeachdb 'use [?];
create user [domain\user];
exec sp_addrolemember ''db_backupoperator'', ''domain\user'';
exec sp_addrolemember ''db_denydatareader'', ''domain\user'';'
Ignore output messages indicating the database user already exists, the roles will still be added
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply