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.