DCL in SQL Server: GRANT, DENY and REVOKE

DCL commands control WHO can do WHAT.

GRANT  -> give a permission
REVOKE -> take back a permission
DENY   -> block a permission

Steps:

• LOGIN : lets a person connect to the SQL Server
• USER : lets that login use ONE database
• GRANT : gives the user permissions on tables

You need admin rights to run this file
(on your own computer you usually have them).

This lesson creates its own small tables, so you can run it on any SQL Server database. The Output under each query is the real result.

IF DB_ID('SqlBasicsDb') IS NULL
    CREATE DATABASE SqlBasicsDb;
GO

USE SqlBasicsDb;
GO

-- A small table to give permissions on
DROP TABLE IF EXISTS Salaries;
GO

CREATE TABLE Salaries
(
    EmpID  INT,
    Name   VARCHAR(50),
    Salary INT
);

INSERT INTO Salaries VALUES (1, 'Ravi', 50000), (2, 'Sita', 40000);
GO

-- Clean up from an earlier run
DROP USER IF EXISTS TestUser;
IF EXISTS (SELECT 1 FROM sys.server_principals WHERE name = 'TestLogin')
    DROP LOGIN TestLogin;
GO

(2 rows affected)

Create a LOGIN (server level)

CREATE LOGIN TestLogin WITH PASSWORD = 'Test@12345';

Create a USER for that login (database level)

CREATE USER TestUser FOR LOGIN TestLogin;
GO

GRANT: allow TestUser to read the Salaries table

GRANT SELECT ON Salaries TO TestUser;

-- Test it : act as TestUser for a moment
EXECUTE AS USER = 'TestUser';
    SELECT * FROM Salaries;         -- works
    -- DELETE FROM Salaries;        -- would fail: no DELETE permission
REVERT;                             -- go back to your own user
GO

GRANT more than one permission

GRANT INSERT, UPDATE ON Salaries TO TestUser;

REVOKE: take back a permission

REVOKE UPDATE ON Salaries FROM TestUser;

DENY: block a permission (stronger than REVOKE)

DENY DELETE ON Salaries TO TestUser;

See the permissions of TestUser

SELECT permission_name, state_desc, OBJECT_NAME(major_id) AS TableName
FROM sys.database_permissions
WHERE grantee_principal_id = USER_ID('TestUser');

Clean up (remove the test user and login)

DROP USER TestUser;
DROP LOGIN TestLogin;
GO

-- =============================================
-- REVOKE vs DENY
--   REVOKE -> removes a GRANT or DENY (back to "no permission given")
--   DENY   -> blocks it, even if the user gets it from somewhere else (e.g. a role)
-- =============================================