Data Control Language (DCL) Statements

Data Control Language (DCL) statements are used to manage permissions and access to data within a SQL Server database. They control who can perform what operations on which database objects.

Core DCL Statements

The primary DCL statements in SQL Server are:

• GRANT: Used to give users specific permissions to database objects.
• REVOKE: Used to remove permissions previously granted to users.
• DENY: Used to explicitly prevent users from accessing specific database objects, overriding any granted permissions.

GRANT Statement

The GRANT statement is used to allow specific database users or roles to perform certain actions on database objects. These objects can include tables, views, stored procedures, and more. Permissions can range from read access to full control.

Syntax

GRANT { <permission> [ ,...n ] }
    TO <grantee> [ ,...n ]
    [ WITH GRANT OPTION ]

Parameters

• <permission>: Specifies the permission to grant (e.g., SELECT, INSERT, UPDATE, DELETE, EXECUTE, CONTROL).
• <grantee>: Specifies the user or role to whom the permission is granted.
• WITH GRANT OPTION: Allows the grantee to grant the same permission to other users or roles.

Example

-- Grant SELECT permission on the Customers table to the 'DataReader' role
GRANT SELECT ON dbo.Customers TO DataReader;

-- Grant EXECUTE permission on the 'usp_UpdateProduct' stored procedure to user 'AppUser'
GRANT EXECUTE ON dbo.usp_UpdateProduct TO AppUser;

-- Grant all permissions on the Orders table to 'AdminUser' and allow them to grant these permissions
GRANT ALL ON dbo.Orders TO AdminUser WITH GRANT OPTION;

REVOKE Statement

The REVOKE statement is used to remove permissions that were previously granted to users or roles. This is essential for managing access and ensuring data security.

Syntax

REVOKE [ FOR <replica_group> ] { <permission> [ ,...n ] }
    FROM <revokee> [ ,...n ]
    [ CASCADE ]
    [ AS <revoke_from_principal> ]

Parameters

• <permission>: Specifies the permission to revoke.
• <revokee>: Specifies the user or role from whom the permission is revoked.
• CASCADE: Revokes the permission from subordinates of the revokee who were granted the permission via WITH GRANT OPTION.
• AS <revoke_from_principal>: Specifies the principal that is performing the revoke operation.

Example

-- Revoke SELECT permission on the Customers table from the 'DataReader' role
REVOKE SELECT ON dbo.Customers FROM DataReader;

-- Revoke EXECUTE permission on the 'usp_UpdateProduct' stored procedure from user 'AppUser'
REVOKE EXECUTE ON dbo.usp_UpdateProduct FROM AppUser;

-- Revoke all permissions on the Orders table from 'AdminUser' and cascade the revoke
REVOKE ALL ON dbo.Orders FROM AdminUser CASCADE;

DENY Statement

The DENY statement explicitly prevents specific users or roles from performing certain actions on database objects, even if they have been granted those permissions through other means (e.g., membership in a role that has the permission). DENY takes precedence over GRANT.

Syntax

DENY { <permission> [ ,...n ] }
    TO <deny_grantee> [ ,...n ]
    [ CASCADE ]
    [ AS <database_principal> ]

Parameters

• <permission>: Specifies the permission to deny.
• <deny_grantee>: Specifies the user or role to whom the permission is denied.
• CASCADE: Denies the permission to subordinates of the grantee.
• AS <database_principal>: Specifies the principal that is performing the deny operation.

Example

-- Explicitly deny DELETE permission on the Products table to user 'Guest'
DENY DELETE ON dbo.Products TO Guest;

-- Deny INSERT permission on the Logs table to the 'Auditor' role
DENY INSERT ON dbo.Logs TO Auditor;

Permissions Hierarchy

Permissions in SQL Server are hierarchical. Permissions on a server level apply to all databases. Permissions on a database level apply to all objects within that database. Permissions on schema level apply to all objects within that schema, and so on down to individual object permissions.

Viewing Permissions

You can use system catalog views and stored procedures to inspect current permissions:

• sys.database_permissions: Provides information about permissions granted at the database level.
• sys.server_permissions: Provides information about permissions granted at the server level.
• sp_helptext: Can be used to view the definition of stored procedures and other database objects.
• fn_my_permissions(): Returns permissions for the current user.

Example to view SELECT permissions on a table:

SELECT
    dp.permission_name,
    dp.state_desc,
    dp.class_desc,
    rp.name AS principal_name
FROM sys.database_permissions AS dp
JOIN sys.database_principals AS rp
    ON dp.grantee_principal_id = rp.principal_id
WHERE dp.permission_name = 'SELECT'
  AND dp.major_id = OBJECT_ID('dbo.Customers');