SQL Server script to delete accounts no longer in Active Directory

Question

We have a SQL Server 2000 that will shortly be migrated to SQL Server 2005. It has years of Windows Authentication accounts created that no longer exist in Active Directory, which prevent the Copy Database Wizard from creating these accounts on the new server.

Is there a script or some automated way of deleting the accounts that no longer exist in our Active Directory?

---

EDIT: Just to be clear, the logins needing to be deleted are on SQL Server 2000, which does not support the DROP LOGIN command.

Separately, manually deleting the logins in SQL Server 2000 would (I think) be done with exec sp_droplogin 'loginname' but on mine, the login name cannot be found, whether I use 'domain\loginname' or 'loginname'

Just to add to the confusion, exec sp_revokelogin 'domain\loginname' does appear to work.

EDIT 2: Finally resolved the issue. Many of the logons that were problematic were programmatically added to the database, and while they worked in the sense that a user could connect, the username vs NT login name had a mismatch of domain-prefixed logons when SQL Server expected no domain, and vice versa.

To resolve this, I modified the sp_droplogin procedure to take out one of the checks that was erroring.

I'm accepting my own answer as it works in SQL Server 2000.

Answer 1

What I ended doing is listing the accounts with:

 exec sp_validatelogins

And running

 exec sp_dropuser loginname
 exec sp_droplogin loginname

on the results.

Answer 2

Per my original comment, it appears the SUSER_SID function just grabs whatever sid was recorded when the login was created, and doesn't actually query Active Directory (makes sense, as that could be expensive -- I even tried restarting the server service).

Here is a C# console application that accomplishes the task, allowing you to audit the logins that will be dropped before they actually get dropped.

This app requires .NET 3.5 or higher to run, and in theory it could be put into a PowerShell script (I'm much more comfortable with direct programming).

To remove any logins of local/machine user accounts from the server, you'll need to run this application on the server machine, and hard-code the ContextType variable (I have it like that for testing on my non-domain-joined home computer). Otherwise, you can run it from any machine in the same domain as the server, which also has access to the server.

I'm going to post this on my blog after externalizing the parameters and cleaning up the code a bit, so when I do that, I'll edit this post. But this will get you started now.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.DirectoryServices.AccountManagement;
using System.Security.Principal;
using System.Text;
namespace ConsoleApplication1
{
 class Program
 {
 static void Main(string[] args)
 {
 string connectionString = @"Data Source=.\SQL2008R2DEV;Initial Catalog=master;Integrated Security=SSPI;";
 ContextType domainContext = Environment.UserDomainName == Environment.MachineName ? ContextType.Machine : ContextType.Domain;
 IList<string> deletedPrincipals;
 using (SqlConnection conn = new SqlConnection(connectionString))
 {
 conn.Open();
 deletedPrincipals = _GetDeletedPrincipalsFromServer(conn, domainContext);
 }
 if (deletedPrincipals.Count > 0)
 {
 Console.WriteLine("Logins that will be dropped:");
 foreach (string loginName in deletedPrincipals)
 Console.WriteLine(loginName);
 Console.WriteLine();
 Console.WriteLine("Press Enter to continue.");
 Console.ReadLine();
 }
 else
 Console.WriteLine("No logins with deleted principals.");
 if (deletedPrincipals.Count > 0)
 {
 using (SqlConnection conn = new SqlConnection(connectionString))
 {
 conn.Open();
 _DropDeletedPrincipalLoginsFromServer(conn, deletedPrincipals);
 }
 Console.WriteLine("Logins dropped successfully.");
 }
 Console.WriteLine();
 Console.WriteLine("Press Enter to continue.");
 Console.ReadLine();
 }
 private static void _DropDeletedPrincipalLoginsFromServer(IDbConnection conn, IList<string> loginNames)
 {
 if (loginNames.Count == 0)
 return;
 StringBuilder sb = new StringBuilder();
 foreach (string loginName in loginNames)
 sb.AppendFormat("DROP LOGIN {0};", loginName); // This was escaped on the way out of SQL Server
 IDbTransaction transaction = conn.BeginTransaction();
 IDbCommand cmd = conn.CreateCommand();
 cmd.Transaction = transaction;
 cmd.CommandText = sb.ToString();
 try
 {
 cmd.ExecuteNonQuery();
 transaction.Commit();
 }
 catch
 {
 try
 {
 transaction.Rollback();
 }
 catch { }
 throw;
 }
 }
 private static IList<string> _GetDeletedPrincipalsFromServer(IDbConnection conn, ContextType domainContext)
 {
 List<string> results = new List<string>();
 IDbCommand cmd = conn.CreateCommand();
 cmd.CommandText = "SELECT sid, QUOTENAME(loginname) AS LoginName FROM sys.syslogins WHERE isntname = 1;";
 IDataReader dr = null;
 try
 {
 dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
 while (dr.Read())
 {
 if (!_PrincipalExistsBySid((byte[])dr["sid"], domainContext))
 results.Add((string)dr["LoginName"]);
 }
 }
 finally
 {
 if ((dr != null) && !dr.IsClosed)
 dr.Close();
 }
 return results;
 }
 private static bool _PrincipalExistsBySid(byte[] principalSid, ContextType domainContext)
 {
 SecurityIdentifier sid = new SecurityIdentifier(principalSid, 0);
 if (sid.IsWellKnown) return true;
 using (PrincipalContext pc = new PrincipalContext(domainContext))
 {
 return AuthenticablePrincipal.FindByIdentity(pc, IdentityType.Sid, sid.Value) != null;
 }
 }
 }
}

• user16766: I was off on other projects and didn't get a chance to try this until now. I think what I'm running into is SQL Server 2005 is installed on a different server than SQL Server 2000, and the sys.syslog and DROP LOGIN functions are not supported by SQL Server 2000 -- the database won't transfer to SQL Server 2005 because of the logon creation failures.
• Jon Seigel: @emgee: Ohhh I totally dropped the ball on that one. Sorry. I hope it's clear where you can insert the command to drop the login for SQL Server 2000. I didn't have an instance to test against when I wrote this.
• user16766: No worries, modifications were easy enough.

Answer 3

You can leverage xp_logininfo for this process. This extended stored procedure can be used to provide information from Active Directory for Windows logins in SQL Server. The procedure returns an error if no login exists, so we can put a TRY/CATCH block around it to provide SQL for logins that are no longer valid when the procedure errors:

declare @user sysname
declare @domain varchar(100)
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
 exec xp_logininfo @user
 end try
 begin catch
 --Error on xproc because login doesn't exist
 print 'drop login '+convert(varchar,@user)
 end catch
 fetch next from recscan into @user
end
close recscan
deallocate recscan

With the way the script works, you'll need to set the @domain variable to whatever the domain your checking is. The cursor query will filter only on Windows logins (not groups) within that domain. You will get query results for all the valid logins, but drop statements will be printed with the messages. I went with the print approach instead of actually executing the SQL so you can review and validate the results before actually dropping the logins.

Note, this script will only create your drop login statments. Users will still need to be removed from the respective databases. The appropriate logic can be added to this script as necessary. Also, this will need to be run in your SQL 2005 environment, as this logic is not supported in SQL 2000.

• Gili: Beware! If the SQL Server service account is a local account, Xp_logininfo will return error 0x5, which means access denied, for a valid domain account. This results in every domain account listed to drop.The sp_validatelogins stored procedure will produce the same results whether the SQL Server service account is a local account or domain account.

Answer 4

You can do a drop and recreate in a transaction like this:

BEGIN TRAN
BEGIN TRY
DROP LOGIN [DOMAIN\testuser]
CREATE LOGIN [DOMAIN\testuser] FROM WINDOWS;
END TRY
BEGIN CATCH
 SELECT ERROR_NUMBER(), ERROR_MESSAGE(), ERROR_LINE();
END CATCH
ROLLBACK 

If the error you get is this: Windows NT user or group 'DOMAIN\testuser' not found. Check the name again. then your windows login does not exist anymore. However, there are a bunch of reasons that the drop itself will fail (e.g. permissions granted by the login). You will need to follow up on those manually.

• Jon Seigel: TRY ... CATCH was introduced in SQL 2005. stackoverflow.com/questions/5552530/sql-server-2000-try-catch
• Sebastian Meine: That is correct. I did not see that restriction. (I guess I started reading in the second line...) You probably still can use this method, just checking @@ERROR instead of using the try catch. However, I don't have a SQL Server installation that old available to test this.