February 28, 2008 at 2:38 pm
I'm looking for a script to give me a simple list of the database name, Login ID and Role. Similar to the following post, but for 2000. I found a few online but they are not giving me the same output as 2005 and need it for comparison on same databases but different version. Any help appreciated!
http://qa.sqlservercentral.com/Forums/Topic455264-359-1.aspx#bm460030
¤ §unshine ¤
February 28, 2008 at 2:58 pm
master.dbo.sysdatabases will get you the database list.
You need to use sysusers in the database. That has users and roles in it. syspersmissions has the permissions linked on uid = sysusers.grantee.
That should get you started
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 28, 2008 at 3:43 pm
so far I have this but it only seems to bring back public role.
SELECT DISTINCT sysdatabases.name AS [Database], sysxlogins.name AS [User], sysusers.name AS role
FROM sysxlogins INNER JOIN
sysdatabases ON sysxlogins.dbid = sysdatabases.dbid CROSS JOIN
sysusers INNER JOIN
syspermissions ON sysusers.uid = syspermissions.grantee
ORDER BY sysdatabases.name
¤ §unshine ¤
February 28, 2008 at 7:05 pm
You'll be wanting the version I did for my SQL 2000 servers then. 😉
[font="Courier New"]DECLARE @name sysname,
@sql nvarchar(4000),
@maxlen1 smallint,
@maxlen2 smallint,
@maxlen3 smallint
IF EXISTS (SELECT TABLE_NAME FROM tempdb.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '#tmpTable%')
DROP TABLE #tmpTable
CREATE TABLE #tmpTable
(
DBName sysname NOT NULL ,
UserName sysname NOT NULL,
RoleName sysname NOT NULL
)
DECLARE c1 CURSOR for
SELECT name FROM master..sysdatabases
OPEN c1
FETCH c1 INTO @name
WHILE @@FETCH_STATUS >= 0
BEGIN
SELECT @sql =
'INSERT INTO #tmpTable
SELECT N'''+ @name + ''', a.name, c.name
FROM [' + @name + ']..sysusers a
JOIN [' + @name + ']..sysmembers b ON b.memberuid = a.uid
JOIN [' + @name + ']..sysusers c ON c.uid = b.groupuid
WHERE a.name != ''dbo'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT @maxlen1 = COALESCE((MAX(LEN(DBName)) + 2), 1)
FROM #tmpTable
SELECT @maxlen2 = COALESCE((MAX(LEN(UserName)) + 2), 1)
FROM #tmpTable
SELECT @maxlen3 = COALESCE((MAX(LEN(RoleName)) + 2), 1)
FROM #tmpTable
SET @sql = 'SELECT LEFT(DBName, ' + LTRIM(STR(@maxlen1)) + ') AS ''DB Name'', '
SET @sql = @sql + 'LEFT(UserName, ' + LTRIM(STR(@maxlen2)) + ') AS ''User Name'', '
SET @sql = @sql + 'LEFT(RoleName, ' + LTRIM(STR(@maxlen3)) + ') AS ''Role Name'' '
SET @sql = @sql + 'FROM #tmpTable '
SET @sql = @sql + 'ORDER BY DBName, UserName'
EXEC(@sql)
DROP TABLE #tmpTable
GO
[/font]
Same comments apply as in my other post referenced above.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 29, 2008 at 9:06 am
Try this if it helps.
---------------------------------------------------------------------------------------
-- run this from master database only
USE master
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers
GO
DECLARE @DBName VARCHAR(32)
DECLARE @SQLCmd VARCHAR(1024)
SELECT sid,
loginname AS [Login Name],
dbname AS [Default Database],
CASE isntname
WHEN 1 THEN 'AD Login'
ELSE 'SQL Login'
END AS [Login Type],
CASE
WHEN isntgroup = 1 THEN 'AD Group'
WHEN isntuser = 1 THEN 'AD User'
ELSE ''
END AS [AD Login Type],
CASE sysadmin
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [sysadmin],
CASE [securityadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [securityadmin],
CASE [serveradmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [serveradmin],
CASE [setupadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [setupadmin],
CASE [processadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [processadmin],
CASE [diskadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [diskadmin],
CASE [dbcreator]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [dbcreator],
CASE [bulkadmin]
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [bulkadmin]
INTO ##Users
FROM dbo.syslogins
SELECT [Login Name],
[Default Database],
[Login Type],
[AD Login Type],
[sysadmin],
[securityadmin],
[serveradmin],
[setupadmin],
[processadmin],
[diskadmin],
[dbcreator],
[bulkadmin]
FROM ##Users
ORDER BY [Login Type], [AD Login Type], [Login Name]
CREATE TABLE ##DBUsers (
[Database] VARCHAR(64),
[Database User ID] VARCHAR(64),
[Server Login] VARCHAR(64),
[Database Role] VARCHAR(64))
DECLARE csrDB CURSOR FOR
SELECT name
FROM sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')
OPEN csrDB
FETCH NEXT
FROM csrDB
INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
--
SELECT @SQLCmd = 'INSERT ##DBUsers ' +
' SELECT ''' + @DBName + ''' AS [Database],' +
' su.[name] AS [Database User ID], ' +
' COALESCE (u.[Login Name], ''** Orphaned **'') AS [Server Login], ' +
' COALESCE (sug.name, ''Public'') AS [Database Role] ' +
' FROM [' + @DBName + '].[dbo].[sysusers] su' +
' LEFT OUTER JOIN ##Users u' +
' ON su.sid = u.sid' +
' LEFT OUTER JOIN ([' + @DBName + '].[dbo].[sysmembers] sm ' +
' INNER JOIN [' + @DBName + '].[dbo].[sysusers] sug ' +
' ON sm.groupuid = sug.uid)' +
' ON su.uid = sm.memberuid ' +
' WHERE su.hasdbaccess = 1' +
' AND su.[name] != ''dbo'' '
EXEC (@SQLCmd)
FETCH NEXT
FROM csrDB
INTO @DBName
END
CLOSE csrDB
DEALLOCATE csrDB
SELECT *
FROM ##DBUsers
ORDER BY [Database], [Database User ID]
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##Users' AND type in (N'U'))
DROP TABLE ##Users
GO
IF EXISTS (SELECT * FROM tempdb.dbo.sysobjects WHERE name = '##DBUsers' AND type in (N'U'))
DROP TABLE ##DBUsers
GO
SQL DBA.
February 29, 2008 at 9:37 am
You can use the script in the link given below
http://www.sql-articles.com/index.php?page=Scripts/permission_list.php
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 29, 2008 at 11:40 am
Scott's worked perfectly. Thank you so much!
😀
¤ §unshine ¤
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply