locked
Create system role that grants read access to ALL databases on instance RRS feed

  • Question

  • Hi, Ive just installed SQL 2012 and Im trying to use the new ability to Create a System role.

    I want to have a role that grants read access to all databases in the server, but denys write.

    Any idea how I can do this? 

    (I know I can set each database individually, but Id like to have master role that grants access to ALL databases on the instance)

     

    Thanks,

     

    Zoe

    Thursday, November 15, 2012 11:49 AM

Answers

  • Hello Zoe,

    That's not possible. The permissions on databases/tables (objects) are defined/stored on database level, not on server level. Server permissions are defined on server level.

    If you e.g. move a database to an other server, the permission on database level are still the same.


    Olaf Helper

    Blog Xing

    • Marked as answer by Zoe.Ohara Thursday, November 15, 2012 12:01 PM
    Thursday, November 15, 2012 11:57 AM

All replies

  • Hello Zoe,

    That's not possible. The permissions on databases/tables (objects) are defined/stored on database level, not on server level. Server permissions are defined on server level.

    If you e.g. move a database to an other server, the permission on database level are still the same.


    Olaf Helper

    Blog Xing

    • Marked as answer by Zoe.Ohara Thursday, November 15, 2012 12:01 PM
    Thursday, November 15, 2012 11:57 AM
  • what a pain! 

    Oh well, Im sure theres some reason for it!

    Thank you,

    Zoe

    Thursday, November 15, 2012 12:01 PM
  • Also, when you open in SSMS a server login and switch to "User Mapping", SSMS queries each database separatly to get the database user name, roles etc.


    Olaf Helper

    Blog Xing


    Thursday, November 15, 2012 12:13 PM
  • As Olaf says, there is no such server role. The only server permission that gives you access to all databases is CONTROL SERVER, but then you can do anything in the databases.

    So you will have to grant access in all databases. However, with regards to new databases, you there is a set-and-forget-it option: set up the permissions you want in the model databases, and they will be copied to all new databases.

    Note that to deny writes, you need to do nothing at all, as by default users have no access at all.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, November 15, 2012 10:01 PM