SQL Server User Permissions: Check, Grant, Deny and Revoke

If you’ve tried to query a table as a new login, you’ve probably seen “The SELECT permission was denied on the object.” SQL Server user permissions are working; nobody granted that user SELECT.

The fix starts with knowing what a user can do: impersonate them with EXECUTE AS USER, run fn_my_permissions, then REVERT. You change it with GRANT, DENY or REVOKE.

In this tutorial, I’ll show you how to check and manage SQL Server user permissions on SQL Server 2025, from one table up to the whole server.

The Sample Database, Logins and Users We’ll Use

Everything runs in a demo database, DfqPermissions. dbo.Employees holds salaries, so it’s the table people shouldn’t read:

CREATE SCHEMA sales;
GO
CREATE TABLE dbo.Customers (CustomerID int PRIMARY KEY, Name nvarchar(20), State char(2));
CREATE TABLE dbo.Employees (EmployeeID int PRIMARY KEY, Name nvarchar(20), Salary decimal(10,2));
CREATE TABLE sales.Invoices (InvoiceID int PRIMARY KEY, CustomerID int, Total decimal(10,2));

INSERT INTO dbo.Customers VALUES (1, N'Megan Bowen', 'WA'), (2, N'Alex Wilber', 'TX'), (3, N'Adele Vance', 'CA');
INSERT INTO dbo.Employees VALUES (10, N'Lee Gu', 98000.00), (11, N'Lynne Robbins', 87500.00);
INSERT INTO sales.Invoices VALUES (5001, 1, 245.50), (5002, 3, 1210.00);

Each of three SQL logins maps to a database user with the same name:

CREATE LOGIN DfqApp_Orders   WITH PASSWORD = N'Str0ng!Pass2026';
CREATE LOGIN DfqReader_Megan WITH PASSWORD = N'Str0ng!Pass2026';
CREATE LOGIN DfqReader_Alex  WITH PASSWORD = N'Str0ng!Pass2026';

USE DfqPermissions;
CREATE USER DfqApp_Orders   FOR LOGIN DfqApp_Orders;
CREATE USER DfqReader_Megan FOR LOGIN DfqReader_Megan;
CREATE USER DfqReader_Alex  FOR LOGIN DfqReader_Alex;

Principal	Lives in	Gets
Login	Server (master)	Connect to the instance, server permissions
User	One database	Table, schema and database permissions
Role	Server or database	Permissions shared by its members

The app user can read and change dbo.Customers, Megan is in db_datareader, and Alex reads sales through a role (GRANT and ALTER ROLE sections below). Here’s how to get a list of users in SQL Server.

Method 1: Check User Permissions on a Table With fn_my_permissions (Recommended)

fn_my_permissions returns the effective permissions of whoever runs it, roles included. To check someone else, switch to them with EXECUTE AS USER, then REVERT:

SET NOCOUNT ON;
EXECUTE AS USER = 'DfqApp_Orders';

SELECT CAST(permission_name AS varchar(12)) AS Permission
FROM fn_my_permissions('dbo.Customers', 'OBJECT')
WHERE subentity_name = '';

SELECT CAST(USER_NAME() AS varchar(16)) AS CheckedAs;
REVERT;
SELECT CAST(USER_NAME() AS varchar(16)) AS BackTo;

Output:

Permission  
------------
SELECT      
UPDATE      
INSERT      
CheckedAs       
----------------
DfqApp_Orders   
BackTo          
----------------
dbo

How does this work? Inside the EXECUTE AS block, the check runs as DfqApp_Orders, so you get SELECT, UPDATE and INSERT. REVERT puts you back as dbo.

subentity_name = '' drops per-column rows; add AND permission_name = 'INSERT' to test one permission. You need IMPERSONATE on the user.

Method 2: Check a User Permission as Yes/No With HAS_PERMS_BY_NAME

When you only need 1 or 0, HAS_PERMS_BY_NAME is shorter. Same three tables, now checked for Megan:

SET NOCOUNT ON;
EXECUTE AS USER = 'DfqReader_Megan';

SELECT CAST(SCHEMA_NAME(schema_id) + '.' + name AS varchar(16)) AS TableName,
  HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name, 'OBJECT', 'SELECT') AS CanSelect,
  HAS_PERMS_BY_NAME(SCHEMA_NAME(schema_id) + '.' + name, 'OBJECT', 'INSERT') AS CanInsert
FROM sys.tables
ORDER BY TableName;

SELECT HAS_DBACCESS('DfqPermissions') AS DbAccess;
REVERT;

Output:

TableName        CanSelect   CanInsert  
---------------- ----------- -----------
dbo.Customers              1           0
dbo.Employees              1           0
sales.Invoices             1           0
DbAccess   
-----------
          1

How does this work? db_datareader grants SELECT database-wide, so every CanSelect is 1 and every CanInsert is 0. NULL would mean a misspelled permission.

HAS_DBACCESS returns 1 when the user can enter the database. When it’s 0, logins hit Cannot open database requested by the login.

Method 3: List Who Has Permissions on Each Table (sys.database_permissions)

To audit the whole database, join sys.database_permissions to sys.database_principals and sys.objects:

SET NOCOUNT ON;
SELECT CAST(pr.name AS varchar(15)) AS Grantee,
       CAST(pe.permission_name AS varchar(15)) AS Permission,
       CAST(pe.state_desc AS varchar(5)) AS State,
       CAST(CASE pe.class WHEN 1 THEN OBJECT_SCHEMA_NAME(o.object_id) + '.' + o.name
                          WHEN 3 THEN 'SCHEMA::' + SCHEMA_NAME(pe.major_id)
                          ELSE 'DATABASE' END AS varchar(16)) AS OnWhat
FROM sys.database_permissions AS pe
JOIN sys.database_principals AS pr ON pr.principal_id = pe.grantee_principal_id
LEFT JOIN sys.objects AS o ON o.object_id = pe.major_id AND pe.class = 1
WHERE pr.name LIKE 'Dfq%' OR pr.name = 'SalesReaders'
ORDER BY Grantee, OnWhat;

Output:

Grantee         Permission      State OnWhat          
--------------- --------------- ----- ----------------
DfqApp_Orders   CONNECT         GRANT DATABASE        
DfqApp_Orders   INSERT          GRANT dbo.Customers   
DfqApp_Orders   SELECT          GRANT dbo.Customers   
DfqApp_Orders   UPDATE          GRANT dbo.Customers   
DfqReader_Alex  CONNECT         GRANT DATABASE        
DfqReader_Alex  VIEW DEFINITION GRANT DATABASE        
DfqReader_Megan CONNECT         GRANT DATABASE        
SalesReaders    SELECT          GRANT SCHEMA::sales

How does this work? class 1 is an object, 3 a schema and 0 the database. Add WHERE o.name = N'Customers' to see who has what on one table.

Important: Megan shows only CONNECT, because catalog views skip role membership. Pair this inner join across multiple tables with Method 1; the older sp_table_privileges has the same gap.

How Do You Check Server-Level Permissions in SQL Server?

Server permissions belong to logins, in sys.server_permissions. I’ll grant one first, from master:

SET NOCOUNT ON;
GRANT VIEW SERVER STATE TO DfqApp_Orders;

SELECT CAST(sp.name AS varchar(15)) AS Login,
       CAST(pe.permission_name AS varchar(17)) AS Permission,
       CAST(pe.state_desc AS varchar(5)) AS State
FROM sys.server_permissions AS pe
JOIN sys.server_principals AS sp ON sp.principal_id = pe.grantee_principal_id
WHERE sp.name LIKE 'Dfq%'
ORDER BY Login;

Output:

Login           Permission        State
--------------- ----------------- -----
DfqApp_Orders   CONNECT SQL       GRANT
DfqApp_Orders   VIEW SERVER STATE GRANT
DfqReader_Alex  CONNECT SQL       GRANT
DfqReader_Megan CONNECT SQL       GRANT

Only the app login has VIEW SERVER STATE. sysadmin members skip permission checks, so keep that role small and know how to reset the sa password.

How to Grant Permissions to a User in SQL Server

GRANT works on one table, a whole schema, or the database. Here are the statements behind the sample setup:

-- 1. Table level
GRANT SELECT, INSERT, UPDATE ON dbo.Customers TO DfqApp_Orders;

-- 2. Schema level, through a role
CREATE ROLE SalesReaders;
GRANT SELECT ON SCHEMA::sales TO SalesReaders;

-- 3. Database level
GRANT VIEW DEFINITION TO DfqReader_Alex;
GO

