SQL Server Security should be in the top of your priority list. In the perfect world, once a user has left the company, you’d receive a notification to remove the login from your SQL server. Unfortunately, that doesn’t happen often enough. Too many stale users can increase your security footprint and make SQL server security harder to manage. Also ideally, you’d be manage your security via groups, instead of users. That way, once an AD account is disabled/dropped, then the group is automatically updated.
I’m big on being proactive and keeping my servers clean and secure, so I wrote the following script. This script builds a list of AD logins, check their status, and optionally drops them. If ActiveDirectory returns any kind of errors, then it drops the login. If you feel like this is a bit too drastic, then you can comment out line 25! Then all the script does is print out the list of candidates. Once you have that list, you can verify the candidates before you actually pull the trigger on them.
USE master
DECLARE @users TABLE ( pk INT IDENTITY(1,1),
username sysname)
INSERT INTO @users ([username])
SELECT name FROM sys.server_principals
WHERE type_desc = 'windows_login'
DECLARE @username sysname
DECLARE @i INT = 1
WHILE @i <= (SELECT MAX(pk) FROM @users)
begin
SELECT @username = username
FROM @users WHERE pk = @i
BEGIN try
EXEC xp_logininfo @username
END TRY
BEGIN CATCH
IF ERROR_NUMBER() >0
PRINT @username
EXEC ('DROP login ['+@username+']')
PRINT ERROR_NUMBER()
PRINT ERROR_MESSAGE()
END CATCH
SELECT @i = @i + 1
end
Another thing to keep in mind is that this script does not remove the users from the databases, so the permissions in the database itself are preserved. This presents a bit of a fork on the road, and the direction you go depends on your security policies (or paranoia)
- If your requirement is to just totally clean up old users, then you should loop through each database and remove the respective users from there as well.
- If there’s a chance the login should not be removed (maybe an old service account?), then preserving the user within the database would preserve the permissions. That way, if you have to add the user back, most of the database-level permissions are still there.
Useful links:
XP_loginInfo documentation: Link