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