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.

9.2 User and Role Management

9.2.1 Introduction

User and Role Management in SQL Server involves creating and managing user accounts, assigning permissions, and organizing users into roles for simplified access control.

9.2.2 Creating Users

To create a new user, you can use the CREATE USER statement.

-- Create a new user
CREATE USER [UserName] WITH PASSWORD = 'UserPassword';

9.2.3 Creating Roles

Roles help organize users and manage permissions collectively. Use CREATE ROLE to create a new role.

-- Create a new role
CREATE ROLE [RoleName];

9.2.4 Adding Users to Roles

To grant permissions to a group of users, add them to a role using ALTER ROLE.

-- Add a user to a role
ALTER ROLE [RoleName] ADD MEMBER [UserName];

9.2.5 Viewing Users

You can view the existing users in the database using the sys.database_principals system view.

-- View existing users
SELECT name AS 'User Name', type_desc AS 'User Type'
FROM sys.database_principals
WHERE type_desc = 'SQL_USER';

User Name	User Type
User1	SQL_USER
User2	SQL_USER
User3	SQL_USER

9.2.6 Viewing Roles

Similarly, you can view existing roles in the database using the sys.database_principals system view.

-- View existing roles
SELECT name AS 'Role Name', type_desc AS 'Role Type'
FROM sys.database_principals
WHERE type_desc = 'SQL_ROLE';

Role Name	Role Type
Role1	SQL_ROLE
Role2	SQL_ROLE
Role3	SQL_ROLE

9.2.7 Granting Permissions

Grant specific permissions to a user or role using the GRANT statement.

-- Grant SELECT permission on a table
GRANT SELECT ON [TableName] TO [UserName];

9.2.8 Revoking Permissions

To revoke previously granted permissions, use the REVOKE statement.

-- Revoke SELECT permission on a table
REVOKE SELECT ON [TableName] FROM [UserName];

9.2.9 Removing Users from Roles

Remove a user from a role using the ALTER ROLE statement.

-- Remove a user from a role
ALTER ROLE [RoleName] DROP MEMBER [UserName];

9.2.10 Deleting Users

Delete a user from the database using the DROP USER statement.

-- Delete a user
DROP USER [UserName];

9.2.11 Deleting Roles

Delete a role from the database using the DROP ROLE statement.

-- Delete a role
DROP ROLE [RoleName];

This tutorial provides an overview of User and Role Management in SQL Server, including creating users and roles, assigning permissions, and managing their relationships. The query outputs are presented in a table format for easy reference.