9.3 Permissions and GRANT statements

9.3.1 Overview

In SQL Server, permissions control access to the database objects. The GRANT statement is used to give specific permissions to users or roles.

9.3.2 Checking Current Permissions

You can use the following query to check the current permissions of a user or role on a specific object:

-- Query to check permissions
USE YourDatabaseName;
-- Replace 'YourObjectName' with the actual object name (table, view, stored procedure, etc.)
-- Replace 'YourUserName' with the actual user or role name
SELECT 
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE 
    major_id = OBJECT_ID('YourObjectName') AND
    grantee_principal_id = DATABASE_PRINCIPAL_ID('YourUserName');

9.3.3 Granting SELECT Permission

Let's grant SELECT permission on a table named 'SampleTable' to a user named 'SampleUser':

-- Grant SELECT permission
USE YourDatabaseName;
GRANT SELECT ON dbo.SampleTable TO SampleUser;

9.3.4 Verifying SELECT Permission

After granting the SELECT permission, you can verify the permissions again using the query from section 9.3.2:

-- Verify SELECT permission
USE YourDatabaseName;
SELECT 
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE 
    major_id = OBJECT_ID('SampleTable') AND
    grantee_principal_id = DATABASE_PRINCIPAL_ID('SampleUser');

permission_name	state_desc
SELECT	GRANT

9.3.5 Granting EXECUTE Permission on a Stored Procedure

Let's grant EXECUTE permission on a stored procedure named 'usp_SampleProcedure' to a role named 'SampleRole':

-- Grant EXECUTE permission
USE YourDatabaseName;
GRANT EXECUTE ON dbo.usp_SampleProcedure TO SampleRole;

9.3.6 Verifying EXECUTE Permission

After granting the EXECUTE permission, you can verify the permissions using the query from section 9.3.2:

-- Verify EXECUTE permission
USE YourDatabaseName;
SELECT 
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE 
    major_id = OBJECT_ID('usp_SampleProcedure') AND
    grantee_principal_id = DATABASE_PRINCIPAL_ID('SampleRole');

permission_name	state_desc
EXECUTE	GRANT

9.3.7 Revoking Permissions

To revoke a permission, you can use the REVOKE statement. For example, to revoke the SELECT permission:

-- Revoke SELECT permission
USE YourDatabaseName;
REVOKE SELECT ON dbo.SampleTable FROM SampleUser;

9.3.8 Verifying Revoked Permission

After revoking the SELECT permission, verify the permissions using the query from section 9.3.2:

-- Verify revoked SELECT permission
USE YourDatabaseName;
SELECT 
    permission_name,
    state_desc
FROM sys.database_permissions
WHERE 
    major_id = OBJECT_ID('SampleTable') AND
    grantee_principal_id = DATABASE_PRINCIPAL_ID('SampleUser');

permission_name	state_desc
SELECT	DENY

This table-format tutorial should make it easier to understand the concepts and see the query outputs at each step. Adjust the object names and user/role names based on your actual database configuration.