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

You could run scripts like from link below against those servers/DBs and get the data you need:

http://consultingblogs.emc.com/jamiethomson/archive/2007/02/09/SQL-Server-2005_3A00_-View-all-permissions--_2800_2_2900_.aspx

http://www.sql-server-performance.com/2002/object-permission-scripts/

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

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

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

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'''

I will try this when I'm in the office next week.... thanks again

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

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'''

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 PERFECT!!!!!!!!!!

Youre welcome

Regards Marten