Script to list out SQL Logins and Database User Mappings


You can use system stored procedure sp_msloginmappings to list out the SQL logins and database user mappings.

Syntax
sp_msloginmappings @Loginname , @Flags

@Loginname – Optional argument, in case if you not specify the Login name procedure will return the result for all the SQL Server logins
@Flags – You can specify value 0 or 1, 0 value will show user mapping to all databases and 1 will show the user mapping to current database only. Default value is 0

use master
go
exec sp_msloginmappings 'sa', 0

Image2

use master
go
exec sp_msloginmappings 'sa', 1

Image1

In case you want to run the sp_msloginmappings across multiple SQL Instance using either Central management server or powershell. You can use the below script.

 create table #loginmappings(  
  LoginName  nvarchar(128) NULL,  
  DBName     nvarchar(128) NULL,  
  UserName   nvarchar(128) NULL,  
  AliasName  nvarchar(128) NULL  
 )  
 
 insert into #loginmappings
 EXEC master..sp_msloginmappings
 
 select * from #loginmappings
 
 drop table #loginmappings

2 thoughts on “Script to list out SQL Logins and Database User Mappings

  1. Pingback: Script to list out SQL Logins and Database User Mappings – SQLDBPool – SQL Server Online Help | DiggerSite

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s