May 21, 2012

sp_MSloginmappings (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData


sys.sp_MSloginmappings(nvarchar @loginname
, int @flags)



create proc sys.sp_MSloginmappings
@loginname nvarchar(258) = null, @flags int = 0

create table #loginmappings(
LoginName nvarchar(128) NULL,
DBName nvarchar(128) NULL,
UserName nvarchar(128) NULL,
AliasName nvarchar(128) NULL

* @flags bits:
* 0x01 - current db only
* Added @dbname so dbo can see everyone in current database.
* Use hacky 4.21 syntax so it will run there, instead of a case..when.
declare @checkmultilogin int
select @checkmultilogin = 1
if ((@flags & 0x01 <> 0) and user_id() = 1)
select @checkmultilogin = 0

declare @logincount int
select @logincount = 0
if (@loginname is not null)
select @logincount = count(*) from dbo.syslogins where loginname = @loginname

-- Gotta be sa or dbo to see other than just current login. --
declare @numlogins int, @whereloginname nvarchar(258), @name nvarchar(258), @retval int
if (@loginname is null)
select @numlogins = 2
select @numlogins = count(*) from dbo.syslogins where loginname = @loginname

if (@numlogins = 0) begin
RAISERROR (15007, -1, -1, @loginname) -- Login not found --
return 1
if (@checkmultilogin <> 0) begin
-- We do not want to allow everybody to execute this SP --
if (is_member(N'db_ddladmin') <> 1 and is_member(N'db_owner') <> 1 and is_member(N'db_accessadmin') <> 1 and is_member(N'db_securityadmin') <> 1 and (@numlogins > 1 or suser_sid() <> suser_sid(@loginname))) begin
RAISERROR (14301, -1, -1, N'') -- Only sa can see other than the current login --
return 1
if (@loginname is not null)
select @whereloginname = N' and loginname = ' + QUOTENAME(@loginname, '''')
select @whereloginname = N' '

* This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
* If loginname is specified, the results are limited to that login. First load a temp table with all logins that are
* in a db, then add those which aren't mapped to any db.
if (@flags & 0x01 <> 0) begin
INSERT #loginmappings select l.loginname, db_name(),, null from master.dbo.syslogins l, dbo.sysusers u where l.sid = u.sid and l.loginname is not NULL
* We only allow multi-db on a 6.x server because dynamic exec() didn'
t exist before then,
* hence there is no way to loop thru every database. This is caught in SQLDMO so no
* need for error message here; we'll just return no result sets.
end else begin
exec @retval = sys.sp_MSforeachdb
use [?] INSERT #loginmappings select l.loginname, db_name(),, null from master.dbo.syslogins l, dbo.sysusers u where l.sid = u.sid and l.loginname is not NULL'
if (@retval <> 0)
return 1
insert #loginmappings select l.loginname, null, null, null from master.dbo.syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL

* Now bring them out by loginname, each in its own result set.
* If this is for all logins, we'
ll return all logins; if for curdb,
* only those in #loginmappings (i.e. only those mapped in curdb).
exec(N'declare hCForEachLogin cursor global for select loginname from master.dbo.syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
if (@@error = 0)
open hCForEachLogin
if (@@error <> 0)
return @@error
fetch hCForEachLogin into @name
while (@@fetch_status >= 0) begin
-- Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly --
if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName = @name))
select * from #loginmappings where LoginName = @name
fetch hCForEachLogin into @name
close hCForEachLogin
deallocate hCForEachLogin
return @@error

No comments:

Post a Comment

Total Pageviews