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;
            }
        }
    }
}

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.

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.