Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SQL Server 2005: View all permissions (2) - Explode the roles!

Two days ago I posted a blog entry here that showed how the following SQL statement:

   select dp.NAME AS principal_name,

           dp.type_desc AS principal_type_desc,

           o.NAME AS object_name,

           p.permission_name,

           p.state_desc AS permission_state_desc

   from    sys.database_permissions p

   left    OUTER JOIN sys.all_objects o

   on     p.major_id = o.OBJECT_ID

   inner   JOIN sys.database_principals dp

   on     p.grantee_principal_id = dp.principal_id

 

could be used to produce a list of permissions defined in a SQL Server database. It worked OK but I took a look at it and decided it could be better so today I wrote the following, more concise version (and with an exta column - class_desc):

select USER_NAME(p.grantee_principal_id) AS principal_name,

        dp.type_desc AS principal_type_desc,

        p.class_desc,

        OBJECT_NAME(p.major_id) AS object_name,

        p.permission_name,

        p.state_desc AS permission_state_desc

from    sys.database_permissions p

inner   JOIN sys.database_principals dp

on     p.grantee_principal_id = dp.principal_id

Again though, I thought I could make this better. The above statements will give me all the defined permissions. However this only gives you the roles on which permissions are defined. It doesn't allow you to explode the roles to the role members and display their inherited permissions. That is what the following script does:

 

WITH    perms_cte as

(

        select USER_NAME(p.grantee_principal_id) AS principal_name,

                dp.principal_id,

                dp.type_desc AS principal_type_desc,

                p.class_desc,

                OBJECT_NAME(p.major_id) AS object_name,

                p.permission_name,

                p.state_desc AS permission_state_desc

        from    sys.database_permissions p

        inner   JOIN sys.database_principals dp

        on     p.grantee_principal_id = dp.principal_id

)

--users

SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast(NULL as sysname) as role_name

FROM    perms_cte p

WHERE   principal_type_desc <> 'DATABASE_ROLE'

UNION

--role members

SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name

FROM    perms_cte p

right outer JOIN (

    select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name--,*

    from    sys.database_role_members rm

    INNER   JOIN sys.database_principals dp

    ON     rm.member_principal_id = dp.principal_id

) rm

ON     rm.role_principal_id = p.principal_id

order by 1

 

Here's the output it produces. Note the last column which defines whether the permission is defined explicitly or whether it is inherited from a role:

 

As before, this will only work on SQL Server 2005 and beyond.

There may of course be a hundred and one other ways of achieving this, and I expect many of you already have your own method, but this works for me. For now.

 

-Jamie

 

UPDATE, 2008-02-26: This query does not return information about the 'public' database role. This is because sys.database_role_members does not contain info about that role. This kind of makes sense given that the public role "Cannot have users, groups, or roles assigned to it because they belong to the role by default"

 

Published Friday, February 09, 2007 11:54 PM by jamie.thomson

Comments

 

SSIS Junkie : SQL Server 2005: View all permissions said:

February 10, 2007 1:11 AM
 

kb said:

Pls, I need also column permissions.

February 15, 2007 4:05 PM
 

Sirish said:

Can this work for table permissions as well?..

March 1, 2007 1:29 PM
 

JW said:

I need to map the sql Usernames to actual Windows LoginNames.  This was automatic as part of sp_helpuser in SQL 2K, however now only SOME Windows LoginNames are returned when running that procedure.  Any thoughts?  I can't find where the darn crossover tables are anywhere in SQL 2005... looked at system views, tables, Information_schema views, etc, tried all the sp_help* procedures, nada.  If you didn't name your original SQL Username exactly the same as your Windows (domainname\username for example), then you won't know where the SQL Username is mapped.  

March 6, 2007 10:53 PM
 

jamie.thomson said:

JW,

I've just spent the past 20minutes searching for a sys view that gives a link between a user and a login and I can't find anything - which amazes me.

Looks like it doesn't exist. Do you want to flag it at http://connect.microsoft.com?

-Jamie

March 6, 2007 11:21 PM
 

GP said:

Thank you this was very helpful for what I was jsut trying to do!!!

May 2, 2007 8:00 PM
 

poopants said:

excellent! thanks for the tips

May 31, 2007 8:23 PM
 

harveysburger said:

this is awesome

June 6, 2007 3:16 PM
 

jamie.thomson said:

Thanks very much. Could I ask you how you came across this post? I'm quite interested to know!

June 6, 2007 4:23 PM
 

JRM said:

I was searching for a way to map NT login ID's to NT Groups, DB Roles and permissions...i.e. given a login id, be able to find out what permissions the user has and why.  Found the blog via a google search for "tsq roles select permissions"

June 7, 2007 8:20 PM
 

jamie.thomson said:

thanks man. Much appreciated. Good to know Google is finding this stuff.

June 7, 2007 8:36 PM
 

Sinix said:

Hey! What about sys.database_principals and sys.server_principals views?

U can join them by sid column

June 9, 2007 1:46 AM
 

Christoph Muthmann said:

Hi Jamie,

try this one!

WITH    perms_cte(principal_name,principal_id, principal_type_desc,class_desc, [object_name], permission_name, permission_state_desc, login ) as

(

       select USER_NAME(p.grantee_principal_id) AS principal_name,

               dp.principal_id,

               dp.type_desc AS principal_type_desc,

               p.class_desc,

               OBJECT_NAME(p.major_id) AS [object_name],

               p.permission_name,

               p.state_desc AS permission_state_desc,

sp.name as login

       from    sys.database_permissions p

       left JOIN sys.database_principals dp on     p.grantee_principal_id = dp.principal_id

left Join sys.server_principals sp on dp.sid = sp.sid

)

-- users

SELECT p.principal_name,  p.principal_type_desc, login, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc, cast('<granted explicit>' as sysname) as role_name

FROM    perms_cte p

UNION

-- role members

SELECT rm.member_principal_name, rm.principal_type_desc, rm.login, p.class_desc, p.[object_name], p.permission_name, coalesce(p.permission_state_desc, '<Member of fixed database-role:>'), rm.role_name

FROM    perms_cte p

right outer JOIN (

   select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,user_name(role_principal_id) as role_name, sp.name as login

   from    sys.database_role_members rm

   INNER   JOIN sys.database_principals dp ON     rm.member_principal_id = dp.principal_id

left Join sys.server_principals sp on dp.sid = sp.sid

) rm

ON     rm.role_principal_id = p.principal_id

order by 2, 1, 4, 5, 6, 8

Kind regards,

Christoph

June 21, 2007 10:58 AM
 

John Reynolds said:

when I look at the permissions that public has in the master database, I come across 4 where I cannot find the object that the SELECT permission was granted on.

here's the sql

SELECT *

FROM SYS.DATABASE_PERMISSIONS P,

SYS.DATABASE_PRINCIPALS R

WHERE   P.GRANTEE_PRINCIPAL_ID=R.PRINCIPAL_ID and

permission_name='SELECT' and class_desc='OBJECT_OR_COLUMN' and

r.name='public'

order by r.name desc

November 8, 2007 3:57 PM
 

Peter said:

Wonderful script!

November 29, 2007 12:40 PM
 

John Frankhold said:

Thanks for the tip!

I want to suggest a tool from my experience with sql server administration that may be useful for managing sql permissions and that can help to get rid of scripting in some cases.

Take a look at <a href="http://www.scriptlogic.com/products/security-explorer/sql-server">security explorer's features</a>. I'm sure you'll love them.

My favourite and the most used ones are copy and paste permissions beetwen users or databases and searching for blank or weak passwords.

January 10, 2008 1:07 PM
 

Steve Hogg said:

Great script! Keep up the good work!

January 16, 2008 4:26 PM
 

Vasilis Kotronakis said:

Hi Jamie,

if the permissions were inherited from the Public db role, would that role appear in the role_name column?

February 26, 2008 1:07 PM
 

jamie.thomson said:

Vasilis,

You know you can try it out for yourself rather than waiting for an answer from me, right? :)

