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  :    https://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

This page was last updated on May 01, 2006 04:28 PM.