July 30, 2007 at 9:38 am
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
July 30, 2007 at 10:08 am
July 31, 2007 at 7:05 am
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.nameas LoginName
,syslogins.sidas LoginSID
,suser_sname(syslogins.sid) as ADName
from syslogins
wheresyslogins.isntname = 1
) as LoginAD
-- changed or not found
whereADName is null
orADName LoginName
SQL = Scarcely Qualifies as a Language
July 31, 2007 at 9:41 am
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
July 31, 2007 at 11:00 am
"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
August 1, 2007 at 6:23 am
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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply