login management

  • paul Arscott

    Mr or Mrs. 500

    Points: 504

    Im using SQLServer 2000 sp3a (old I know) and I'm new to my DBA job. One thing I've noticed it that domain logins accounts still show up as active in enterprise manager even though they are disabled or deleted in Active Directory. Why is that? Is there some way to clean up these accounts?

    None our our DB servers are registered with active directory (server properties 'Active Directory' tab). Does that have anything to do with it?

     

    Paul

  • bledu

    SSChampion

    Points: 12335

    you do a comparison of the windows logins on sql again a txt dump of the logins in AD to see which ones no longer exist


    Everything you can imagine is real.

  • Carl Federl

    One Orange Chip

    Points: 25384

    To determine SQL Server Windows logins that no longer exist in Active Directory ( or on the local server for local accounts), run this SQL:

    select *

    from

    (select syslogins.name as LoginName

    , syslogins.sid as LoginSID

    , suser_sname(syslogins.sid) as ADName

    from syslogins

    where syslogins.isntname = 1

    ) as LoginAD

    -- changed or not found

    where ADName is null

    or ADName LoginName

    SQL = Scarcely Qualifies as a Language

  • paul Arscott

    Mr or Mrs. 500

    Points: 504

     I don't believe its as simple as Carl's SQL above. At least it didn't work in my case. That is, disabling or deleting in AD didn't update the syslogins. I did find the perl script InvalidLogins.pl on this site that may actually checks AD but I haven't tried it yet.

    Paul

  • Carl Federl

    One Orange Chip

    Points: 25384

    "disabling or deleting in AD didn't update the syslogins"

    That is correct as the SQL only reports what login SIDs are no longer in Active Directory and what logins have had name changes in Active Directory. It does not report what AD accounts have been locked.

    The SQL provided does not change anything. You must revoke access manually as you may find that the AD account owns objects such as tables, databases or DTS Packages and you will need to change all of the ownerships before removing the logins.

    MS does not provide a means of renaming a login.

    SQL = Scarcely Qualifies as a Language

  • Frances L

    SSCarpal Tunnel

    Points: 4727

    Carl:

    Where the query will run ? Thx.

    select *

    from

    (select syslogins.name as LoginName

    , syslogins.sid as LoginSID

    , suser_sname(syslogins.sid) as ADName

    from syslogins

    where syslogins.isntname = 1

    ) as LoginAD

    -- changed or not found

    where ADName is null

    or ADName <> LoginName

     

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply