CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Alienware
Alienware

Reputation: 321

Stored procedure to return user permissions

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!

Upvotes: 0

Views: 88

Answers (1)

M.Ali
M.Ali

Reputation: 69494

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

Upvotes: 1

Related Questions