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.