Database Permissions in SQL Server

In SQL Server, database permissions control what actions users can perform on specific database objects (like tables, views, and stored procedures). Permissions can be granted at different levels, such as server, database, or individual object levels. Here's a breakdown of SQL Server database permissions and how to manage them:

1. Types of Permissions

• Server-level permissions: These affect the entire server and include actions like creating databases or managing logins.
• Database-level permissions: These control actions like selecting data, inserting rows, updating rows, or executing stored procedures within a database.
• Object-level permissions: These are more granular, controlling access to individual objects like tables, views, or functions.

2. Common Database-Level Permissions

• SELECT: Read data from a table or view.
• INSERT: Add data into a table or view.
• UPDATE: Modify data in a table or view.
• DELETE: Remove data from a table.
• EXECUTE: Execute a stored procedure or function.
• ALTER: Modify an object (table, view, etc.).
• CONTROL: Gives full control over a database object.

3. Granting, Revoking, and Denying Permissions

• GRANT: Allows a user to perform an action.
• REVOKE: Removes a previously granted or denied permission.
• DENY: Explicitly prevents a user from performing an action (overrides GRANT).

4. How to Manage Permissions in SQL Server

Granting Permissions

To grant a specific permission to a user or role:

GRANT  ON  TO ;

Example (Grant SELECT on a table):

GRANT SELECT ON Sales.Customers TO JohnDoe;

Revoking Permissions

To revoke a permission:

REVOKE  ON  FROM ;

Example (Revoke INSERT on a table):

REVOKE INSERT ON Sales.Customers FROM JohnDoe;

Denying Permissions

To explicitly deny a permission:

DENY  ON  TO ;

Example (Deny DELETE on a table):

DENY DELETE ON Sales.Customers TO JohnDoe;

5. Roles for Simplified Permission Management

SQL Server uses roles to manage permissions more easily by grouping them:

• Fixed Server Roles (e.g., sysadmin, serveradmin, dbcreator).
• Fixed Database Roles (e.g., db_owner, db_datareader, db_datawriter).
• Custom Roles: You can create your own roles to manage permissions.

To create a custom role and assign permissions:

CREATE ROLE ReportViewer;
GRANT SELECT ON Sales.Customers TO ReportViewer;
EXEC sp_addrolemember 'ReportViewer', 'JohnDoe';

6. Checking User Permissions

To view a user’s permissions, you can query system views like sys.database_permissions or use the fn_my_permissions function:

SELECT * FROM fn_my_permissions(NULL, 'DATABASE');