How to list logins mapped to a database in SQL Server

26 May,2022 by Tom Collins

Question: I'd like to create a list of SQL Logins\users mapped to a database.  Do you have a script which will create this list?

Answer: This code uses the undocumented stored procedure sp_msloginmappings.  This script creates a temporary table , inserts the results - filtering on the database name and presents the results in a table. 

 

--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 DBName = 'my_db'
ORDER BY DBname, username

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

 

Read more on security scripts

Find database users mapped to db_owner role 

How to List SQL logins and database user mappings

How to Script database role permissions and securables


Author: Tom Collins (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 logins mapped to a database in SQL Server


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