As 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.
Figure 1. Server securables under server properties
SERVER SECURABLES BY LOGIN
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.
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.
Figure 3. Database securables under database properties
DATABASE SECURABLES BY USEr
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.
Figure 4. Database securables through users
Summary
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?
