Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access required to grant fixed role membership to database user


Access required to grant fixed role membership to database user

Author
Message
RPSQL
RPSQL
SSC-Addicted
SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)SSC-Addicted (470 reputation)

Group: General Forum Members
Points: 470 Visits: 609
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.
Erland Sommarskog
Erland Sommarskog
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 866
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, www.sommarskog.se
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search