Configure database and object-level permissions using graphical tools
In this tutorial, we will learn about how to Configure database and object-level permissions using graphical tools. However, Permissions in the Database Engine are managed at the server level through logins and server roles, and at the database level through database users and database roles. The model for SQL Database exposes the same system within each database, but the server level permissions are not available.
Security Principals
Security principal is the official name of the identities that use SQL Server and that can be assigned permission to take actions. They are usually people or groups of people, but can be other entities that pretend to be people. However, the security principals can be created and managed using the Transact-SQL listed, or by using SQL Server Management Studio.
Logins
Logins are individual user accounts for logging on to the SQL Server Database Engine. SQL Server and SQL Database support logins based on Windows authentication and logins based on SQL Server authentication.
Fixed Server Roles
In SQL Server, fixed server roles are a set of pre-configured roles that provide convenient group of server-level permissions. Logins can be added to the roles using the ALTER SERVER ROLE … ADD MEMBER statement.
User-defined Server Roles
In SQL Server, you can create your own server roles and assign server-level permissions to them. Logins can be added to the server roles using the ALTER SERVER ROLE … ADD MEMBER statement.
Database Users
Logins are granted access to a database by creating a database user in a database and mapping that database user to login. Typically the database user name is the same as the login name, though it does not have to be the same. Further, database users can also be created that do not have a corresponding login. These are called contained database users. Microsoft encourages the use of contained database users because it makes it easier to move your database to a different server. Like a login, a contained database user can use either Windows authentication or SQL Server authentication.
Fixed Database Roles
Fixed database roles are a set of pre-configured roles that provide convenient group of database-level permissions. However, database users and user-defined database roles can be added to the fixed database roles using the ALTER ROLE … ADD MEMBER statement.
User-defined Database Roles
Users with the CREATE ROLE permission can create new user-defined database roles to represent groups of users with common permissions. Typically permissions are granted or denied to the entire role, simplifying permissions management and monitoring. However, database users can be added to the database roles by using the ALTER ROLE … ADD MEMBER statement.
Assigning Permissions
Most permission statements have the format:
AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL;
- Firstly, AUTHORIZATION must be GRANT, REVOKE or DENY.
- Secondly, the PERMISSION establishes what action is allowed or prohibited. SQL Server 2016 (13.x) can specify 230 permissions. SQL Database has fewer permissions because some actions are not relevant in Azure.
- Thirdly, ON SECURABLE::NAME is the type of securable (server, server object, database, or database object) and its name. Some permissions do not require ON SECURABLE::NAME because it is unambiguous or inappropriate in the context.
- Next, PRINCIPAL is the security principal (login, user, or role) which receives or loses the permission. Grant permissions to roles whenever possible.
However, permissions are granted to security principals (logins, users, and roles) by using the GRANT statement. Permissions are explicitly denied by using the DENY command. A previously granted or denied permission is removed by using the REVOKE statement. Permissions are cumulative, with the user receiving all the permissions granted to the user, login, and any group memberships; however any permission denial overrides all grants.
Permission Hierarchy
Permissions have a parent/child hierarchy. That is, if you grant SELECT permission on a database, that permission includes SELECT permission on all (child) schemas in the database. However, if you grant SELECT permission on a schema, it includes SELECT permission on all the (child) tables and views in the schema. And, the permissions are transitive; that is, if you grant SELECT permission on a database, it includes SELECT permission on all (child) schemas, and all (grandchild) tables and views.
Because both the parent/child hierarchy and the covering hierarchy can act on the same permission, the permission system can get complicated. For example, let’s take a table (Region), in a schema (Customers), in a database (SalesDB).
- Firstly, CONTROL permission on table Region includes all the other permissions on the table Region, including ALTER, SELECT, INSERT, UPDATE, DELETE, and some other permissions.
- Secondly, SELECT on the Customers schema that owns the Region table includes the SELECT permission on the Region table.
So SELECT permission on the Region table can be achieved through any of these six statements:
Monitoring Permissions
The following views return security information.
- Firstly, the logins and user-defined server roles on a server can be examined by using the sys.server_principals view. This view is not available in SQL Database.
- Secondly, the users and user-defined roles in a database can be examined by using the sys.database_principals view.
- Thirdly, the permissions granted to logins and user-defined fixed server roles can be examined by using the sys.server_permissions view. This view is not available in SQL Database.
- Next, the permissions granted to users and user-defined fixed database roles can be examined by using the sys.database_permissions view.
- After that, database role membership can be examined by using the sys. sys.database_role_members view.
- Lastly, server role membership can be examined by using the sys.server_role_members view. This view is not available in SQL Database.
Useful Transact-SQL Statements
The following statements return useful information about permissions.
To return the explicit permissions granted or denied in a database ( SQL Server and SQL Database), execute the following statement in the database.
SQL
SELECT
perms.state_desc AS State,
permission_name AS [Permission],
obj.name AS [on Object],
dPrinc.name AS [to User Name]FROM sys.database_permissions AS perms
JOIN sys.database_principals AS dPrinc
ON perms.grantee_principal_id = dPrinc.principal_id
JOIN sys.objects AS obj
ON perms.major_id = obj.object_id;
To return the members of the server roles ( SQL Server only), execute the following statement.
SQL
SELECT sRole.name AS [Server Role Name] , sPrinc.name AS [Members]FROM sys.server_role_members AS sRo
JOIN sys.server_principals AS sPrinc
ON sRo.member_principal_id = sPrinc.principal_id
JOIN sys.server_principals AS sRole
ON sRo.role_principal_id = sRole.principal_id;
To return the members of the database roles ( SQL Server and SQL Database), execute the following statement in the database.
SQL
SELECT dRole.name AS [Database Role Name], dPrinc.name AS [Members]FROM sys.database_role_members AS dRo
JOIN sys.database_principals AS dPrinc
ON dRo.member_principal_id = dPrinc.principal_id
JOIN sys.database_principals AS dRole
ON dRo.role_principal_id = dRole.principal_id;
Reference: Microsoft Documentation