Seeing Object Based Authorizations in SQL Server

Hello to everyone,

In this article, I will try to give information about seeing object-based privileges in SQL Server.

In SQL Server, in some cases, you may want to see object-based privileges.

You can easily do this using the code below.

SELECT permission_name,
       s.name,
       o.name,
       dpr.name,
       o.type_desc
FROM sys.database_permissions AS dp
    INNER JOIN sys.objects AS o
        ON dp.major_id = o.object_id
    INNER JOIN sys.schemas AS s
        ON o.schema_id = s.schema_id
    INNER JOIN sys.database_principals AS dpr
        ON dp.grantee_principal_id = dpr.principal_id
WHERE o.type_desc != 'SYSTEM_TABLE'
      AND dpr.name != 'guest'
      AND dpr.name != 'public'
ORDER BY s.name DESC;

Good luck to everyone in business and life.

27 Views

Yavuz Selim Kart

I try to explain what I know in software and database. I am still improving myself by doing research on many programming languages. Apart from these, I am also interested in Graphic Design and Wordpress. I also have knowledge about SEO and Social media management. In short, I am a determined person who likes to work hard.

You may also like...

Don`t copy text!