snippetMajor
How to create a read-only server role on SQL Server 2012?
Viewed 0 times
2012createreadsqlrolehowserveronly
Problem
I am granting the "View any database" permission when creating a new server role, but realized this permission only allows the user to view the system databases.
I am trying to create a server role that is read-only and can read any database.
Is there a way to create a user-defined server role to read user databases? Or do I have to do this through user mapping per user?
I am trying to create a server role that is read-only and can read any database.
Is there a way to create a user-defined server role to read user databases? Or do I have to do this through user mapping per user?
Solution
Posting this as an answer just because it is too long for a comment, and because it will be relevant to other users pretty soon.
SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:
The former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can
The 2014 security changes are documented here - well, partially; they forgot about the database-level permission
SQL Server 2014 adds some new server-level permissions that will assist with exactly this type of scenario - they were designed with auditing in mind, but this type of requirement seems to fit that bill as well. You could simply add the following two permissions to a server-level login:
CONNECT ANY DATABASE
SELECT ALL USER SECURABLESThe former, like it sounds, allows the login to connect to any database. The nice thing about this is that it will allow this even for databases that are created in the future (provided you don't set explicit deny, which is how you can protect certain user databases from logins that have this permission). The latter allows the login to perform read operations on any database they have access to - so they can
SELECT from tables, views, UDFs etc. but they will not be able to perform any UPDATE operations (I haven't tested if this permission understands when a stored procedure performs DML). These work great in combination if you want to give a login wide-open read access to the entire server, or to be more fine-grained you can grant traditional CONNECT privileges to certain databases, and the SELECT ALL USER SECURABLES right will only function for those databases where the login has explicit access to.The 2014 security changes are documented here - well, partially; they forgot about the database-level permission
ALTER ANY DATABASE EVENT SESSION - though that's not relevant here.Code Snippets
CONNECT ANY DATABASE
SELECT ALL USER SECURABLESContext
StackExchange Database Administrators Q#60609, answer score: 23
Revisions (0)
No revisions yet.