SQL Server 2014 Security Enhancements : User Object Permissions
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.