Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.xp_logininfo

  No additional text.


Syntax

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

 
Last revision 2008RTM
See also

  sp_MSpublication_access (Procedure)
sp_sqlagent_has_server_access (Procedure)
sp_validate_user (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash