How to List SQL logins and database user mappings

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