Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

Access required to grant fixed role membership to database user Expand / Collapse
Posted Thursday, August 21, 2014 11:14 AM


Group: General Forum Members
Last Login: Tuesday, November 1, 2016 9:01 AM
Points: 433, Visits: 597
Hi DBAs, I have a question related to database security.

We hired a team to perform access provisioning and removal from database. The main function of the team will be to control database security access only. Thus we don't want to give them db_owner or sysadmin level of permission.

We tried with providing the team db_securityadmin and db_accesadmin but those are not sufficient roles. Can someone suggest workaround to provide access to the team to manage database level security i.e.
Post #1605933
Posted Thursday, August 21, 2014 12:01 PM

SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 4:45 AM
Points: 888, Visits: 861
So what does this team need to do, which is not covered by db_securityadmin and db_accessadmin? Without this information, your question is somewhat difficult to answer.

I note in Books Online that it says: "Adding members to fixed database roles requires membership in the db_owner fixed database role." And that's a good thing, or else the team members could add themselves to db_owner.

Maybe the best if you give the team a copy of the database, and then you can use a tool like Red Gate's SQLCompare, or SQL Server Data Tools to replicate permissions and other security-related things from their copy once they are done. Or simply ask the team to produce a script that you can review.

Erland Sommarskog, SQL Server MVP,
Post #1605951
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse