February 13, 2008 at 11:38 am
Hi there,
I'm looking for a query or script that can generate a list of all SQL Server login's on an instance, along with all the roles they are mapped to on what databases. I'm new to SQL Server and am unaware of what system views or tables I can use to provide me with this information. It would be nice if all this info is contained somewhere in the master database instead of having to go to each database individually. I'm using SQL Server 2005.
Thanks!
February 13, 2008 at 1:04 pm
I actually logged in today to ask just about the same question. I would also like to know how this can be done. The sp_helprotect will provide a list of tables to groups (or individuals if applicable).
I believe some combination of sys.database_principals, sys.database_permissions and sys.database_role_members will give the user > Role ID list. However I have not been able to determine how to link the user to the role.
As for me also, any help would be appreciated.
February 20, 2008 at 6:53 pm
Try this:
To get a list of SQL Server logins:
[font="Courier New"]EXEC master..sp_helplogins[/font]
A list of Server role memberships:
[font="Courier New"]EXEC master..sp_helpsrvrolemember[/font]
To get a list of database users, role memberships for all databases:
[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.sys.databases
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 + '].sys.database_principals a
JOIN [' + @name + '].sys.database_role_members b ON b.member_principal_id = a.principal_id
JOIN [' + @name + '].sys.database_principals c ON c.principal_id = b.role_principal_id
WHERE a.name != ''dbo'''
EXECUTE (@sql)
FETCH c1 INTO @name
END
CLOSE c1
DEALLOCATE c1
SELECT @maxlen1 = (MAX(LEN(COALESCE(DBName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen2 = (MAX(LEN(COALESCE(UserName, 'NULL'))) + 2)
FROM #tmpTable
SELECT @maxlen3 = (MAX(LEN(COALESCE(RoleName, 'NULL'))) + 2)
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)[/font]
You can lookup each of the stored procs & views in BOL for more details on each.
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 22, 2008 at 10:59 am
Perfect. Thank you.
February 25, 2008 at 5:48 pm
No problem. That query could be trimmed a bit. I just pulled it out of a documentation script I have that outputs the results wrapped in HTML, so it includes formatting to remove extraneous spaces.
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
May 23, 2008 at 11:30 am
Hi scott,I have some prolem regarding deleting the logins that dont have any acess to the databases.We r having some 600 logins are there,its very difficult to go to each login and delete,Is there any script to delete the logins which doesn't have any database acess??
Thanks in advance.
October 20, 2008 at 9:37 am
Scott,
Do you happen to have a SQL2000 version of this script? I changed sys.databases to dbo.sysdatabases, but still receive errors.
Thanks,
Al
October 20, 2008 at 10:01 am
Alan,
What errors?
swekik, you need to run through the databases and query against sys.logins and use left joins to find those that aren't matched up. How many databases do you have?
I could do
select l.principal_id, d1.name, d2.name
from master.sys.server_principals l
left outer join Adventureworks.sys.database_principals d1
on l.sid = d1.sid
left outer join TestDB.sys.database_principals d2
on l.sid = d2.sid
where d1.name is null
and d2.name is null
and repeat that for each database if I didn't have many. If you had a lot, I might tackle it differently, but this is quick and dirty looking for IDs that aren't in there. I'd also make sure these aren't groups/roles in there since I didnt' check for that. This should get you a good list.
October 20, 2008 at 2:12 pm
Scott,
The errors are:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbMaintenance.sys.database_principals'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbMaintenance.sys.database_role_members'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbMaintenance.sys.database_principals'.
Msg 208, Level 16, State 1, Line 1
This is repeated for each Database.
Thanks,Al
August 30, 2017 at 8:05 pm
Great little script. May I suggest you place a state = 0 at the end of sys.databases, to only retrieve ONLINE databases? Otherwise the entire script fails.
SELECT name FROM master.sys.databases WHERE state = 0 --only ONLINE databases
Cheers
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply