Database Permissions in SQL Server

Managing database permissions is a critical aspect of SQL Server security. Permissions determine what actions users and roles can perform on database objects such as tables, views, stored procedures, and schemas. Proper permission management helps enforce the principle of least privilege, ensuring that users only have the access necessary to perform their designated tasks.

Understanding Permission Levels

Permissions in SQL Server can be granted at various levels:

  • Server Level: Permissions that apply to the entire SQL Server instance. Examples include ALTER ANY LOGIN or CONNECT SQL.
  • Database Level: Permissions that apply to the entire database. Examples include ALTER ANY SCHEMA or SELECT ALL USER SECURABLES.
  • Schema Level: Permissions that apply to all objects within a specific schema. Examples include ALTER ANY ASSEMBLY or CREATE PROCEDURE.
  • Object Level: Permissions that apply to individual database objects like tables, views, or stored procedures. Examples include SELECT, INSERT, UPDATE, DELETE, EXECUTE.
  • Column Level: Permissions that apply to specific columns within a table or view.

Types of Permissions

SQL Server distinguishes between several types of permissions:

  • GRANT: Grants specific permissions to a principal (user or role).
  • DENY: Explicitly denies specific permissions to a principal. DENY overrides GRANT.
  • REVOKE: Removes previously granted or denied permissions.

Securables

A securable is any object on which permissions can be granted or denied. Common SQL Server securables include:

  • Databases
  • Schemas
  • Tables
  • Views
  • Stored Procedures
  • Functions
  • Assemblies
  • Service Broker Objects
  • Endpoints

Granting and Revoking Permissions (T-SQL Examples)

The following examples demonstrate how to manage permissions using Transact-SQL (T-SQL).

Granting SELECT permission on a table


GRANT SELECT ON dbo.Customers TO MyUserRole;
GO

Granting INSERT and UPDATE permissions on specific columns


GRANT INSERT (CustomerID, CompanyName) ON dbo.Customers TO AnotherUser;
GRANT UPDATE (ContactName) ON dbo.Customers TO AnotherUser;
GO

Revoking EXECUTE permission from a stored procedure


REVOKE EXECUTE ON dbo.usp_GetOrderDetails TO ReadOnlyUser;
GO

Denying DELETE permission on a table


DENY DELETE ON dbo.Orders TO SalesTeam;
GO

Roles and Permissions

Using roles is an effective way to manage permissions for groups of users. Instead of granting permissions to individual users, you grant them to roles, and then add users to those roles. SQL Server provides fixed server roles (e.g., sysadmin, securityadmin) and fixed database roles (e.g., db_owner, db_datareader, db_datawriter).

Best Practice: Always grant permissions to roles rather than individual users whenever possible. This simplifies administration and reduces the chance of errors.

Controlling Access with Schemas

Schemas provide a way to group database objects and manage permissions at a higher level. You can grant permissions on entire schemas to roles or users, simplifying the management of access to related objects.


-- Create a schema
CREATE SCHEMA Sales;
GO

-- Grant SELECT permission on all objects in the Sales schema
GRANT SELECT ON SCHEMA::Sales TO SalesViewerRole;
GO

Further Reading