19

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?

2 Answers 2

23

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:

CONNECT ANY DATABASE

SELECT ALL USER SECURABLES

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 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.

6
  • Thanks for this extended answer for up coming servers. Being that this operation/check is at the server level. Are there any comments on if the DB Compatibility Level. So if the DB CL is 2012, will the 2014 feature still work?
    – SnapJag
    Oct 8, 2015 at 18:17
  • @SnapJag Compatibility level does not have any bearing on security features, it is mostly about how T-SQL is parsed and how the optimizer generates query plans. Oct 8, 2015 at 18:20
  • @AaronBertrand Do you know if there is a permission we can set on server-level roles to allow updates on any database too ? (sysadmin fixed role can for sure, but can we do it for a user defined server role ?)
    – MaxiWheat
    Apr 14, 2016 at 13:53
  • @MaxiWheat No, can't think of any trivial way to do that, sorry. Apr 14, 2016 at 14:40
  • FWIW this won't work for Azure SQL Database. GRANT CONNECT ANY DATABASE TO [NameOfUserOrGroup] Securable class 'server' not supported in this version of SQL Server.
    – Dzejms
    Feb 20, 2019 at 20:37
8

There's no server-level 'read any database' permission and server-level roles can't be granted database-level permissions

So yes, you will have to map users to databases individually. If you're using Active Directory, you can create a windows group, then give that group a login to SQL Server, then apply db_datareader in all databases for that group (you'll need to create users in each database though).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.