USERS HAVING PRIVILEGES ON DATABASE OBJECTS
Privileges can be provided to users of a database in 3 ways.
1. By assigning the user to a fixed database role
2. By assigning the user to a User defined database role
3. By granting privilege to a user directly on the database objects.
In this area of blog, we will cover the third part. The other two points are also covered in the blog.
Below is the query provided to track down the privileges on database objects for all the users in a particular database of SQL Server.
This will be very helpful in case of database refresh and database restore activity to retain the privileges of Users on a database.
Script will be generated in the last column of the query output. Do save it :)
SELECT
USER_NAME(p.grantee_principal_id) AS principal_name,
SUSER_SNAME(dp.sid) AS LOGIN_NAME,
dp.principal_id,
dp.type_desc AS principal_type_desc,
p.class_desc,
OBJECT_NAME(p.major_id) AS [object_name],
p.permission_name,
p.state_desc AS permission_state_desc,
'grant '+p.permission_name +' on object::['+OBJECT_NAME(p.major_id)+'] to '+USER_NAME(p.grantee_principal_id)+'' as 'script'
from
sys.database_permissions p
inner JOIN
sys.database_principals dp
on
p.grantee_principal_id = dp.principal_id
where dp.type_desc <>'DATABASE_ROLE' and
dp.principal_id>4 and OBJECT_NAME(p.major_id) is not null
