Hello techresearch7777777 !
Thanks for answering !
A query that lists all permissions , ( If null means no explicit permissions are set on tables or views)
The Object Type will show you if it is a Table or a View
SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(o.schema_id) AS SchemaName,
o.name AS ObjectName,
o.type_desc AS ObjectType,
dp.permission_name,
dp.state_desc,
p.name AS PrincipalName,
p.type_desc AS PrincipalType
FROM
sys.objects o
LEFT JOIN
sys.database_permissions dp ON dp.major_id = o.object_id AND dp.minor_id = 0
LEFT JOIN
sys.database_principals p ON dp.grantee_principal_id = p.principal_id
WHERE
o.type IN ('U', 'V') -- U for Table, V for View
ORDER BY
SchemaName, ObjectName, PrincipalName, permission_name;
Another one is the following only for Tables :
SELECT
DB_NAME() AS DatabaseName,
SCHEMA_NAME(t.schema_id) AS SchemaName,
t.name AS TableName,
dp.permission_name,
dp.state_desc,
p.name AS PrincipalName,
p.type_desc AS PrincipalType
FROM
sys.tables t
INNER JOIN
sys.schemas s ON t.schema_id = s.schema_id
LEFT JOIN
sys.database_permissions dp ON dp.major_id = t.object_id AND dp.minor_id = 0
LEFT JOIN
sys.database_principals p ON dp.grantee_principal_id = p.principal_id
ORDER BY
SchemaName, TableName, PrincipalName, permission_name;
And finally this will show you who has what ! : (lists out all the roles in a database, along with their members)
SELECT
role_principals.name AS RoleName,
member_principals.name AS MemberName,
member_principals.type_desc AS MemberType
FROM
sys.database_role_members role_members
JOIN
sys.database_principals role_principals ON role_members.role_principal_id = role_principals.principal_id
LEFT JOIN
sys.database_principals member_principals ON role_members.member_principal_id = member_principals.principal_id
ORDER BY
RoleName,
MemberName;
Keep in mind :
-
Explicit Permissions: Permissions assigned directly to the user.
-
Role-Based Permissions: Permissions assigned to roles that the user is a member of.
-
Inherited Role Memberships: If the user is a member of a role, and that role is a member of another role, the user inherits permissions from both roles.
I hope this helps!
Kindly mark the answer as Accepted and Upvote in case it helped!
Regards