How to List SQL logins and database user mappings

27 January,2015 by Jack Vamvas

The SQL Server system stored procedure sp_msloginmappings returns a recordset of SQL Logins and database user mappings.  The stored procedure iterates through every SQL login and checks for mappings to every database on the SQL instance.

A recordset is returned per login. To make the recordsets more useable and allow filtering for specific groups of SQL logins, it may be necessary to insert every recordset into one table. When all the logins and associated mappings are in one table, it is much easier to manipulate the records .

During a recent SQL Server security cleanup , after a new single sign-on feature was implemented , I had to delete a few hundred logins and the associated database users. The first step was to place all the logins and maapings into one table. I created some WHERE filters , and generated DROP SCHEMA , DROP USER, DROP LOGIN statements.

To clean up properly , you’ll also need to list the orphaned database users i.e a database user without an associated SQL login, and  generate DROP USER statements.

Before you commence changing SQL Server security objects, ensure there’s a rollback plan in place. Including a backup or scripts listing the current SQL Seurity login settings.  The last thing you need is a denial of service!

A script to list SQL Logins and database user mappings

This query example, places the results of sp_msloginmappings into a temp table. The resultset is filtered according to requirements.

 

 

--Step 1 : Create temp tab;le
CREATE TABLE #tempMappings (
    LoginName nvarchar(1000),
    DBname nvarchar(1000),
    Username nvarchar(1000), 
    Alias nvarchar(1000)
)
--Step 2:Insert the sp_msloginmappings into the temp table
INSERT INTO #tempMappings
EXEC master..sp_msloginmappings 

--Step 3 : List the results . Filter as required
SELECT * 
FROM   #tempMappings WHERE username LIKE ‘%Jack%’
ORDER BY DBname, username

--Step 4:  Manage cleanup of temp table
DROP TABLE #tempMappings


Read more on security objects management

 List all NT group logins with Powershell - SQL Server DBA

SQL Server - Powershell and Failed Logon attempts - SQL Server DBA

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to List SQL logins and database user mappings


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer