Re: Keeping Domain & SQL Access In Sync

From: Mike Epprecht \(SQL MVP\) (mike_at_epprecht.net)
Date: 07/19/05

  • Next message: kai: "How to create SQL Login audit log?" 
    Date: Tue, 19 Jul 2005 23:53:23 +0200

    Hi

    Then you will need to write some code for this.

    Call sp_validatelogins, this will give you a list of all NT Logins that are
    no longer valid.
    Then based on that result, call sp_revokelogin to remove the user from the
    server.

    Regards
    --------------------------------
    Mike Epprecht, Microsoft SQL Server MVP
    Zurich, Switzerland

    IM: mike@epprecht.net

    MVP Program: http://www.microsoft.com/mvp

    Blog: http://www.msmvps.com/epprecht/

    "Colette" <Colette@discussions.microsoft.com> wrote in message
    news:4AE2F5A1-DA00-4591-BC54-76DEC19D08CA@microsoft.com...
    > That's what I've suggested but they do not want to add additional groups
    > to
    > Active Directory. Strange....but true. I have instructed them we need to
    > go
    > this route or they need to manually notify the SQL Admins based off the
    > "security form" of a termination involving SQL access.
    >
    > P.S. The servers were already set up this way prior to my hire. I'm
    > trying
    > to fix it. Just wanted to throw that out there...
    >
    > Thanks again.
    >
    > Colette
    >
    > "Mike Epprecht (SQL MVP)" wrote:
    >
    >> Hi
    >>
    >> Why don't you rather use Domain Groups?
    >> Give a domain group the correct access, and add the user to the group. A
    >> user can be in multiple groups and when the NT accounts gets added or
    >> removed, there is no maintenance from the DBA side.
    >>
    >> Regards
    >> --------------------------------
    >> Mike Epprecht, Microsoft SQL Server MVP
    >> Zurich, Switzerland
    >>
    >> MVP Program: http://www.microsoft.com/mvp
    >>
    >> Blog: http://www.msmvps.com/epprecht/
    >>
    >>
    >>
    >> "Colette" wrote:
    >>
    >> > Does anyone know of a sample script I could get access to that would
    >> > automatically compare disabled/deleted domain accounts to the SQL
    >> > Security
    >> > Logins and keep them "in sync" without any interaction from the SQL
    >> > administrator? We're wanting to eliminate the need of sending manual
    >> > notifications to the SQL administrator of terminated employees and
    >> > running
    >> > the sp_denylogin. Instead, we want to just create a script that would
    >> > go out
    >> > to all of our SQL servers and automatically remove accounts no longer
    >> > active
    >> > in the domain itself. Does Active Directory provide any tools to do
    >> > this?
    >> >
    >> > Any help would be greatly appreciated.
    >> >
    >> > Thanks.
    >> >
    >> > Colette

  • Next message: kai: "How to create SQL Login audit log?"

    Relevant Pages