sp_validatelogins.This is just crazy !!!

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

sp_validatelogins.This is just crazy !!!

Author

Topic

blackclouds999
Starting Member

2 Posts

Posted - 2010-11-08 : 17:13:38

Hi Dear Friends,

Can someone plz help me out with this !

I am under the assignment to pull out all the orphan logins.For this I have looped through all the servers in all of our environment and executed "exec sp_validatelogins".I got a resultset.Thought the next thing to do was to delete them right away.To my astonishment I found many logins that are very much present in windows AD.

I did a web lookup and I found 2 possible reasons.
1) collation mismatch
2) AD level login drop and recreate

I tried looking for these in my environment but in vain.My collation is not CS.It is CI.

But in syslogins table I find few logins as "XXX\dsdsds" and few others as "xxx\dsdsds".

I have just spent hours on it but not able to sketch where the problem is ?

Also I have thought of an other alternate to sp_validatelogins .Here is the code.Any help would be greatly appreciated.

select
sid,
name as "SERVER LOGIN NAME",
loginname as "WINDOWS ACCOUNT NAME",
substring(loginname,charindex('\',loginname,0)+1,len(loginname)-charindex('\',loginname,0+1))
from
master..syslogins
where
isntuser=1 and suser_sid(substring(loginname,charindex('\',loginname,0)+1,len(loginname)-charindex('\',loginname,0+1))) is null
and
name like 'TT%'

here TT-> Domain Name

Thanks Friends in advance !!!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2010-11-12 : 17:10:33

have you tried
select * from sys.server_principals?

--------------------
keeping it simple...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources