Security Questions: Accessing SQL Server Securables

PadlockAs I mentioned in the introductory post, during the Introduction to SQL Server Security session for Pragmatic Work’s Training on the T’s, I received a large number of questions that there wasn’t time to answer.  Instead of just a re-cap of all of the questions, instead I’ve opted to put together a post per topic.  Hopefully, this will help, not just those that attended the session, but also anyone searching for the same questions later on.

Security question

The next question in the list is:

How do you access the list of Server Securables?

Great question.  In a perfect world, I’d be able to answer this question with a dynamic management view (DMV).  Unfortunately, that is not one of the available locations for discovering the available securables.  There are two places that provide a list of securables.

The first location for securables is Books Online under the Grant (Transact-SQL) topic.  From there you are able to select the appropriate security domains (such as Server or Database) and click a link to all of the securables for that security domain.

The other securable location is in SQL Server Management Studio (SSMS).  In SSMS, there are four ways to access the lists of securables.  These list are context restricted and break down into two general areas; which are server and database securables.  In the rest of this post, we’ll walk through the four ways in SSMS to access securables.

Server Securables By Server

To access the server securables for all logins on an instance, start by selecting the Properties right-click menu item in the SSMS Object Explorer.  This opens the Server Properties window, shown in Figure 1.  From that window, select the Permissions tab item to access the server securables available for all logins.

Server Securables

Figure 1. Server securables under server properties


To access the server securables for a single login, start by selecting the login under the Security –> Logins folder in the SSMS Object Explorer.  Choose the login that you want to manage Server Securables for and right-click on the login and choose the Properties menu item, shown in Figure 2.  Under the Login Properties, select the Permissions tab item to access the server securables available for that login.

Server Securables by Login

Figure 2. Server securables through logins

Database Securables By Database

Accessing the database securables for all database users, select the Properties right-click menu item under the database name in the SSMS Object Explorer.  Within the Database Properties window, select the Permissions tab, shown in Figure 3.  If a user isn’t listed, use the Search button to find the use that requires database securables.  With a user listed, the database securables are listed at the bottom of the window.

Database Securables

Figure 3. Database securables under database properties


The last method for accessing securables is through users within a database.  To access these securables, right-click on a user and select Properties under a database within SSMS through <database name> –> Security –> Users, shown in Figure 4.  Initially, no database securables will display.  To list the available database securables, click the Search button and select the objects that the securables are related to.  Once chosen, the available securables will be displayed a the bottom of the screen.

Database Securables by Users

Figure 4. Database securables through users


In this post, we answered the question of how to access securables on a SQL Server instance.  For those that aren’t familiar with securables, this is an aspect of SQL Server security that you should be familiar with.  Securables offer some of the best opportunities to secure your environment to the degree that offers the security and access that your environment demands.  What do you think about securables within SQL Server? Do you use them to build upon your security or do you rely on the built-in server and database roles instead?

6 thoughts on “Security Questions: Accessing SQL Server Securables

  1. Where do I get the best practices required for a secure SQL server from a developer’s view point?


    1. Good question, I’ll add it to the list of questions. Now that TechEd is done, I should have more of these posts up this week.


  2. Jason – I can’t share a DMV with you that gives this information… but I can share a System Function.
    SELECT * FROM sys.fn_builtin_permissions(‘SERVER’);
    SELECT * FROM sys.fn_builtin_permissions(‘LOGIN’);
    SELECT * FROM sys.fn_builtin_permissions(‘DATABASE’);
    SELECT * FROM sys.fn_builtin_permissions(‘USER’);
    SELECT * FROM sys.fn_builtin_permissions(DEFAULT);

    It looks like LOGIN and USER (in the screen shots in your post) have more than what this is returning – it probably gets others and concatenates them together. I’ll have to play around to see what is absent, then investigate where they are.


Comments are closed.