Configure permissions for users to access database objects
Every SQL Server securable has associated permissions that can be granted to a principal. Permissions in the Database Engine are allocated to logins and server roles at the server level and database users and database roles at the database level. The database permissions mechanism is the same in the Azure SQL Database model, except server level permissions are not accessible.
Once you’ve mastered the permissions, utilize the GRANT, REVOKE, and DENY statements to apply server-level permissions to logins and database-level rights to users. For Example:
SQL
GRANT SELECT ON OBJECT::HumanResources.Employee TO Larry;
REVOKE SELECT ON OBJECT::HumanResources.Employee TO Larry;
Chart of SQL Server Permissions
The following graphic shows the permissions and their relationships to each other. Some of the higher-level permissions (like CONTROL SERVER) are mentioned several times.
Summary of the Permission Check Algorithm
Permissions might be difficult to check. The permission check process takes into account overlapping group memberships and ownership chaining, as well as explicit and implicit permission, and can be influenced by permissions on securable classes containing the securable item. The algorithm’s general procedure, however, is to acquire all relevant permits. The program then looks for a GRANT that grants adequate access if no blocking DENY is identified. The security context, the permission space, and the needed authorization are all critical components of the algorithm.
Security context
This is the set of principals whose permissions are added to the access check. Unless the security context was changed to another login or user using the EXECUTE AS line, these are rights associated to the current login or user. The security context, on the other hand, incorporates the following principles:
- Firstly, the login
- Secondly, the user
- Thirdly, role memberships
- Next, windows group memberships
- Lastly, if module signing is being used, any login or user account for the certificate used to sign the module that the user is currently executing, and the associated role memberships of that principal.
Permission space
This is the securable entity, as well as any securable classes that it contains. The schema securable class and the database securable class, for example, both include a table (a securable object). Table, schema, database, and server-level permissions can all affect access.
Required permission
The kind of permission that is required. INSERT, UPDATE, DELETE, SELECT, EXECUTE, ALTER, CONTROL, and so on are some examples. Access, on the other hand, may need several permissions, as seen in the following examples:
- Firstly, a stored procedure can require both EXECUTE permission on the stored procedure and INSERT permission on several tables that are referenced by the stored procedure.
- Secondly, a dynamic management view can require both VIEW SERVER STATE and SELECT permission on the view.
General Steps of the Algorithm
When the algorithm is determining whether to allow access to a securable, the precise steps that it uses can vary, depending on the principals and the securables that are involved. However, the algorithm performs the following general steps:
- Firstly, bypass the permission check if the login is a member of the sysadmin fixed server role or if the user is the dbo user in the current database.
- Secondly, allow access if ownership chaining is applicable and the access check on the object earlier in the chain passed the security check.
- thirdly, aggregate the server-level, database-level, and signed-module identities that are associated with the caller to create the security context.
- Next, for that security context, collect all the permissions that are granted or denied for the permission space. The permission can be explicitly stated as a GRANT, GRANT WITH GRANT, or DENY. Or the permissions can be an implied or covering permission GRANT or DENY.
- After that, identify the required permission.
- Then, fail the permission check if the required permission is directly or implicitly denied to any of the identities in the security context for the objects in the permission space.
- Lastly, pass the permission check if the required permission was not denied and the required permission contains a GRANT or a GRANT WITH GRANT permission either directly or implicitly to any of the identities in the security context for any object in the permission space.
Examples
The examples in this section show how to retrieve permissions information.
Returning the complete list of grantable permissions
The following statement returns all Database Engine permission by using the fn_builtin_permissions function. For more information, see sys.fn_builtin_permissions (Transact-SQL).
SQL
SELECT * FROM fn_builtin_permissions(default);
GO
Returning the permissions on a particular class of objects
The following example uses fn_builtin_permissions to view all the permissions that are available for a category of securable. However, the example returns permissions on assemblies.
SQL
SELECT * FROM fn_builtin_permissions(‘assembly’);
GO
The permissions given to the executing principal on an object are returned.
The following example shows how to use fn my permissions to get a list of the effective permissions that the caller principal has on a given securable. The example, on the other hand, returns permissions for an object called Orders55.
SQL
SELECT * FROM fn_my_permissions(‘Orders55’, ‘object’);
GO
Returning the permissions applicable to a specified object
The permissions for an object named Yttrium are returned in the example below. Furthermore, the ID of object Yttrium is retrieved using the built-in function OBJECT_ID.
SQL
SELECT * FROM sys.database_permissions
WHERE major_id = OBJECT_ID(‘Yttrium’);
GO
Reference: Microsoft Documentation