I added a couple more features as well, to extend to object level permissions as well and check for orphaned logins. It's based of the main table your script creates when it derives the new tables.
--First section, slightly tweaked from original script in posting
/**
Script: list all Usernames, Roles for all the databases.
Author: Shiva Challa (http://challa.info)
and the database Roles that the user belongs to in all the databases.
Also, you can use this script to get the roles of one user in all the databases.
Directions of Use:
For All Users list: You can directly run this script in SQL Server Management studio
For a specific user:
1. Find this code and u.name like ''tester''
2. Uncomment the code
3. Replace the Name ''tester'' with the username you want to search on.
Resultset:
DBName: Database name that the user exists in.
Name: user name.
GroupName: Group/Database Role that the user is a part of.
LoginName: Actual login name, if this is null, Name is used to connect.
default_database_name
default_schema_name
principal_id
sid
Change History:
08/26/2008 Shiva Challa - Removed the misc characters from the "Select of EXEC sp_MSForEachdb" statement.
09/02/2008 Cathy Greenselder - Convert to SQL2000
(default_database_name not in SQL2K)
(default_schema_name not in SQL2K)
(principal_id not in SQL2K)
(uid is in SQL2K
10/08/2008 Shiva Challa - Added Cathy's script to the original script with an IF logic to make it work for both SQL 2000 and SQL 2005.
- Added code to use SysObjects in 2000 instead of sys.objects
10/09/2008 Cathy Greenselder - for 2000, switched the SysUsers join to use "m.groupuid" instead of "m.memberuid"
**/
USE MASTER
GO
BEGIN
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.sys.objects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
IF EXISTS (
SELECT TOP 1 *
FROM Tempdb.dbo.sysobjects (nolock)
WHERE name LIKE '#TUser%')
DROP TABLE #TUser
CREATE TABLE #tuser (
rownum int identity(1,1),
DBName VARCHAR(50),
[Name] varchar(128),
GroupName varchar(128) NULL,
LoginName varchar(128) NULL,
default_database_name VARCHAR(50) NULL,
default_schema_name VARCHAR(256) NULL,
Principal_id INT,
sid VARBINARY(85))
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '8'
INSERT INTO #TUser
EXEC sp_MSForEachdb
'use ?;
SELECT
db_name(),
u.name As UserName,
CASE
WHEN (r.uid IS NULL) THEN ''public''
ELSE r.name
END AS GroupName,
l.name AS LoginName,
NULL AS Default_db_Name,
NULL as default_Schema_name,
u.uid,
u.sid
FROM .dbo.sysUsers u
LEFT JOIN ( .dbo.sysMembers m
JOIN .dbo.sysUsers r
ON m.groupuid = r.uid)
ON m.memberuid = u.uid
LEFT JOIN master.dbo.syslogins l
ON u.sid = l.sid
WHERE (u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1) and u.name not in(''dbo'', ''guest'', ''INFORMATION_SCHEMA'')
/*and u.name like ''tester''*/
ORDER BY u.name
'
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR(1)),1) = '9'
INSERT INTO #TUser
EXEC sp_MSForEachdb 'use ?;
SELECT
db_name(),
u.name,
CASE
WHEN (r.principal_id IS NULL) THEN ''public''
ELSE r.name
END GroupName,
l.name LoginName,
l.default_database_name,
u.default_schema_name,
u.principal_id,
u.sid
FROM .sys.database_principals u
LEFT JOIN ( .sys.database_role_members m
JOIN .sys.database_principals r
ON m.role_principal_id = r.principal_id)
ON m.member_principal_id = u.principal_id
LEFT JOIN .sys.server_principals l
ON u.sid = l.sid
WHERE u.TYPE <> ''R'' and u.name not in (''dbo'', ''guest'', ''sys'', ''INFORMATION_SCHEMA'')
/*and u.name like ''tester''*/
order by u.name
'
SELECT Name "User", dbname, GroupName, LoginName
FROM #TUser
order by Name
--DROP TABLE #TUser -- not dropping now as needed for next two sections.
END
go
------------------------------------
-- NEW SECTIONS
------------------------------------
-- This second section generates all object level permissions
create table #Tobjectlevel
(
dbname varchar(128),
owner varchar(128),
object varchar(128),
grantee varchar(128),
grantor varchar(128),
protecttype varchar(128),
action varchar(128),
[column] varchar(512)
)
exec sp_msforeachdb 'use ?;
insert into #TobjectLevel(owner, object, grantee, grantor, protecttype, action, [column])
exec sp_helprotect
update #TobjectLevel
set dbname = db_name() where dbname is null'
select grantee [User], dbname, object, action [Permissions] from #Tobjectlevel
where grantee in (select Name from #TUser)
order by upper(grantee) asc
go
-- This section creates a list of orphaned users (i.e. those not in #TUser)
create table #TOrphans
(
[User] sysname,
dbname sysname
)
exec sp_msforeachdb 'use ?;
insert into #TOrphans
select name, db_name() from sysusers
where name not in (select name from #TUser)
and name not in (''dbo'', ''guest'', ''INFORMATION_SCHEMA'')
and roles <> 0x00
'
select * from #TOrphans
go
drop table #TOrphans
drop table #Tobjectlevel
drop table #TUser
Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein