use master
go
if object_id('sp_logininfo') is not null
	drop procedure sp_logininfo
go
create procedure sp_logininfo
(
@loginmode varchar(30) = NULL OUTPUT,
@integrated varchar(30) = NULL OUTPUT,
@showdetails bit = 0
)
as
/********************************************************************************/
/*	Created By :	Umachandar Jayachandran	(UC)				*/
/*	Created On :	20 October 1996						*/
/*	Description:	This stored procedure can be used to obtain information	*/
/*			about a SQL Server login. If executed with no parameters*/
/*			the stored procedure will report if the current user is */
/*			connected through standard or integrated/mixed type     */
/*			login. The output can also be obtained through variables*/
/*			to make it easy to use from other SPs. The showdetails  */
/*			flag will display the current NT logins and groups which*/
/*			have been granted access to SQL Server.			*/
/********************************************************************************/
/*	Resources  :	http://www.umachandar.com/resources.htm         	*/
/********************************************************************************/
set nocount on
set ansi_defaults off
declare @account varchar(30), @nt_domain varchar(30), @nt_username varchar(30),
	@privilege varchar(30), @showresults bit

if @loginmode IS NULL or @integrated IS NULL
	select @showresults = 1

create table #loginconfig (name varchar(30), config_value varchar(30) null)
insert #loginconfig exec master..xp_loginconfig

select @loginmode = config_value from #loginconfig where name = 'login mode'
if @loginmode = 'standard'
begin
	select @integrated = 'No'
	goto SHOW_RESULTS_LABEL
end
create table #logininfo (account varchar(60), type varchar(30),
	privilege varchar(30) null, mapped_login varchar(60) null,
	permission_path varchar(255) null)
insert #logininfo exec master..xp_logininfo 
declare accounts insensitive cursor for
 select account from #logininfo where charindex('SYSTEM', account) = 0
open accounts
while('FETCH IS OK' = 'FETCH IS OK')
begin
	fetch next from accounts into @account
	if @@fetch_status < 0 break
	insert #logininfo exec master..xp_logininfo @account, 'members'
end
deallocate accounts

select @nt_domain = nt_domain, @nt_username = nt_username from sysprocesses 
 where spid = @@spid

-- First check for sa
if suser_id() = 1
begin
	-- Next check for NT user privilege
	if exists( select privilege from #logininfo
		    where account = @nt_domain + '\' + @nt_username
			and privilege = 'admin')
		select @integrated = 'Yes'
	else
		select @integrated = 'No'
	goto SHOW_RESULTS_LABEL
end

-- Second check for users
select @integrated = case when @nt_username = suser_name() then 'Yes' else 'No' end

SHOW_RESULTS_LABEL:
if @showresults = 1
begin
	select @loginmode as "Server Login Mode",
		@integrated as "Integrated"
end
if @showdetails = 1
begin
	print ''
	print 'Server Security Configuration'
	select * from #loginconfig
	print ''
	print 'Server Security Details'
	select * from #logininfo
end
go
grant execute on sp_logininfo to public
go

-- Usage Examples:
print 'With no parameters...'
exec sp_logininfo
print ''
go
print 'With show details option...'
exec sp_logininfo @showdetails = 1
print ''
go
print 'With output parameters...'
declare @loginmode varchar(30), @integrated varchar(30)
select @loginmode = '', @integrated = ''
exec sp_logininfo @loginmode out, @integrated out
select @loginmode as LoginMode, @integrated as Integrated