For anyone managing a SQL Server database, understanding permissions is critical to ensuring that only authorized users are able to view and modify data.
This article explains the different types of SQL Server permissions, including server-level and database-level user permissions, and provides guidance on how to manage them.
Key Components of SQL Server Security
Microsoft SQL Server is a relational database management system (DBMS) that supports a wide variety of transaction processing, business intelligence and analytics applications in corporate IT environments. Microsoft SQL Server is one of the three market-leading database technologies.
SQL Server has three primary security components that enable DBAs and sysadmins to control which users can log onto the server, what data they have access to, and what operations they are permitted to carry out on that data. These components are:
- Security principals — People or processes that are authorized to access a particular SQL Server instance
- Securables — The server and database resources for which access is regulated
- Permissions — The types of access that principals have to specific securables
Security Principals
A security principal is an identity that uses SQL Server and can be granted permission to access and modify data. Principals can be individuals, groups of users, roles or entities that are treated as people (such as dbowner, dbo or sysadmin).
Security principals can be created and managed in two ways:
- SQL Server Management Studio (SSMS) — This environment is built for managing various SQL infrastructures, including SQL Server and Azure SQL Database. SMSS provides tools for:
- SQL Server and database configuration, monitoring, and instance administration
- Deploying, monitoring, and upgrading data-tier application components
- Building queries and scripts
- Transact-SQL (T-SQL) — This proprietary extension of SQL is central to utilizing Microsoft SQL services. All database commands provided in this article are T-SQL statements.
Role-Based Security
SQL Server supports role-based access control for security principals. Roles are similar to Microsoft Windows security groups: You define a variety of roles, assign permissions and restrictions, and add users to the server roles corresponding to their job duties. Permissions assigned to a particular role are inherited by all members of the role, so there is no need to create and manage unique permission sets for individual users.
Both server-level and database-level roles can be used.
Server-level Roles
As the name implies, server roles allow you to grant server-level permissions. There are two types of server roles:
- Fixed server roles — SQL Server provides nine fixed server roles; you can assign principals to these roles but you cannot change role permissions. Fixed server roles are convenient and support backwards compatibility, but user-defined roles are recommended wherever possible because they enable you to fine-tune the permissions. Note that SQL Database does not support fixed server roles. However, it includes two roles, dbmanager and loginmanager, that behave like server roles.
- User-defined server roles — You can assign principals and change role permissions.
Creating a user-defined server role is simple:
- Create the role using this command:
CREATE SERVER ROLE serverrole
- Grant permissions to the role using a command like this example:
GRANT ALTER ANY DATABASE TO serverrole
- Add a principal to the role using this command:
ALTER SERVER ROLE serverrole ADD MEMBER [testexampleuser]
Repeat steps 2 and 3 to grant additional permissions and add more principals to the role.
Database-level Roles
Database roles work the same as server roles, except that they allow you to assign database-level permissions instead of server-level permissions.
To create a database role, use the same procedure detailed above for server roles. For example, you might use the following commands:
CREATE ROLE dbrole GRANT SELECT ON DATABASE::TestDatabase TO dbrole ALTER ROLE dbrole ADD MEMBER exampleuser2
Securables
Principals receive permissions to securables. Securables are the resources for which access is regulated. Securables can have nested hierarchies, called “scopes,” which also can be secured. The securable scopes are server, database and schema. (A database schema is a skeletal structure that represents a logical view of an entire database. It defines how data is organized and how the relationships between them are related. It spells out all the constraints that must be applied to the data.)
Permissions
Permissions are the types of access granted to specific securables. At the server level, permissions are assigned to SQL Server logins and server roles. At the database level, they are assigned to database users and database roles.
You have three main ways to control permissions:
- Grant — The GRANT statement enables principals to access specified securables.
- Deny — The DENY statement prevents principals from accessing specified securables.
- Revoke — The REVOKE statement eliminates permissions that were previously granted for specific securables.
Note that the DENY permissions statement overrides all granted permissions.
Hierarchy of Permissions
Securables are organized into a hierarchy comprised of servers, databases, schemas, and objects contained within those schemas. Permissions can be assigned at the server, database, schema or single object level.
Note that permissions granted at a higher level of the hierarchy automatically apply to lower levels, unless you specifically deny them to the securable’s children and grandchildren objects.
How to Securely Manage SQL Server Permissions
Incorrectly assigned permissions can pose a serious risk to data security and create major disruptions for employees trying to complete their tasks. The following best practices will help you avoid those issues.
Adhere to the Principle of Least Privilege
Grant each user only the privileges that are required to perform their tasks — no more and no less. Here are some examples of users and what level of access they might need:
- SQL Server administrator — Need the ability to add and delete entire databases
- Developer — Need to create, design, modify and delete database objects
- Application — Need to view and edit data
- End user — Need to use data in a read-only capacity, or to both read and edit data
Use Roles
Take advantage of the role-based security options provided by SQL Server to assign permissions. Using roles, rather than assigning unique rights to individual users, is not only more convenient, it is also far more accurate.
Understand the Hierarchy of Permissions and Inheritance
As explained earlier, SQL Server securables are grouped into a hierarchy. The server owns the databases housed on it, databases own schemas, and schemas own objects. Permissions can be assigned at any of these levels. Granting a user or role access to a particular level automatically grants them access to all levels below it, unless access is specifically denied or revoked for a child or grandchild object.
Moreover, due to the nested hierarchy, principals, securables and permissions can all inherit each other. For example, a principal could be a role, and all permissions assigned to it would automatically apply to all members of that role.
How Netwrix Auditor Enhances SQL Server Security
Having visibility into your SQL Server permissions is critical to managing them effectively. Netwrix Auditor for SQL Server can help you understand permissions and monitor changes to them. You get:
- Control over SQL Server permissions — Helps you understand who has access to what in SQL Server and determine how those permissions were granted
- Server-level change auditing — Tracks all SQL Server configuration changes, such as permissions updates or SQL server instance deletions
- Database-level change auditing — Monitors database content and object changes
In addition, Netwrix Auditor also provides:
- Detailed logon monitoring — Reports on successful and failed attempts to log into your SQL Server to help you detect and address unauthorized access and cyberattacks
- Alerts on threat patterns — Notifies you about critical events requiring your immediate attention, such as permissions changes and attempts to access essential databases
- Automated audit reports and dashboards — Augments SQL audit logs by providing comprehensive predefined reports and dashboards with filtering, sorting and exporting capabilities
