Database roles/permissions

  • 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

  • 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