Menu
How can we show all the securable that is added in any particular role in script?
219
To find all the role assignments to users in SQL Server database, you can use the following query. SELECT r.name role_principal_name, m.name AS member_principal_name FROM sys. database_role_members rm JOIN sys. database_principals r ON rm.
Querying DBA/USER Privilege Views For example, a DBA wishing to view all system privileges granted to all users would issue the following query: SELECT * FROM DBA_SYS_PRIVS; The DBA_SYS_PRIVS view contains three columns of data: GRANTEE is the name, role, or user that was assigned the privilege.
In the navigation pane, click ACCESS CONTROL. In the lower navigation pane, click Roles. In the display pane, the roles are listed. Select the role whose permissions you want to view.
Given the fact that there are server-level roles and database-level roles, let us explore the server-level permissions assigned to the public role. SELECT sp. state_desc as "Permission State", sp. permission_name as "Permission", sl.name "Principal Name",sp.
SELECT
OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
FROM
sys.database_permissions p
WHERE
p.class = 1 AND
OBJECTPROPERTY(major_id, 'IsMSSHipped') = 0
ORDER BY
OBJECT_NAME(major_id), USER_NAME(grantee_principal_id), permission_name
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
