Help: SQL Server

In my last post of SQL 2014 Learning series I have explained “Connect any database” permission. As I mentioned, this permission does not grant any permission in any database beyond connect. Someone might think, what’s the use if they are just able to expand the database but not see any database object? Here is the screenshot (which I showed in earlier blog)

As we can see above, database can be expanded but tables are not visible. The real usage of the “Connect any database” permission can be seen if it’s combined with “Select All User Securables”. You may ask, what are the situations when DBA need to give those two permissions? Imagine a situation where an auditor has been appointed to audit the databases. If you have to created auditor account (Balmukund in my example), a long way would be to create login, give permission to database and then permission to select individual tables. This permission “SELECT ALL USERS SECURABLES” is designed  to allow a user to view data in database where he can connect.

Let me give this permission to Balmukund login. I can either use Management Studio Interface or T-SQL.

use [master]
GO
GRANT SELECT ALL USER SECURABLES TO [Balmukund]
GO

As soon as permissions are given to Balmukund, he would be able to select data from all table in database.

If you notice closely, there is a lock icon on dbo.v1 under “Views”. This means that “Balmukund” would not be able to see the definition of the view. His permissions are limited to perform Select on tables and views.

Good part about this permission is that even if we create new tables in the database or even a new database, the permissions are available on new objects as well. This means that DBA need not keep giving permission on newly created table and he can have sound sleep. After taking above screenshot, I created new database (yeah, that’s the name as well) and a new table. Select permissions are automatically available to Balmukund.

Hope this post was helpful.