locked
SQL Server 2003/2008 and Active Directory Question RRS feed

  • Question

  • We currently run Active Directory 2003 and many SQL Servers some are 2003 and some are 2008.  We have our SQL Servers in a child domain and the users in the primary domain.  In the past we added our users individually to Each SQL Server using the AD Account. (Currently we are trying to stick with Security Groups)  The problem is when we delete a user from the AD it does not delete from the SQL Server.  This is a problem when we get audited because the auditors think the user still has access to the Database and it also makes it messy.  Currently when people leave we have to go through every SQL Server and run the below Query to delete the "Ghost" accounts.  I would like to know two things.  1.  Does anyone else experience this problem with AD and SQL?  If not is there a fix for this issue?  2.  If there is no fix is there an easier way to run the below query on multiple servers? Currently using SQL Tools 2008

     

    DECLARE    @User    VARCHAR( 500 )
    DECLARE    @User1    VARCHAR( 500 )
    DECLARE    @Cmd1    VARCHAR( 500 ),
            @Cmd2    VARCHAR( 500 )

    SELECT    @User    =    'Domain\username'
    SELECT    @User1    =    'username'
    SELECT    @Cmd1    =    '
    USE [?]
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @User + ''')
    BEGIN
        DROP SCHEMA [' + @User + ']
        PRINT ''*** Found and dropped schema in DB - ? ***''
    END
    IF  EXISTS(SELECT * FROM sys.database_principals WHERE name = N''' + @User + ''')
    BEGIN
        DROP USER [' + @User + ']
        PRINT ''*** Found and dropped user in DB - ? ***''
    END'
    EXEC    SP_MSFOREACHDB    @Command1 = @Cmd1

    SELECT    @Cmd1    =    '
    USE [?]
    IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N''' + @User1 + ''')
    BEGIN
        DROP SCHEMA [' + @User1 + ']
        PRINT ''*** Found and dropped schema in DB - ? ***''
    END
    IF  EXISTS(SELECT * FROM sys.database_principals WHERE name = N''' + @User1 + ''')
    BEGIN
        DROP USER [' + @User1 + ']
        PRINT ''*** Found and dropped user in DB - ? ***''
    END'
    EXEC    SP_MSFOREACHDB    @Command1 = @Cmd1

    USE Master
    IF  EXISTS ( SELECT * FROM sys.server_principals WHERE name = @User )
    BEGIN
        SELECT    @Cmd2    =    'DROP LOGIN [' + @User + ']'
        EXEC( @Cmd2 )
    END

    IF  EXISTS ( SELECT * FROM sys.server_principals WHERE name = @User1 )
    BEGIN
        SELECT    @Cmd2    =    'DROP LOGIN [' + @User1 + ']'
        EXEC( @Cmd2 )
    END


    Marty
    Wednesday, August 3, 2011 2:31 PM

Answers

  • Hi,

    For the SQL clean up, you will have to clean up each database as you listed above.  But you can run "sp_validatelogins" to get the list of logins that have don't exist on AD any more.  From there you can go about cleaning up the logins from respective databases.  Unfortentally there is no way to clean up SQL logins automatically; best way you can make sure no Ghost logins exist is use AD groups for all access.  There by when people leave and their account is deleted, they lose SQL access also.

     

    Cheers!


    Mohit K. Gupta --- MCTS, MCITP, MCC2011 http://sqllearnings.blogspot.com/
    • Proposed as answer by Peja Tao Friday, August 5, 2011 5:12 AM
    • Marked as answer by Peja Tao Friday, August 5, 2011 5:25 AM
    Wednesday, August 3, 2011 7:47 PM