Permissions

Question

Permissions

sunny.tjk

SSCertifiable

Points: 5041
More actions
September 6, 2012 at 1:20 pm

#393122

Is there a way to check who has what kind of permissions on a database? I just have read permissions to the database.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Try this stack of queries; they all give you some various information about what permissions your current login belongs to:

select * from fn_my_permissions(null,'SERVER')

select * from fn_my_permissions(null,'DATABASE')

--some inferred roles I may belong to:

SELECT

IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],

IS_MEMBER('db_owner') AS [Is_DB_owner],

IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],

IS_MEMBER('db_datawriter') AS [Is_DB_Datawriter],

IS_MEMBER('db_datareader') AS [Is_DB_Datareader]

--all the database roles I belong to:

SELECT

*

FROM sys.database_permissions permz

INNER JOIN sys.database_principals userz

ON permz.grantee_principal_id = userz.principal_id

WHERE userz.principal_id IN( --the userid and also all the roles i'm directly in.

--that doesn't seem to get the role-within-a-role info those

SELECT

USER_ID() AS id

UNION ALL

SELECT

rolezx.role_principal_id

FROM sys.database_principals userzx

LEFT OUTER JOIN sys.database_role_members rolezx

ON userzx.principal_id = rolezx.member_principal_id

LEFT OUTER JOIN sys.database_principals decripz

ON rolezx.role_principal_id = decripz.principal_id

WHERE userzx.name = USER_NAME())

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

Lowell (9/6/2012)
Try this stack of queries; they all give you some various information about what permissions your current login belongs to:
select * from fn_my_permissions(null,'SERVER')
select * from fn_my_permissions(null,'DATABASE')
--some inferred roles I may belong to:
SELECT
IS_SRVROLEMEMBER ('sysadmin') AS [Is_ServerAdmin_Sysadmin],
IS_MEMBER('db_owner') AS [Is_DB_owner],
IS_MEMBER('db_ddladmin') AS [Is_DDL_Admin],
IS_MEMBER('db_datawriter') AS [Is_DB_Datawriter],
IS_MEMBER('db_datareader') AS [Is_DB_Datareader]
--all the database roles I belong to:
SELECT
*
FROM sys.database_permissions permz
INNER JOIN sys.database_principals userz
ON permz.grantee_principal_id = userz.principal_id
WHERE userz.principal_id IN( --the userid and also all the roles i'm directly in.
--that doesn't seem to get the role-within-a-role info those
SELECT
USER_ID() AS id
UNION ALL
SELECT
rolezx.role_principal_id
FROM sys.database_principals userzx
LEFT OUTER JOIN sys.database_role_members rolezx
ON userzx.principal_id = rolezx.member_principal_id
LEFT OUTER JOIN sys.database_principals decripz
ON rolezx.role_principal_id = decripz.principal_id
WHERE userzx.name = USER_NAME())

Thanks Lowell.

I actually need to know who all are added under the database roles of a database.

george sibbald SSC Guru Points: 104210 More actions · Answer 3

if you just need to know who is in what database role use this

SELECT 'EXEC sp_addrolemember [' + dp.name + '], [' + USER_NAME(drm.member_principal_id) + '] ' AS [-- AddRolemembers]

FROM sys.database_role_members drm

INNER JOIN sys.database_principals dp ON dp.principal_id = drm.role_principal_id

where USER_NAME(drm.member_principal_id) != 'dbo'

order by drm.role_principal_id

that actually reverse engineers the sql to recreate role members, if you don't need that adjust accordingly

---------------------------------------------------------------------