0

I am trying to find the permissions for a specific user for a specific database on SQL Server 2008 R2.

My database is testDB and name of user is testUser.

This is the script I am using

SELECT
    dp.type_desc AS principal_type_desc,
    dbp.class_desc,
    OBJECT_NAME(dbp.major_id) AS object_name,
    dbp.permission_name,
    dbp.state_desc AS permission_state_desc
FROM
    sys.database_permissions dbp
INNER JOIN 
    sys.database_principals dp ON dbp.grantee_principal_id = dp.principal_id
WHERE
    USER_NAME(dbp.grantee_principal_id) = 'TestUser'

Not sure how to get permissions for a specific user in a specific database

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
san
  • 229
  • 2
  • 3
  • 11
  • [SYS.DATABASE_PERMISSIONS?](http://msdn.microsoft.com/en-us/library/ms188367%28v=sql.100%29.aspx) – Andrew Sep 03 '14 at 16:36
  • possible duplicate of [SQL Server query to find all permissions/access for all users in a database](http://stackoverflow.com/questions/7048839/sql-server-query-to-find-all-permissions-access-for-all-users-in-a-database) – M.Ali Sep 03 '14 at 16:59

1 Answers1

1

If you are sa, you can change the context and then use the built in function

execute as Login = 'testUser';
select * from sys.fn_my_permissions(NULL,'Database')
Mark Wojciechowicz
  • 4,287
  • 1
  • 17
  • 25