SET NOCOUNT ON;
EXECUTE AS USER = 'DfqApp_Orders';
SELECT HAS_PERMS_BY_NAME('dbo.Customers', 'OBJECT', 'UPDATE') AS CanUpdate,
       HAS_PERMS_BY_NAME('dbo.Customers', 'OBJECT', 'DELETE') AS CanDelete;
REVERT;

Output:

CanUpdate   CanDelete  
----------- -----------
          1           0

How does this work? The app user can UPDATE dbo.Customers but not DELETE, because GRANT gives exactly what you list. SCHEMA::sales also covers tables added later.

For procedures, use GRANT EXECUTE ON dbo.usp_Name TO user, then execute the stored procedure as that user.

Add Users to db_datareader and db_datawriter With ALTER ROLE

db_datareader reads every user table and view; db_datawriter inserts, updates and deletes in every user table. Add members with ALTER ROLE:

SET NOCOUNT ON;
ALTER ROLE db_datareader ADD MEMBER DfqReader_Megan;
ALTER ROLE SalesReaders  ADD MEMBER DfqReader_Alex;

SELECT CAST(r.name AS varchar(14)) AS RoleName,
       CAST(m.name AS varchar(16)) AS Member
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS r ON r.principal_id = rm.role_principal_id
JOIN sys.database_principals AS m ON m.principal_id = rm.member_principal_id
WHERE m.name <> 'dbo'
ORDER BY RoleName;

Output:

RoleName       Member          
-------------- ----------------
db_datareader  DfqReader_Megan 
SalesReaders   DfqReader_Alex

How does this work? ADD MEMBER replaces the old sp_addrolemember, and sys.database_role_members lists who’s in each role. DROP MEMBER takes someone out.

Azure SQL uses the same statement once you create a SQL authentication user in Azure SQL.

Why Does DENY Beat GRANT? (Error 229)

Same Employees table, but salaries are now off limits, so I DENY Megan SELECT on it:

SET NOCOUNT ON;
DENY SELECT ON dbo.Employees TO DfqReader_Megan;

EXECUTE AS USER = 'DfqReader_Megan';
BEGIN TRY SELECT COUNT(*) AS Employees FROM dbo.Employees; END TRY
BEGIN CATCH PRINT CONCAT(ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;
REVERT;

REVOKE SELECT ON dbo.Employees FROM DfqReader_Megan;

EXECUTE AS USER = 'DfqReader_Megan';
SELECT COUNT(*) AS Employees FROM dbo.Employees;
REVERT;

Output:

229: The SELECT permission was denied on the object 'Employees', database 'DfqPermissions', schema 'dbo'.
Employees  
-----------
          2

How does this work? DENY beats GRANT, even one inherited from a role, so the query fails with error 229. REVOKE deletes the DENY, and the role’s GRANT returns 2 employees.

DENY doesn’t apply to sysadmin members or object owners. For the CATCH pattern, see TRY…CATCH in a SQL Server stored procedure.

How to Check User Permissions in SSMS (Securables Page)

1. In SSMS 21 Object Explorer, expand Databases > DfqPermissions > Security > Users.
2. Right-click DfqApp_Orders and select Properties.
3. Select the Securables page, then Search > Specific objects.
4. Select Object Types, tick Tables, then Browse and tick dbo.Customers.
5. Read the Explicit tab (Grant, With Grant, Deny) or the Effective tab.

Ticking Grant or Deny runs the same statements. First, install SQL Server Management Studio.

Which Method Should You Use to Check SQL Server Permissions?

Question	Use
What can this user do on a table?	EXECUTE AS + fn_my_permissions
Can they INSERT, yes or no?	HAS_PERMS_BY_NAME
Who was granted what?	sys.database_permissions
Who’s in which role?	sys.database_role_members

Things to Keep in Mind

• Always REVERT after EXECUTE AS, or the rest of the script runs as that user.
• REVOKE removes a GRANT or DENY; it never blocks anything.
• A table-level DENY doesn’t override a column-level GRANT (kept for backward compatibility).
• Failed logins land in the error log; here’s how to view SQL Server error logs.

Once your permissions are sorted, these are worth a look:

• Check database encryption in SQL Server
• Encrypt sensitive columns in Azure SQL Database
• Reset a password in Azure SQL Database
• List users and roles in PostgreSQL

Frequently Asked Questions