June 15, 2012

xp_logininfo (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

Definition:

sys.xp_logininfo(nvarchar @acctname
, varchar @option)

MetaData:

   
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - xp_logininfo -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --

create procedure sys.xp_logininfo
@acctname sysname = null, -- IN: NT login name
@option varchar(10) = null, -- IN: 'all' | 'members' | null
@privilege varchar(10) = 'Not wanted' OUTPUT -- OUT: 'admin' | 'user' | null
as
set nocount on

declare @ret int

-- VALIDATE PARAMETERS --
if (@acctname is null AND (@option is not null OR (@privilege is null OR @privilege <> 'Not wanted')))
OR ((@option is null OR @option <> 'all') AND (@privilege is null OR @privilege <> 'Not wanted'))
OR (@option is not null and @option not in ('all', 'members'))
begin
raiserror(15600,-1,-1,'sys.xp_logininfo')
return 1
end


-- HANDLE case where NO @acctname GIVEN --
if (@acctname is null)
begin
select 'account name' = loginname,
'type' = convert(varchar(8), case when isntuser = 1 then 'user' else 'group' end),
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = loginname,
'permission path' = convert(sysname, null)
from master..syslogins where isntname = 1 and hasaccess = 1
order by 3, 1
return @@error
end

-- HANDLE 'members' QUERY --
if (@option = 'members')
begin
declare @priv varchar(8)
select @priv = case when sysadmin = 1 then 'admin' else 'user' end
from master..syslogins where isntname = 1 and loginname = @acctname and hasaccess = 1
if @priv is not null
select 'account name' = domain+N'\'+name,
'type' = convert(varchar(8), case when sidtype = 1 then 'user' else 'group' end),
'privilege' = @priv,
'mapped login name' = domain+N'\'+name,
'permission path' = @acctname
from OpenRowset(TABLE NETGROUPGETMEMBERS, @acctname) order by 3, 1
else
select 'account name' = convert(sysname, null),
'type' = convert(varchar(8), null),
'privilege' = @priv,
'mapped login name' = convert(sysname, null),
'permission path' = convert(sysname, null)
where 0=1 -- empty result set
return @@error
end


-- CREATE TEMP TABLE AND POPULATE WITH THE REQUIRED DATA --
CREATE TABLE #nt (name sysname collate catalog_default, sid varbinary(85), sidtype int)
insert #nt select loginname, sid, isntgroup + 1 from master..syslogins
where isntname = 1 and loginname = @acctname
insert #nt select distinct domain+N'\'+name, sid, sidtype
from OpenRowset(TABLE NETUSERGETGROUPS, @acctname)
select @ret = @@error
if @ret <> 0
return @ret
-- IF ANY DENY, THEN NO ACCESS --
if exists (select * from master..syslogins where sid in (select #nt.sid from #nt) and denylogin = 1)
delete #nt


-- HANDLE case where OUTPUT REQUESTED --
if (@privilege is null OR @privilege <> 'Not wanted')
begin
select @privilege = case max(sysadmin)
when 1 then 'admin'
when 0 then 'user'
else NULL end
from master..syslogins where isntname = 1 and hasaccess = 1
AND sid in (select sid from #nt)
return @@error
end


-- GET NT TYPE FOR NEXT OPTIONS --
declare @type varchar(8)
select @type = case when get_sid('\U'+@acctname) is null then 'group' else 'user' end

-- HANDLE 'all' QUERY --
if (@option = 'all')
begin
select 'account name' = @acctname,
'type' = @type,
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = @acctname,
'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
where l.loginname = n.name and hasaccess = 1
order by 3, 5
return @@error
end


-- HANDLE DEFAULT QUERY --
select TOP 1
'account name' = @acctname,
'type' = @type,
'privilege' = convert(varchar(8), case when sysadmin = 1 then 'admin' else 'user' end),
'mapped login name' = @acctname,
'permission path' = case when l.loginname = @acctname then NULL else l.loginname end
from master..syslogins l join #nt n on l.isntname = 1 and l.sid = n.sid
where l.loginname = n.name and hasaccess = 1
order by 3, 5
return @@error

No comments:

Post a Comment

Total Pageviews