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

compdigit44
compdigit44 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
lcohanDatabase Analyst

Commented:
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

Author

Commented:
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
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
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
lcohanDatabase Analyst

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

Author

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

Author

Commented:
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
lcohanDatabase Analyst

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

Author

Commented:
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..
SQL Expert/Infrastructure Architect
Commented:
Here is a script that will show all information on all databases for you.

I just created it for you.

It will show multiple entrys if a user has db_datareader and db_datawriter access.

Regards Marten
SET NOCOUNT ON
Declare @SQL as varchar(max)
Declare @DBName as sysname


create table #testdata ( UserName varchar(50) ,GroupName varchar(50) ,LoginName
varchar(50) ,DefDBName varchar(50) ,DefSchemaName varchar(50) ,UserID smallint ,SID varbinary(85) )

create table #testdata2 ( DatabaseName sysname, UserName varchar(50) ,GroupName varchar(50) ,LoginName
varchar(50) ,DefDBName varchar(50) ,DefSchemaName varchar(50) ,UserID smallint ,SID varbinary(85) )


Declare @dbNames as table(
DBName sysname
)

insert into @dbNames
select name from sys.databases
order by name ASC

While exists(select * from @dbNames)
Begin
select TOP 1 @DBName=DBName from @dbNames

SET @SQL = 'use [' + @DBName + '];insert into #testdata exec sp_helpuser'
print @sql
EXEC (@sql)
SET @SQL = 'Insert into #testdata2 Select ''' + @DBName + ''', * from #testdata'
print @sql
EXEC (@sql)



DELETE @dbNames where @DBName=DBName
END

select	 DatabaseName
		,UserName 
		,GroupName 
		,LoginName
		,DefDBName 
		,DefSchemaName 
from #testdata2

IF OBJECT_ID('TempDB..#testdata') IS NOT NULL
Drop TABLE #testdata;

IF OBJECT_ID('TempDB..#testdata2') IS NOT NULL
Drop TABLE #testdata2;

Open in new window

Author

Commented:
This is PERFECT!!!!!!!!!!
Marten RuneSQL Expert/Infrastructure Architect

Commented:
Youre welcome

Regards Marten

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial