SQL Server

SQL Server security – (Part 3)

by Basit Aalishan Masood-Al-Farooq

SQL Server security – (Part 3)

Author: Basit A. Farooq

Editor’s Note: In this third part of the SQL Server security article series, you’ll first learn how to manage securables at the database and schema scope. You’ll also learn about fixed server roles and how to create custom server roles to simplify administration and manage permissions. This article applies to SQL Server 2005 and later editions. However, some contents of this part may only apply to SQL Server 2012, SQL Server 2014 and SQL Server 2016 Preview editions.

SQL Server securables

A securable is an object that you can secure using permissions. You assign permissions to the server instance and to securable objects with the server scope. These objects are logins, databases, and endpoints. An endpoint represents a port used to communicate with SQL Server.

SQL Server server-level permissions

You assign permissions on the server to determine which changes a user can make at the server level. Here are some of the permissions you can set at the server level are:

• ALTER ANY DATABASE — Modify any database on the server.
• ALTER ANY LOGIN — Modify any login.
• ALTER SERVER STATE — Modify the state of the server.
• ALTER SETTINGS — Modify server settings.
• CONNECT SQL — Connect to the server.
• CONTROL SERVER — Full control of the server.
• CREATE ANY DATABASE — Create a database on the server.
• SHUTDOWN — Shut down the server.
• VIEW SERVER STATE — View information about the state of the server.

Some of these permissions are said to “cover” other permissions, meaning that, by granting one permission, you implicitly cover another permission (grant the other permission). For example, ALTER ANY DATABASE covers CREATE ANY DATABASE, so if you grant ALTER ANY DATABASE to a login, that login can also create databases. For full list of permission you can assign to SQL Server 2014 and SQL Server 2016 instance, refer to Permissions (Database Engine) article on MSDN website.

Granting permissions

You can use SQL Server Management Studio or the GRANT statement to grant permissions. When used to grant access permission to a server, the GRANT statement has the following syntax:

GRANT permission [,…n]

TO <grantee_principal> [,…n] [WITH GRANT OPTION]

[AS <grantor_principal>]

The grantee_principal must be a login. You use the grantor_principal to identify the login from which the principal executing the statement derives the permission to grant permission. You must have IMPERSONATE permission on the security principal specified. You can grant server-level permissions only when connected to the master database.

You can specify WITH GRANT OPTION to grant the permission and the permission to grant the same permission to other logins. For example, to grant the BasitFarooq the ALTER ANY DATABASE permission and allow him to grant that permission to other logins, you execute the following in SQL Server Management Studio Query Editor:

GRANT ALTER ANY DATABASE

TO BasitFarooq

WITH GRANT OPTION

Denying permissions

You use the DENY statement to prevent a login from performing an action, even if the principal obtains the permission from membership in a Windows group or role. The DENY statement has the following syntax:

DENY permission [,…n]

TO <grantee_principal> [,…n]

[CASCADE]

[AS <grantor_principal>]

You use the CASCADE option to deny the permission to the specified principal and any principal granted permission by that principal. You must have CONTROL SERVER permission to deny permission to a server. For example, to deny JoeBlogg the SHUTDOWN permission and also deny any logins granted the SHUTDOWN permission by JoeBlogg, you execute the following in SQL Server Management Studio Query Editor:

DENY permission

TO JoeBlogg

CASCADE

Revoking permissions

You use the REVOKE statement to reverse a GRANT or DENY statement. After executing REVOKE on a permission to a login, the permission is neither granted nor denied. However, permissions applied by way of Windows group or role membership still apply. You must have CONTROL SERVER permission to revoke permission to a server. The REVOKE statement has the following syntax when used to revoke permissions on a SQL Server instance:

REVOKE [GRANT OPTION FOR] permission [,…n]

{ TO | FROM } <grantee_principal> [,…n]

[CASCADE]

