The SELECT ALL USER SECURABLES Permission in SQL Server 2014

The SELECT ALL USER SECURABLES permission in SQL Server 2014 is a very useful new server-level permission.

The login that gets granted this permission, can view the data in all databases that the user can connect to.

For example, consider a scenario where you have the following three databases:

  • db1
  • db2
  • db3
Each database has a different login that accesses its data. So if you do not have sysadmin access on the SQL Server instance, in order to access the data in all databases you will have to log in three times, each time using a different login.

Learn more tips like this! Get the Online Course!

Check our online course titled “Essential SQL Server Administration Tips” (special limited-time discount included in link).

Learn essential hands-on SQL Server Administration tips on SQL Server maintenance, security, performance, integration, error handling and more. Many live demonstrations and downloadable resources included!

Essential SQL Server Administration Tips - Online Course with Live Demonstrations and Hands-on Guides
Learn more about the course.

Learn More


However, if the DBA grant a new login (for example db123) with the “SELECT ALL USER SECURABLES” permission then the new login will be able to access the data in all three databases.

How you can use the SELECT ALL USER SECURABLES permission in SQL Server

Using the visual way, that is Login Properties dialog in SSMS, you can achieve this using two steps:
The SELECT ALL USER SECURABLES Permission in SQL Server 2014 - Article on SQLNetHub
Step 1: Map the login (db123) to the default schema of DB1, DB2 and DB3.

 

The SELECT ALL USER SECURABLES Permission in SQL Server 2014 - Article on SQLNetHub
Step 2: Grant the login (db123) with the “Select All User Securables” permission.

Testing the SELECT ALL USER SECURABLES permission in SQL Server

As you can see in the below screenshot, it was possible to retrieve the data from all three databases by just using the login db123 which was only granted with the server-level permission “Select All User Securables”:

 

The SELECT ALL USER SECURABLES Permission in SQL Server 2014 - Article on SQLNetHub

 

Featured Online Courses:

 

Read also

 

Did you find this article useful and interesting? Feel free to leave your comment!

If you enjoy my SQL Server administration tips and articles, I have something special just for you. It is one of my eBooks and it is called “Administering SQL Server“. Check it out!

Subscribe to our newsletter and stay up to date with our latest articles on SQL Server and related technologies!

Check out our latest software releases! All our software tools have 30-day Trial Versions which are free to download.

 

Rate this article: 1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)

Loading...

Reference: SQLNetHub.com (https://www.sqlnethub.com)

© SQLNetHub