SQL Server

SQL Server security – (Part 5)

by Basit Aalishan Masood-Al-Farooq

SQL Server security – (Part 5)

Author: Basit A. Farooq

Editor’s note: In this part, you’ll learn how to manage securables at the database and schema scope. You’ll learn how to use fixed database roles and how to create custom database roles to manage permissions.

Database and schema securables – Overview

Each database has a number of securables that inherit permissions from the database. You can also manage permission on them explicitly. Permissions at the database and schema scope can be managed by user and by role.

Database securables

Each database has a number of objects you can secure by using permissions. Although a full list of objects is beyond the scope of this article, a few you need to manage include:

• Role – Database role.
• Application role – Used to assign access to an application.
• Assembly – Packages a CLR function for execution.
• Fulltext catalog – Stores full-text indexes.
• Certificate – PKI certificate.
• Schema – Container for objects.

Schema securables

Each schema also has a number of securables. The objects in a schema inherit their default permissions from the schema. Most objects you associate with a database belong to a schema. These include:

• Type
• XML schema collection
• Object – The object class has the following members:
  • Aggregate
  • Function
  • Procedure
  • Queue
  • Synonym
  • Table
  • View

Database roles

SQL Server has a number of fixed database roles. You can also define custom roles.

Fixed database roles

The following table shows the fixed database-level roles and their capabilities. These roles exist in all databases.

Role Name	Database permissions	Description
db_accessadmin	Granted ALTER ANY USER, CREATE SCHEMA. Granted CONNECT with Grant option.	Members can add or remove access to the database for Windows logins, Windows groups, and SQL Server logins.
db_backupoperator	Granted BACKUP DATABASE, BACKUP LOG, and CHECKPOINT.	Members can back up the database.
db_datareader	Granted SELECT.	Members can read all data from all user tables.
db_datawriter	Granted INSERT, UPDATE, DELETE.	Members can add, delete, or change data in all user tables.
db_ddladmin	Granted the ALTER and CREATE permission on all database level objects.	Members can run any Data Definition Language (DDL) command in a database.
db_denydatareader	Denied SELECT.	Members cannot read any data in the user tables within a database.
db_denydatawriter	Denied INSERT, UPDATE, and DELETE.	Members cannot add, modify, or delete any data in the user tables within a database.
db_owner	Granted CONTROL with Grant option.	Members can perform all configuration and maintenance activities on the database, and can also drop the database.
db_securityadmin	Granted ALTER ANY APPLICATION ROLE, ALTER ANY ROLE, CREATE SCHEMA, and VIEW DEFINITION.	Members can modify role membership and manage permissions. Adding principals to this role could enable unintended privilege escalation.

Fixed database roles inside msdb database

The msdb database contains the special-purpose roles that are shown in the following table.

Msdb Role Name	Description
db_ssisadmin db_ssisoperator db_ssisltduser	Members can administer and use SSIS.
dc_admin dc_operator dc_proxy	Members can administer and use the data collector.
PolicyAdministratorRole	Members can perform all configuration and maintenance activities on Policy-Based Management policies and conditions.
ServerGroupAdministratorRole ServerGroupReaderRole	Members can administer and use registered server groups.
dbm_monitor	Created in the msdb database when the first database is registered in Database Mirroring Monitor. The dbm_monitor role has no members until a system administrator assigns users to the role.

Creating a database role

You can create a role using the Transact SQL or by using SQL Server Management Studio. The general steps for creating and managing a role are:

• Create the role.
• Grant and deny permissions to the role.
• Add database users as role members.

To create a role using Transact-SQL, you execute the CREATE ROLE statement. The syntax for CREATE ROLE statement is as follows:

CREATE ROLE role_name [AUTHORIZATION owner_name]

You use the AUTHORIZATION option to designate an owner for the role, other than the user creating the role. The requirements for authorizing another user or role as owner are shown in the table below.

Type of owner	Permission required on the owner
Database user	IMPERSONATE permission.
Database role	ALTER permission or membership in the role.
Application role	ALTER permission.

Next, you assign permissions using GRANT and DENY. The syntax depends on the objects you’re assigning permissions to. The syntax is similar to what you’ve seen for other permissions in previous articles of this series.

Finally, you add members to the role using the ALTER ROLE statement. Its syntax is:

— SQL Server Syntax

ALTER ROLE role_name

{

[ ADD MEMBER database_principal ]

| [ DROP MEMBER database_principal ]

| WITH NAME = new_name

}

[;]

You can specify the database_principal as a database user, database role, Windows login, or Windows group.

You can also add members to the role using the sp_addrolemember system stored procedure. It has the following syntax:

sp_addrolemember [@rolename=] ‘role’
[@membername=] ‘security_account’

You can specify the security_account as a database user, database role, Windows login, or Windows group.

Note that sp_addrolemember will be removed in a future version of Microsoft SQL Server. Therefore, avoid using it. Use ALTER ROLE instead.

To create a role using SQL Server Management Studio:

• Launch SQL Server Management Studio.
• In Object Explorer, expand Databases, and then expand the database in which you want to create a role.
• Expand the Security folder.
• Right-click Roles, then choose New, and then choose New Database Role from the menu.
• In the Role name box, enter the name of the role.
• If you want to assign ownership to another user or role, in the Owner box, enter the owner name or browse to select one.
• Select any schemas owned by the role.
• Click Add to add a role member.
• Enter the name of the member.
• Click Check Names.
• Click OK.
• Click Securables.
• Add the securables that the role has permission on as follows:
  • Click Add. The Add Objects dialog box appears.
  • Select Specific objects, All objects of the types, or All objects belonging to the schema. Your selection here filters the types of objects available for assigning permissions. For example, select All objects of the types to display the Select Object Types dialog box.
  • If you selected All objects of the types, the Select Object Types dialog box appears. Check the objects types you want to assign permission to.
  • Click OK.
  • Select each object and assign individual permissions by checking the appropriate Grant, With Grant, and Deny boxes.
• After you’ve finished setting permissions, click OK.

Dropping role members

You can drop a member from a role either by using the sp_droprolemember system stored procedure or ALTER ROLE statement.

Note that sp_droprolemember will be removed in a future version of Microsoft SQL Server. Therefore, avoid using it. Use ALTER ROLE instead. See above for its syntax.

The sp_droprolemember has the following syntax:

sp_droprolemember [@rolename=] ‘role’,
[@membername=] ‘security_account’

You can also remove a role member using SQL Server Management Studio. To do so:

• Launch SQL Server Management Studio.
• In Object Explorer, expand Databases, and then expand the database in which database role exist.
• Expand the Security folder
• Next, expand the Roles folder, and then Database Roles folder.
• Right-click the role and choose Properties.
• Select the member you want to delete.
• Click Remove.
• Click OK.

Dropping roles

You can drop a user-defined role, provided it doesn’t own any database objects. To delete a role using SQL Server Management Studio:

• Launch SQL Server Management Studio.
• In Object Explorer, expand Databases, and then expand the database in which database role exist.
• Expand the Security folder
• Next, expand the Roles folder, and then Database Roles folder.
• Right-click the role and choose Delete.
• Click OK to confirm the deletion.

You can drop a role using the DROP ROLE statement. The statement has the following syntax:

DROP ROLE role_name

Retrieving information about roles

You can retrieve information about membership in database roles using the sys.database_role_members catalog view. It returns two columns:

• role_principal_id – Database Principal ID of the role.
• member_principal_id – Database Principal ID of the member.

You can use a join with the sys.database_principals table to retrieve names instead of IDs.

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

IS_MEMBER(‘group‘ | ‘role‘)

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-6…