[AS <grantor_principal>

If you specify GRANT OPTION FOR, the permission to grant the permission to others is revoked but not the permission itself.

Managing permissions using SQL Server Management Studio GUI

You can manage permissions using SQL Server Manage Studio GUI. To do so, follow the instructions listed below:

• Launch SQL Server Management Studio
• Right-click the SQL Server in Object Explorer and choose Properties.
• Click Permissions.
• Select a login or role.
• In the explicit permissions list, check Grant, With Grant, or Deny for the appropriate permission. The following combinations are supported:
  • Grant — Permission is granted.
  • Grant and With Grant — Permission is granted and the login can grant others the permission.
  • Deny — The permission is denied.
• To revoke a permission, clear the box.
• Click OK.

Effective permissions

You can use SQL Server Management Studio to view effective permissions for a login. Effective permissions take into account the group and role memberships for the login. For example, JoeBlogg is a login, a member of the public role (because all logins are members), and he has been granted ALTER ANY DATABASE. To view his effective permissions:

• Launch SQL Server Management Studio
• Right-click the SQL Server in Object Explorer and choose Properties.
• Click Permissions.
• Select a principal.
• Click effective permissions. A dialog box that lists all the permissions assigned to the user through inheritance or directly appears.
• After viewing permissions, click OK or Cancel.
• Click OK or Cancel to close Server Properties.

Server roles

SQL Server has the following two types of server roles: fixed server roles and user-defined server roles. Let’s have a look at them:

Fixed server roles

SQL Server defines eight fixed server roles. You can view them by expanding Security, Server Roles. The eight server roles are described in the following table:

Server Role	Permissions Granted
bulkadmin	ADMINISTER BULK OPERATIONS
dbcreator	CREATE DATABASE
diskadmin	ALTER RESOURCES
processadmin	ALTER ANY CONNECTION ALTER SERVER STATE
securityadmin	ALTER ANY LOGIN
serveradmin	ALTER ANY ENDPOINT ALTER RESOURCES ALTER SERVER STATE ALTER SETTINGS SHUTDOWN VIEW SERVER STATE
setupadmin	ALTER ANY LINKED SERVER
sysadmin	CONTROL SERVER with the GRANT option

The sysadmin account has the following members by default:

• sa
• NT SERVICESQLWriter — a virtual account used by SQL Writer service
• NT SERVICEWinmgmt— a virtual account used by WMI.
• NT SERVICEMSSQLSERVER — a virtual account used by SQL Server service start-up account to gain access to the SQL Server instance.
• NT SERVICESQLSERVERAGENT — a virtual account used for SQL Server Agent service start-up account to gain access to the SQL Server instance.

There’s another special role, the public role, which has VIEW ANY DATABASE permissions. All logins are members of the public role. You can’t modify the membership of the public role.

User-defined server roles

Beginning with SQL Server 2012, SQL Server Database Engine allows creating user-defined server roles. The user-defined roles are similar to the fixed server roles; the only difference is that they are created and managed by SQL Server administrators. User-defined roles allow administrators to create and assign server-wide permissions to user-defined roles and then add members to them.

To return the list of server-wide permissions, you use sys. fn_builtin_permissions as follows:

USE [master]

GO

SELECT *

FROM [sys].[fn_builtin_permissions] (DEFAULT)

WHERE [class_desc] IN ( ‘ENDPOINT’, ‘LOGIN’, ‘SERVER’,

‘AVAILABILITY GROUP’, ‘SERVER ROLE’ )

ORDER BY [class_desc] ,

[permission_name];

GO

The main advantage of user-defined roles is that they simplify administration by limiting access of authorized users according to segregations of duties.

We can create and manage user-defined roles via T-SQL or via SQL Server Management Studio (SSMS), which is demonstrated below:

Creating user-defined roles with SQL Server Management Studio GUI

The steps to create user-define server roles using SQL Server Management Studio GUI are demonstrated as follows:

• Launch SQL Server Management Studio
• In the Object Explorer, expand the instance of SQL Server, and then expand the Security folder.
• Right-click the Sever Roles folder, then select New Server Role from the menu to launch the New Server Role wizard.
• On the General page, specify the name, owner and appropriate securable for the new user-defined server role.
• Click on Members to activate the Members page, then add members to your user-defined roles.
• Finally, click on the Membership page if you want to add your new user-defined role as a member of the existing fixed server role.

Creating user-defined roles using Transact-SQL statements

We can use CREATE SERVER ROLE, ALTER SERVER ROLE and DROP SERVER ROLE Transact-SQL statements to create, alter and drop user-defined server roles. This is demonstrated as follows:

— Creating user-defined roles
CREATE SERVER ROLE [JuniorDBA]

— Granting server-wide permissions
GRANT CREATE ANY DATABASE TO [JuniorDBA]

— Adding members to user-defined roles
ALTER SERVER ROLE [JuniorDBA]
ADD MEMBER [DomainJuniorDBA_Group1]
ALTER SERVER ROLE [JuniorDBA]
ADD MEMBER [DomainJuniorDBA_Group1]

— Making user-defined role member of fixed server role
ALTER SERVER ROLE [processadmin]
ADD MEMBER [JuniorDBA]

— Dropping user-defined roles
DROP SERVER ROLE [JuniorDBA]

Adding a login to a server role

You can use the sp_addsrvrolemember system stored procedure to add a login to a server role. It has the following syntax:

sp_addsrvrolemember [@loginname=] ‘login‘

, [@rolename=] ‘role‘

For example, to add JoeBlogg to the securityadmin role, you execute the following Transact-SQL statement against SQL Server instance in SQL Server Management Studio:

sp_addsrvrolemember ‘JoeBlogg’, ‘securityadmin’

You can add a login to a server role by using SQL Server Management Studio. To do so:

• Expand Security, Server Roles.
• Right-click a role and choose Properties.
• Click Add.
• Enter the login name
• Click Check Names.
• Click OK to close the Select Logins box.
• Click OK to close the Server Role Properties box.

Dropping a login from a server role

You can use the sp_dropsrvrolemember system stored procedure to remove a login from a server role. It has the following syntax:

sp_dropsrvrolemember [@loginname=] ‘login‘,

[@rollename=] ‘role‘

For example, to drop JoeBlogg from the securityadmin role, you execute:

sp_dropsrvrolemember ‘JoeBlogg’, ‘securityadmin’

You can drop a user from a role using SQL Server Management Studio. To do so:

• Expand Security, Server Roles.
• Right-click a role and choose Properties.
• Select a login.
• Click Remove.
• Click OK.

Retrieving role membership

You can use the sys.server_role_members catalog view to retrieve the membership in server-level roles. The catalog view returns two columns:

• role_principal_id
• member_principal_id

To find out the name associated with the principal id, you use the sys.server_principals catalog view. For example, to obtain a list of role membership, showing the member’s name, you execute the following in SQL Server Management Studio:

SELECT srm.role_principal_id, p.[name]

FROM sys.server_principals p

JOIN sys.server_role_members as srm

ON srm.member_principal_id = p.principal_id

You can also use the IS_SRVROLEMEMBER function to determine whether the current user or a specific login is a member of a specific server role. The IS_SRVROLEMEMBER function has the following syntax:

IS_SRVROLEMEMBER(‘role‘ [, ‘login‘)

The function returns 0 if the user isn’t a member; 1 if the user is a member, or NULL if the group or role doesn’t exist.

Continue to Part-4….