Сценарий SQL Server для удаления учетных записей больше не в Active Directory

У нас есть SQL Server 2000, который вскоре будет перенесен на SQL Server 2005. У него есть годы создания учетных записей Windows Authentication, которые больше не существуют в Active Directory, что мешает мастеру копирования базы данных создавать эти учетные записи на новом сервере.

Есть ли сценарий или какой-либо автоматизированный способ удаления учетных записей, которые больше не существуют в нашей Active Directory?


РЕДАКТИРОВАТЬ: Чтобы быть ясным, логины, которые необходимо удалить, находятся на SQL Server 2000, который не поддерживает команду DROP LOGIN .

В отдельности ручное удаление логинов в SQL Server 2000 было бы (я думаю) выполняться с помощью exec sp_droplogin 'loginname', но по моему, имя входа не может быть найден, пользуюсь ли я 'domain \ loginname' или 'loginname'

Чтобы добавить к путанице, exec sp_revokelogin 'domain\loginname', похоже, работает.

РЕДАКТИРОВАТЬ 2: Наконец, решила проблему. Многие из входных систем, которые были проблематичными, были программно добавлены в базу данных, и, хотя они работали в том смысле, что пользователь мог подключиться, имя пользователя и имя входа в систему NT имели несоответствие входных данных с привилегиями домена, когда SQL Server не ожидал ни одного домена, наоборот.

Чтобы решить эту проблему, я изменил процедуру sp_droplogin, чтобы вынуть одну из проверок, которая была ошибкой.

Я принимаю свой собственный ответ, поскольку он работает в SQL Server 2000.

6 голосов | спросил user16766 5 Jpm1000000pmSat, 05 Jan 2013 18:37:58 +040013 2013, 18:37:58

4 ответа


4

То, что я закончил, - это перечисление учетных записей с помощью:

    exec sp_validatelogins

И запустив

    exec sp_dropuser loginname
    exec sp_droplogin loginname

по результатам.

ответил 2 MaramSat, 02 Mar 2013 04:05:59 +04002013-03-02T04:05:59+04:0004 2013, 04:05:59
4

В моем исходном комментарии функция SUSER_SID просто захватывает все, что было записано, когда был создан логин, и не на самом деле запрашивать Active Directory (имеет смысл, поскольку это может быть дорого - я даже попытался перезапустить службу сервера).

Вот консольное приложение C #, которое выполняет задачу, позволяя вам проверять логины, которые будут удалены, прежде чем они действительно будут удалены.

Для этого приложения требуется .NET 3.5 или выше, и теоретически его можно поместить в сценарий PowerShell (мне гораздо удобнее прямое программирование).

Чтобы удалить любые логины локальных /машинных учетных записей пользователей с сервера, вам нужно запустить это приложение на серверном компьютере и выполнить жесткое кодирование ContextType (у меня это похоже на тестирование на моем домашнем компьютере, не подключенном к домену). В противном случае вы можете запустить его с любого компьютера в том же домене, что и сервер, который также имеет доступ к серверу.

Я опубликую это в своем блоге после экстернализации параметров и очистки кода немного, поэтому, когда я это сделаю, я отредактирую этот пост. Но это поможет вам начать работу.

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;
            }
        }
    }
}
ответил Jon Seigel 5 Jpm1000000pmSat, 05 Jan 2013 20:53:32 +040013 2013, 20:53:32
4

Вы можете использовать xp_logininfo для этого процесса. Эта расширенная хранимая процедура может использоваться для предоставления информации из Active Directory для входа Windows в SQL Server. Процедура возвращает ошибку, если логин не существует, поэтому мы можем поместить вокруг него блок TRY /CATCH, чтобы предоставить SQL для логинов, которые больше недействительны при ошибках процедуры:

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

С помощью скрипта вам нужно будет установить переменную @domain независимо от того, в какой области находится ваша проверка. Запрос курсора будет фильтроваться только на входах Windows (не в группах) внутри этого домена. Вы получите результаты запроса для всех допустимых логинов, но инструкции сброса будут напечатаны с сообщениями. Я пошел с подходом к печати вместо фактического выполнения SQL, чтобы вы могли просматривать и проверять результаты до фактического удаления логинов.

Обратите внимание, что этот скрипт будет создавать ваши учетные записи для удаления. Пользователи по-прежнему должны быть удалены из соответствующих баз данных. Соответствующая логика может быть добавлена ​​к этому скрипту по мере необходимости. Кроме того, это нужно будет запускать в вашей среде SQL 2005, так как эта логика не поддерживается в SQL 2000.

ответил Mike Fal 7 Jam1000000amMon, 07 Jan 2013 03:40:54 +040013 2013, 03:40:54
0

Вы можете сделать снимок и воссоздать транзакцию следующим образом:

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  

Если вы получите ошибку: Windows NT user or group 'DOMAIN\testuser' not found. Check the name again., то ваш вход в Windows больше не существует. Тем не менее, существует множество причин, по которым само падение не будет выполнено (например, разрешения, предоставленные логином). Вам нужно будет следить за ними вручную.

ответил Sebastian Meine 5 Jpm1000000pmSat, 05 Jan 2013 20:54:48 +040013 2013, 20:54:48

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132