locked
Server level roles RRS feed

  • Question

  • Hello. I was under the impression that I could create a server role that say, granted read-only permissions to all non-system DBs on a server. But that seems to not be the case. There's no option to add db_datareader into a server role. We still have grant someone db_datareader in every DB either in the GUI or via an sp_msforeachdb statement. Is that right? Guess I'm not quite grasping this.

    Ken

    Friday, August 19, 2016 1:48 PM

Answers

  • Expanding on Olaf's comment, server-level principals (logins and server roles) can only be granted/denied server-level permissions and can only be added to server roles. Database-level principals (users and database roles) can only be granted/denied database-level permissions and be added to database roles.

    If you are in a domain environment, and this is a common requirement, you could create an Active Directory security group, and create a user in one or more databases for the security group (either with a login or as a contained database user). Then add that user account to the database role in one or more databases. Then, in the future, the single step of adding a person to the Active Directory security group, would get them the database role permission.

    Obviously this moves the access control to the people administering the Active Directory group, which might or might not be an issue.

    For a better understanding of permissions and the fixed roles, see the poster athttp://go.microsoft.com/fwlink/?LinkId=229142.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by ktrock Friday, August 19, 2016 5:26 PM
    Friday, August 19, 2016 3:57 PM

All replies

  • Hello Ken,

    See CREATE SERVER ROLE (Transact-SQL) => Remarks: "Server roles cannot be granted permission on database-level securables"

    For this you have to create & use a database role.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Friday, August 19, 2016 1:57 PM
  • Expanding on Olaf's comment, server-level principals (logins and server roles) can only be granted/denied server-level permissions and can only be added to server roles. Database-level principals (users and database roles) can only be granted/denied database-level permissions and be added to database roles.

    If you are in a domain environment, and this is a common requirement, you could create an Active Directory security group, and create a user in one or more databases for the security group (either with a login or as a contained database user). Then add that user account to the database role in one or more databases. Then, in the future, the single step of adding a person to the Active Directory security group, would get them the database role permission.

    Obviously this moves the access control to the people administering the Active Directory group, which might or might not be an issue.

    For a better understanding of permissions and the fixed roles, see the poster athttp://go.microsoft.com/fwlink/?LinkId=229142.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by ktrock Friday, August 19, 2016 5:26 PM
    Friday, August 19, 2016 3:57 PM
  • Thanks folks.

    "If you are in a domain environment, and this is a common requirement, you could create an Active Directory security group, and create a user in one or more databases for the security group (either with a login or as a contained database user). Then add that user account to the database role in one or more databases. Then, in the future, the single step of adding a person to the Active Directory security group, would get them the database role permission."

    That's how we actually do most of our security; we inherited a 2008 environment (now in 2012). We've got ~30 DBs to do this for and have a new security requirement like this. Was hoping to leverage server roles but it does clearly say they can't use database-level securables :(

    Ken

    Friday, August 19, 2016 5:26 PM