Art of the DBA Rotating Header Image

Finding Orphan Logins

Cleaning up old security info is always something that’s hard to stay on top of.  Employees who leave or change responsibilities can make it difficult to track which logins need to remain in our instances.  The best way to manage this is by using Active Directory groups and managing access from the group level instead of by explicit login.  However, because of the communication and organization that’s required to set up these groups in the first place, it’s not uncommon to find ourselves managing explicit logins in our severs.

The unfortunate side effect is this typically results in orphaned logins within our SQL Servers.  A question on how to manage this came up recently on dba.stackexchange.com and, while I couldn’t find a way to answer the specific question (because it was focused around finding orphaned logins in SQL 2000), I was able to come up with a handy utility script for 2005+ that makes use of xp_logininfo.  It’s not a complex script and provides a list of the actual drop login statements you can execute as part of your cleanup.

declare @user sysname
declare @domain varchar(100)

--store total output
declare @return table
(login_name sysname
,sql_statement varchar(max)
,error_msg varchar(100))

--Store output of xp_logininfo
declare @junk table
(a_name sysname NULL
,a_type char(8) NULL
,priv char(9) NULL
,m_login sysname NULL
,p_path sysname NULL)

--Declare domain you are checking
set @domain = 'FOO'

declare recscan cursor for
select name from sys.server_principals
where type = 'U' and name like @domain+'%'

open recscan
fetch next from recscan into @user

while @@fetch_status = 0
begin
	begin try
		insert into @junk
		exec xp_logininfo @user
	end try
	begin catch
		--Error on sproc because login does not exist
		insert into @return
		values(@user,'drop login '+convert(varchar,@user),ERROR_MESSAGE())
	end catch

	fetch next from recscan into @user
end

close recscan
deallocate recscan

select * from @return

A couple notes on this script:

  • It currently doesn’t handle removing the logins from individual databases.  I have another script I use to do that and will be working on combining the two.
  • I don’t automatically execute the drop login because I’m paranoid.  :)  By providing a list, I can review the logins, along with the error message output, and verify that I want to remove them

Hopefully this little utility script can help you with login management in your environments.

5 Comments

  1. Nic Cain says:

    Nice script Mike. Just one thing to note, you may get incorrect results if the AD account you are attempting to query does not have the read attribute set correctly. This can lead to SQL not being able to query the information, returning a 0x5 error, which would then be trapped and you would flag it as dead.

    1. Mike Fal says:

      Thanks for the heads up on that. That’s why I spit out the SQL and the error message and not drop the login automagically. I had some test cases where a non-related error would flag the login as dead, so the idea is to review the output and make sure the errors were the result of xp_logininfo not able to find the login in AD.

  2. If you’re looking to get a list of windows logins that have been removed from AD but not the SQL Server instance this will serve you better

    IF (OBJECT_ID('tempdb..#invalidlogins') IS NOT NULL)
    BEGIN
    DROP TABLE #invalidlogins
    END

    CREATE TABLE #invalidlogins(
    ACCTSID VARBINARY(85)
    , NTLOGIN SYSNAME
    )

    INSERT INTO #invalidlogins
    EXEC sys.sp_validatelogins

    SELECT NTLOGIN FROM #invalidlogins
    order by 1

    Regards Perry

    1. Mike Fal says:

      Thanks Perry, I was unaware of sp_validtelogins. I’m going to have to investigate this stored procedure.

  3. […] Finding Orphan Logins – Mike Fal (Blog|Twitter) […]

Leave a Reply

Your email address will not be published. Required fields are marked *