top of page

GRANT Object Permissions

The permissions system works the same in all versions of SQL Server, SQL Database, Azure Synapse Analytics (Formerly SQL Data Warehouse), Analytics Platform System, however some features are not available in all versions. For example, server-level permission cannot be configured in Azure products.

The following is a chart to illustrate the Database Engine, which manages a hierarchical collection of entities that can be secured with permissions.

https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-hierarchy-database-engine?view=sql-server-ver15

 

Permission in SQL Server is an important thing, here are some examples:

You must have the appropriate permissions within a database to view a query plan. If your login is assigned to the sysadmin, dbcreator, or db_owner role, you have the necessary permissions. Otherwise, you need to be granted the SHOWPLAN permission by running the following statement: GRANT SHOWPLAN TO [username];

You must have permission to read the master database on the Azure server. For example, finding issues such as deadlocking and throttling over the last 30 days through sys.event_log.

To use a DMV in SQL Database, you must be granted the VIEW DATABASE STATE permission.

Who can see the data masked or unmasked is controlled by a database level permission called UNMASK. The dbo user always has this right, so to test this, we create a different user to use after applying the masking. The user must have rights to SELECT data from the table:

CREATE USER MaskedView WITHOUT LOGIN; GRANT SELECT ON Examples.DataMasking TO MaskedView;

 

Granting ALL does not grant all possible permissions. Granting ALL is equivalent to granting all ANSI-92 permissions applicable to the specified object. The meaning of ALL varies as follows:

Scalar function permissions: EXECUTE, REFERENCES.

Table-valued function permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

Stored procedure permissions: EXECUTE.

Table permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE. View permissions: DELETE, INSERT, REFERENCES, SELECT, UPDATE.

https://docs.microsoft.com/en-us/sql/t-sql/statements/grant-object-permissions-transact-sql?view=sql-server-ver15#permissions

 

Permissions are not required to run the EXECUTE statement. However, permissions are required on the securables referenced within the EXECUTE string. For example, if the string contains an INSERT statement, the caller of the EXECUTE statement must have INSERT permission on the target table. Permissions are checked at the time EXECUTE statement is encountered, even if the EXECUTE statement is included within a module.

https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/execute-user-defined-functions?view=sql-server-ver15

 
Trigger execution permissions

You cannot grant permissions to execute a trigger, since users cannot execute triggers: SQL Anywhere fires them in response to actions on the database. Nevertheless, a trigger does have permissions associated with it as it executes, defining its right to perform certain actions.

Triggers execute using the permissions of the owner of the table on which they are defined, not the permissions of the user who caused the trigger to fire, and not the permissions of the user who created the trigger.

When a trigger refers to a table, it uses the group memberships of the table creator to locate tables with no explicit owner name specified. For example, if a trigger on user_1.Table_A references Table_B and does not specify the owner of Table_B, then either Table_B must have been created by user_1 or user_1 must be a member of a group (directly or indirectly) that is the owner of Table_B. If neither condition is met, the database server returns a message, when the trigger fires, indicating that the table cannot be found.

Also, user_1 must have permissions to perform the operations specified in the trigger.

http://dcx.sybase.com/1200/en/dbusage/ptitep.html

 

Example:

If we must modify the Orders table to meet the following requirements: 1. Create new rows in the table without granting INSERT permissions to the table. 2. Notify the sales person who places an order whether or not the order was completed. What should we create?

  1. a stored procedure with the RETURN statement

  2. a FOR UPDATE trigger

  3. an AFTER UPDATE trigger

  4. a user defined function

bottom of page