|
-
Oct 29th, 2012, 10:09 AM
#1
SSMS 2005, Sql 2000, get all database user's roles
I'm using SSMS 2005 connecting to a Sql Server 2000 server. What I'm needing to do is get all of the roles for each DB user for each database on the server. There are a few hundred users each with different roles on the different databases, it would take forever to make an Excel file to record this info if I have to open each person for each database so I'm hoping there's a quick way to do this.
Here's a screenshot of what I'm needing:
-
Oct 29th, 2012, 11:16 AM
#2
Re: SSMS 2005, Sql 2000, get all database user's roles
I think I might have it:
Code:
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
(
ServerName varchar(256),
DBName SYSNAME,
[Name] SYSNAME,
GroupName SYSNAME NULL,
LoginName SYSNAME NULL,
default_database_name SYSNAME 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
'
SELECT
@@SERVERNAME,
''?'' as DBName,
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 dbo.sysLogins l
ON u.sid = l.sid
WHERE u.islogin = 1 OR u.isntname = 1 OR u.isntgroup = 1
/*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
'
SELECT
@@SERVERNAME,
''?'',
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 like ''tester''*/
order by u.name
'
SELECT DBName as [Database], [Name], GroupName, LoginName
FROM #TUser
WHERE GroupName <> 'public'
ORDER BY DBName, [Name], GroupName
DROP TABLE #TUser
END
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|