The answer is "yes, if you're in the public role then that will be captured by this SQL statement".

-Jamie

February 26, 2008 2:13 PM
 

Vasilis Kotronakis said:

Jamie,

I did try it and did not work, that' s why I asked you..

February 26, 2008 2:21 PM
 

jamie.thomson said:

Vasilis,

SORRY! I'm wrong, and you're right. it does not include members of the public role.

Damn, whoops, oh, gotcha, ah, eek........my bad!  Sorry about that!

I will update the blog entry accordingly.

-Jamie

February 26, 2008 2:43 PM
 

jamie.thomson said:

P.S. its because sys.database_role_members does not return information about the public role!

-Jamie

February 26, 2008 2:46 PM
 

Mark said:

This should allow you to link the login and the user. Let me know if you can't make it work.

drop table #permissions

go

WITH perms_cte as

(select USER_NAME(p.grantee_principal_id) AS principal_name, dp.principal_id, dp.type_desc AS principal_type_desc,

p.class_desc, OBJECT_NAME(p.major_id) AS object_name, p.permission_name, p.state_desc AS permission_state_desc, dp.sid

       from sys.database_permissions p

       inner JOIN sys.database_principals dp

       on p.grantee_principal_id = dp.principal_id)

--users

SELECT p.principal_name,  p.principal_type_desc, p.class_desc, p.[object_name], p.permission_name, p.permission_state_desc,

cast(NULL as sysname) as role_name, p.sid

into #permissions

FROM perms_cte p

WHERE principal_type_desc <> 'DATABASE_ROLE'

UNION

--role members

SELECT rm.member_principal_name, rm.principal_type_desc, p.class_desc, p.object_name, p.permission_name, p.permission_state_desc,rm.role_name, p.sid

FROM  perms_cte p

right outer JOIN

   (select role_principal_id, dp.type_desc as principal_type_desc, member_principal_id,user_name(member_principal_id) as member_principal_name,

user_name(role_principal_id) as role_name--,*

   from sys.database_role_members rm

   INNER JOIN sys.database_principals dp

   ON rm.member_principal_id = dp.principal_id) rm

ON rm.role_principal_id = p.principal_id

order by 1

--2

select s2.loginame, p.*

from #permissions p

left join sys.sysusers s1 on p.sid=s1.sid

left join sys.sysprocesses s2 on s1.uid=s2.uid

March 12, 2008 8:56 PM
 

Jeremy said:

You can also run this tool to get a report on all users and all permissions across all your SQL Servers.

http://www.idera.com/Products/SQLsecure/default.aspx

April 4, 2008 7:55 PM
 

Mike said:

Scriptlogic has a tool for this as well that works pretty good for us.

http://www.scriptlogic.com/products/enterprisesecurityreporter/sql

It collects all necessary information without any agents and reports on sql databases, object, schemas, users, role membership and server logins. All the reports are easy customizable without any additional sql coding using built-in drag and drop query builder.

May 8, 2008 4:19 PM
 

ilkay said:

thank you

May 29, 2008 9:46 AM
 

Billy the Cook said:

Maybe you like this one too. It shows all effective permissions (as oposed to only the explicitly granted permissions) of all users in a particular database on the database itself and the tables and view it contains.

DECLARE @doit nvarchar(max), @db sysname

SET @db = '<your databasename goes here>'

SET @doit =

N'USE '+@db+';

DECLARE @login sysname

DECLARE @table sysname

SET NOCOUNT ON;

DECLARE c_login CURSOR

   FOR SELECT [name]

         FROM sysusers

        WHERE hasdbaccess = 1

          AND [name] NOT IN (''dbo'')

CREATE TABLE #permissions ([User] sysname, [Object] sysname, [Permission] sysname)

OPEN c_login

FETCH NEXT FROM c_login

INTO @login

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE AS LOGIN = @login;

INSERT INTO #permissions

SELECT DISTINCT @login

 ,''DATABASE''

 ,permission_name AS [Permission]

 FROM fn_my_permissions(NULL, N''DATABASE'')

REVERT;

   DECLARE c_table CURSOR

       FOR SELECT [name]

 FROM sysobjects

WHERE type IN (''U'',''V'')

OPEN c_table

FETCH NEXT FROM c_table

INTO @table

WHILE @@FETCH_STATUS = 0

BEGIN

EXECUTE AS LOGIN = @login;

INSERT INTO #permissions

SELECT DISTINCT @login [User]

 ,@table AS [Table]

 ,permission_name AS [Permission]

 FROM fn_my_permissions(@table, N''OBJECT'')

REVERT;

FETCH NEXT FROM c_table

INTO @table

END

CLOSE c_table

DEALLOCATE c_table

FETCH NEXT FROM c_login

INTO @login

END

CLOSE c_login

DEALLOCATE c_login

SELECT '''+@db+''' [Database],[User],[Object],[Permission]

 FROM #permissions

DROP TABLE #permissions;';

EXECUTE sp_executesql @doit

July 3, 2008 4:18 PM
 

Oliver Kiss said:

Thanks, this was perfect!

September 4, 2008 8:03 PM
 

roy ashbrook said:

thanks to this article for the base script. i needed to get a list of all users and the roles they had

September 5, 2008 8:07 PM
 

asentell said:

Thanks Jamie. Your code saved me and my team a lot of time and work. I've modified your base sql script to generate commands to grant/deny/revoke permissions for all objects in the database:

SELECT  p.state_desc + ' ' + p.permission_name + ' ON [' + OBJECT_SCHEMA_NAME(p.major_id) + '].[' + OBJECT_NAME(p.major_id) + ']' +

CASE

WHEN p.state_desc IN ('GRANT','DENY') THEN ' TO '

WHEN p.state_desc IN ('REVOKE') THEN ' FROM '

END +

'[' + USER_NAME(p.grantee_principal_id) + '];'

FROM    sys.database_permissions p

       INNER JOIN sys.database_principals dp ON p.grantee_principal_id = dp.principal_id

INNER JOIN sys.objects o ON p.major_id = o.[object_id]

WHERE p.class_desc = 'OBJECT_OR_COLUMN' AND

OBJECT_SCHEMA_NAME(p.major_id) != 'sys' AND

OBJECT_NAME(p.major_id) NOT LIKE 'ZZ%'

ORDER BY o.type_desc, OBJECT_SCHEMA_NAME(p.major_id), OBJECT_NAME(p.major_id)

October 29, 2008 8:29 PM
 

clp said:

what about this?

what a simple and clear, more accurate.

select  su.name AS principal_name,

       dp.type_desc AS principal_type_desc,

       p.class_desc,

       ao.name AS object_name,

       p.permission_name,

       p.state_desc AS permission_state_desc

from    master.sys.database_permissions p,

       master.sys.database_principals dp,

       master.sys.all_objects ao,

       master.sys.sysusers su

where ao.OBJECT_ID = p.MAJOR_ID and

 p.grantee_principal_id = dp.principal_id and

 p.GRANTEE_PRINCIPAL_ID = su.UID

-- and dp.name = 'guest'

somebody edit this query for all database. (loop query)

i'm sql newbie.

November 18, 2008 1:55 AM
 

clp said:

it's better to this,

select  su.name AS principal_name,

       dp.type_desc AS principal_type_desc,

       ao.type_desc,

       ao.name AS object_name,

       p.permission_name,

       p.state_desc AS permission_state_desc

from    master.sys.database_permissions p,

       master.sys.database_principals dp,

       master.sys.all_objects ao,

       master.sys.sysusers su

where ao.OBJECT_ID = p.MAJOR_ID

 and p.grantee_principal_id = dp.principal_id

 and p.GRANTEE_PRINCIPAL_ID = su.UID

 --and dp.name = 'guest'

November 18, 2008 4:06 AM
 

Berechtigungen auf Tabellenebene - SQL 2005 | hilpers said:

January 20, 2009 2:16 PM
 

List of all logins and their permissions | keyongtech said:

January 24, 2009 11:25 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems