Help: SQL Server

Sharing my knowlege about SQL Server Troubleshooting Skills

  • Blog Stats

    • 2,039,820 hits
  • Select GETDATE()

    April 2014
    M T W T F S S

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.


use [master]

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.

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

    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: Logo

    You are commenting using your 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 )

    Connecting to %s

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    %d bloggers like this: