Avatar of compdigit44
compdigit44
 asked on

SQL 2005 & 2008 export all SQL and Windows to CSV and list which DB they have access to

I'm not a DBA by a far shot, my my manager has asked me for a list of users to how access to our SQL servers what which DB they have access to and why type of access. IS this possible. Some of my SQL servers have 60+ databases? Is there an easy way to do this?
DatabasesMicrosoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008SQL

Avatar of undefined
Last Comment
Marten Rune

8/22/2022 - Mon
lcohan

lcohan

Or you could put the script below (that I'm using) in a SQL Stored Proc and add a DBname parameter or just run it against each db on a server by using (undocumented) sp_msforeachdb:


      SET NOCOUNT ON;

      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
compdigit44

ASKER
Is there a way I could run the script and it will automatically run through all of my DB's. So of my SQL server have over 60+ DB's on them
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
compdigit44

ASKER
I have never used the sp_msforeachdb stored procedure how would I use this with the code you listed above. Sorry I'm not a DBA
lcohan

You would run a query like below against each server in SSMS Query and you could save the results as csv or pipe the results directly into a csv:


EXECUTE master.sys.sp_MSforeachdb 'USE [?];
EXEC sp_executesql
N''
      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
      )
      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
      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'''
compdigit44

ASKER
I will try this when I'm in the office next week.... thanks again
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
compdigit44

ASKER
I ran the query above and it did run but the results are very confusing. IN my results windows I have four different windows with all of this informatin.

I'm just looking for a simple way to list the following

User ABC
db1 read & write
db2 read

USER DEF
db3 read
db1 owner
lcohan

You asked for a "...a list of users to how access to our SQL servers what which DB they have access to and why type of access. " and the script posted above it does exactly what you asked for - a list of users from each DB on the server where is executed and all rights/permission associated with eachy and every user in each database. I added db_name column to the script below to make it more clear and sorry I missed that in the previous script.



EXECUTE master.sys.sp_MSforeachdb 'USE [?];
EXEC sp_executesql
N''
      WITH    perms_cte as
      (
                  select db_name() as db_name,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
      )
      SELECT p.db_name,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
      SELECT p.db_name,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'''
compdigit44

ASKER
Thanks, this helps I do not see a column that list what type off access the user has to the db though.. For example, reader, writer etc..
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER CERTIFIED SOLUTION
Marten Rune

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
compdigit44

ASKER
This is PERFECT!!!!!!!!!!
Marten Rune

Youre welcome

Regards Marten