SQL Server

Managing SQL Server Permissions (part 1) - Using SSMS to Manage Permissions at the Server Level

10/17/2010 6:09:08 PM
You can use T-SQL or the visual tools available in SSMS to manage permissions. Based on the number of available permissions and their complexity, it is recommended that you use the SSMS tools. The following sections cover these tools from several different angles and look at the management of permissions at different levels of the security model. You learn how to use T-SQL to manage the permissions as well.

Using SSMS to Manage Permissions

The Object Explorer in SSMS enables you to manage permissions at many different levels of the permission hierarchy. You can manage permissions at a high level, such as the entire server, or you can manage permissions at the very lowest level, including a specific object, such as a table or stored procedure. The degree of granularity you use for permissions depends on your security needs. To demonstrate the scope of permissions, let’s look at managing permissions at several different levels, starting at a high level and working down to the object level.


There are many different ways to achieve a security goal in SSMS. For example, you can manage permissions for a database user from the database or from the user. You can apply permissions on schema objects for the entire schema or to individual objects. You should always try to choose the permission solution that allows you to achieve your security goals with the least amount of administrative overhead.

Using SSMS to Manage Permissions at the Server Level

Logins can be granted explicit permissions at the server level. Earlier we looked at fixed server roles as one means for assigning permissions, but you can manage individual server-level securables as well. Figure 1 shows the Login Properties window for a login named Chris. You launch this window by right-clicking the login and selecting Properties. Figure 1 shows the Securables page, which allows you to add specific securables to the grid.

Figure 1. Server-level permissions.


You can open a Permissions page like the one shown in Figure 1 from many different places in the Object Explorer. The title of the dialog box and the content of the grid vary, depending on the object selected, but the screen is generally the same, no matter where it is launched. This provides consistency and simplifies the overall management of permissions.

You can click the Search button shown toward the top of Figure 1 to add objects to the securables grid. When you click this button, the Add Objects window shown in Figure 2 is displayed. This window allows you to choose the types of objects you want to add. If you select Specific Objects, you are taken directly to the Select Objects window. If you choose All Objects of the Types, you are taken to an intermediate screen that allows you to select the type of objects you want to assign permissions to.

Figure 2. The Add Objects window.

Again, the Add button and the means for adding objects are fairly consistent for all permissions. What varies is the object types available for selection. For example, at the server level, the types of objects available to assign permissions are scoped at the server level. Figure 3 shows the Select Object Types window displayed when you choose the All Objects of the Types option at the server level. You can see that the available objects are all scoped at the server level.

Figure 3. Server-level object types.

If the endpoints objects are selected, the securables grid is populated with all the available endpoints that have permissions to manage. Figure 4 shows the Login Properties window with the endpoints securables populated. The TSQL Named Pipes securable is selected, which allows you to specify the explicit permissions for the securable in the bottom grid. In this example, the Grant and With Grant check boxes have been selected for the control permission. This gives the login named Chris the right to control the Named Pipes endpoint and also allows him to grant this control right (because With Grant is selected) to other logins.

Figure 4. Server-level securables.

The examples we just walked through are related to the assignment of explicit permission on a specific instance of a securable. You can also apply server permissions at a higher level. For example, you might want to specify permissions for a login to allow that login to control all server endpoints instead of specific endpoints. You can accomplish this in several ways. One way to do it is to select the Server object from the list of object types when adding permissions for a specific login. Another way is to right-click the server name in the Object Explorer and select Properties. The Server Properties window that appears has a Permissions page that lists all the logins for the server, along with the macro-level permissions scoped for the server. Figure 5 shows the Server Properties window with the login Chris selected. The explicit permissions listed in this case are at a higher level and are not just for one instance. The example shown in Figure 5 allows the login Chris to alter any database or any endpoint on the server. This is based on the Grant check boxes selected.

Figure 5. The Server Properties window’s Permissions page.

Other -----------------
- Central Management Servers (part 4) - Evaluating Policies
- Central Management Servers (part 3) - Configuring Multi-Server Query Options
- Central Management Servers (part 2) - Running Multi-Server Queries
- Central Management Servers (part 1) - Creating a Central Management Server
- SQL Server 2008 : The sqlcmd Command-Line Utility
- Installing SQL Server 2008 Using a Configuration File
- SQL Server 2008 : Slipstream Installations
- SQL Server Programmability Objects
- SQL Server 2005 : Data Querying and Reporting (part 2)
- SQL Server 2005 : Data Querying and Reporting (part 1)
- Configuring SQL Server 2008 : Instances vs Default Instance
- sp_configure and SQL Server Management Studio
- Configuring SQL Server 2008 : Database Mail
- Configuring SQL Server 2008 : Full-Text Indexing
- SQL Server 2008 : Working with Indexes
- SQL Server 2008 : Working with Constraints
- SQL Server 2008 : Working with Tables and Views
- SQL Server 2008 : Viewing and Modifying Data (part 3) - Creating Functions and Creating Triggers
- SQL Server 2008 : Viewing and Modifying Data (part 2) - Creating Stored Procedures
- SQL Server 2008 : Viewing and Modifying Data (part 1) - Creating Views
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
Most View
- Implementing Windows Vista’s Internet Security and Privacy Features (part 1)
- The Art of SEO : Traditional Approaches: Domain Expertise, Site Content Analysis
- Windows Server 2008 : Configuring Terminal Services Clients
- SharePoint 2010 : SQL Server Reporting Services 2008 (part 3) - Installing and Configuring SQL Server Reporting Services 2008
- SharePoint 2010 : Collaboration and Portals - Choosing to Use Portal Sites
- Windows Phone 7 : Forwarding Calls
- Windows Server 2008 : Perform an Unscheduled Backup of Critical Volumes of a Domain Controller by Using the Command Line
- Understanding Service Broker Constructs (part 3)
- Software Testing with Visual Studio Team System 2008 : Unit testing an ASP.NET application
- Deploying and Administering Windows Vista : Selecting Windows Vista Editions & Choosing a Deployment Type