January 25, 2012 at 2:59 am
Hi,
I am trying to reduce the number of people granted sysadmin on our SQL Servers. I've run into a bit of a hurdle with a guy from the apps team. He needs select, update, delete access to all databases on a particular server, thats fine, I can add him to db_backupoperator, db_datareader and db_datawriter, but doing this for every database is tedious and will require more manual changes if any new databases are added. Is there anyway to grant these permissions universally on a server?
Thanks,
Bodsda
January 25, 2012 at 3:26 am
I don't know any way of doing that, but it should be fairly simple to set up, something like this:
SELECT 'USE ' + name + CHAR(13) + CHAR(10) + ' EXEC sp_addrolemember ''db_datareader'', ''NewUser'''
FROM master.sys.databases
WHERE db_id > 4 --or whatever no you need to exclude system DBs
You'll probably need to tweak the syntax a bit. As for new databases, run this in model as well and it'll automatically appear in new databases, as long as they're CREATEd and not RESTOREd.
John
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply