In many database systems, it is a requirement to grant read permission for all databases. To achieve this, there is no other way but grant read permission to each and every database.
Let’s first create a login :
CREATE LOGIN [newUser] WITH PASSWORD=N'P@$$w0Rd' GO
The login is created without any issues, and you can login to the SQL Server instance. Now, let us connect to database.
USE Test GO
You will get an error as follows.
Msg 916, Level 14, State 1, Line 1
The server principal "newUser" is not able to access the database "Test" under the current security context.
Prior to SQL Server 2014, you don’t have any other option but grant read only permissions to each and every database.
However, with SQL Server 2014 you can achieve this with couple of statements :
GRANT CONNECT ANY DATABASE TO [newUser]
After above statement is executed, now the user can connect to all the databases without any issues. However, can you select data? NO!
Prior to the SQL Server 2014, you need to grant data reader permission in each and every database.
However, again in SQL Server 2014 there is an option for this as well.
GRANT SELECT ALL USER SECURABLES TO [newUser]
With above statement, you will have the option of selecting data in all the tables in all the databases. It is important to note that only SELECT will work for the above statement not INSERT, DELETE or UPDATE.
Another thing to note is, these security settings are sever-wide settings. This means that, when new databases and tables are added to the server or databases, existing users may automatically be granted read permission.
]]>