Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

SQL 2014 Learning Series # 2 – New Permission – Select All User Securables

Posted by blakhani on April 1, 2014


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.

image

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.

image

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.

image

Hope this post was helpful.

  • Cheers,
  • Balmukund Lakhani
  • Twitter @blakhani
  • Author: SQL Server 2012 AlwaysOnPaperback, Kindle
  • Advertisements

    3 Responses to “SQL 2014 Learning Series # 2 – New Permission – Select All User Securables”

    1. manu said

      Really helpful post. A very useful permission introduced to address audit related scenarios.

    2. […] 2014 Learning Series 1: CONNECT ANY DATABASE SQL 2014 Learning Series 2: SELECT ALL USER SECURABLES New SQL Server 2014 Permissions: CONNECT ANY DATABASE Is it safe to grant ADMINISTER BULK […]

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

     
    %d bloggers like this: