Sql server get database permissions by name

We can get permissions by sp_helprotect in a specific database. Then how to get permissions of a specific database, for example master or tempdb?

I have tried this statement right by mysel:

  • Is it possible to combine NEAR and FORMSOF together in a fulltext search?
  • connection string to a SQL Server cluster
  • What is a Trusted Connection?
  • How do I preview a destructive SQL query?
  • Need help with the Merge statement
  • How to Display Page Number in Report Body of SSRS 2008 R2?
  • SELECT major_id, minor_id, grantor_principal_id, grantee_principal_id, permission_name, pr1.name as GRANTEE, pr2.name as GRANTOR, pr2.create_date, pr2.modify_date 
        FROM (SELECT * FROM sys.database_permissions where class = 4) as pe
    JOIN sys.database_principals AS pr1
        ON pe.grantee_principal_id = pr1.principal_id
    JOIN sys.database_principals AS pr2
        ON pe.grantor_principal_id = pr2.principal_id
    

    But the major id is 0, so I can’t get the permission exist on which object.

    One Solution collect form web for “Sql server get database permissions by name”

    MSDN for sys.database_permissions states that:

    Returns a row for every permission or column-exception permission in the database. For columns, there is a row for every permission that is different from the corresponding object-level permission. If the column permission is the same as the corresponding object permission, there will be no row for it and the actual permission used will be that of the object.

    So what this means is that your query will return the permissions only for the context of the database in which you are running it. What you are currently doing though is to filter with permissions that exist on Database Principals ( the class = 4 part of your query). If you want database objects (such as tables, stored procedures etc) you should also include 1 in your class WHERE clause.

    Edit 1: To get the permissions of tempdb or master you can run the query twice, using:

    USE master;
    GO
    /* Your query here that will return the permissions for master */
    USE tempdb;
    GO
    /* Your query here that will return the permissions for master */
    

    Edit 2: If you want a single query you can use this one:

    SELECT major_id, minor_id, grantor_principal_id, grantee_principal_id,   permission_name, pr1.name as GRANTEE, pr2.name as GRANTOR, pr2.create_date, pr2.modify_date , 'master' as db
    FROM 
    (SELECT * FROM master.sys.database_permissions where class = 4) as pe
    INNER JOIN master.sys.database_principals AS pr1
       ON pe.grantee_principal_id = pr1.principal_id
    INNER JOIN master.sys.database_principals AS pr2
       ON pe.grantor_principal_id = pr2.principal_id
    UNION ALL 
    SELECT major_id, minor_id, grantor_principal_id, grantee_principal_id, permission_name, pr1.name as GRANTEE, pr2.name as GRANTOR, pr2.create_date, pr2.modify_date , 'tempdb' as db
    FROM 
    (SELECT * FROM tempdb.sys.database_permissions where class = 4) as pe
    INNER JOIN tempdb.sys.database_principals AS pr1
       ON pe.grantee_principal_id = pr1.principal_id
    INNER JOIN tempdb.sys.database_principals AS pr2
       ON pe.grantor_principal_id = pr2.principal_id;
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.