0

I want to make a stored procedure to return all permissions for a input user and I have no idea how to return all permissions.

Any suggestions are welcome, Thanks!

Alienware
  • 321
  • 1
  • 3
  • 15
  • 1
    There more than one kind of thing within SQL Server that could be called a "permission", which do you mean? Give us some examples. – RBarryYoung Jul 23 '14 at 16:57
  • 1
    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) – huMpty duMpty Jul 23 '14 at 17:04
  • 2
    Have you tried anything like [googling](https://www.google.co.uk/?gfe_rd=cr&ei=3erPU93AB8HH8gfSxYLYCA&gws_rd=ssl#q=get%20sql%20server%20user%20permissions) ? –  Jul 23 '14 at 17:06

1 Answers1

1
CREATE PROCEDURE Get_User_Permissions
@UserName VARCHAR(100)
AS
BEGIN
  SET NOCOUNT ON;

    SELECT   pc.name            AS [User Name]
            ,pc.type_desc       AS [User Type]
            ,pm.permission_name AS [Permission Name]
            ,pm.state_desc      AS [Permission Status]
            ,pm.class_desc      AS [Object Type]
            ,object_name(pm.major_id) AS [object_Name]
    FROM sys.database_principals pc
    LEFT JOIN  sys.database_permissions pm
    ON pm.grantee_principal_id = pc.principal_id
    WHERE pr.name = @UserName
END
M.Ali
  • 67,945
  • 13
  • 101
  • 127