Tuesday, September 24, 2013

List all mapping users of SQL login account

Find a useful system store procedure to list all mapping user of SQL login account.

sp_msloginmappings @Loginname , @Flags

@loginname: the login account name, If loginname is not specified, results are returned for the login accounts
@Flags: value can be 0 and 1, by default 0. 0 means show mapping user in all databases. 1 indicates how mapping user in current database context.

here is sample:
sp_msloginmappings 'test'
show mapping user account info in all databases for login account 'test'

sp_msloginmappings 'test', 1
show mapping user account info in current databases context for login account 'test'

BTW, SQLPS has same function:
(dir sqlserver:\sql\servername\instancename\logins) | foreach { $_.EnumDatabaseMappings()}

1 comment: