Orphan logins? - SQL Server Forums

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)

Orphan logins?

Author

Topic

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 14:12:55

Like orphan users, is there a way to figure out orphan logins?

------------------------
I think, therefore I am - Rene Descartes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:26:56

Orphaning is between users and logins, so I don't understand your question.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 14:29:48

List of logins who doesn't have a foot print in any database.

------------------------
I think, therefore I am - Rene Descartes

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:33:29

You would have to compare sys.logins and sys.users in each of the databases. Use a LEFT OUTER JOIN and then in the WHERE clause use sys.users.WhateverColumnItIs IS NULL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 14:44:18

..that will not take care of the logins, members of ONLY server roles.

------------------------
I think, therefore I am - Rene Descartes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 14:46:32

How did you get Orphan logins? Did you move database from different server? What did you do? Explain clearly.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-04-10 : 14:55:36

quote:
Originally posted by ravilobo

..that will not take care of the logins, members of ONLY server roles.

------------------------
I think, therefore I am - Rene Descartes

It will show you each login that does not have access to any database.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 15:01:19

quote:
Originally posted by sodeep

How did you get Orphan logins? Did you move database from different server? What did you do? Explain clearly.

1. moved few not all, Dbs across servers
2. used sp_help_revlogin to t/f logins
3. now I have unwanted logins on the target server.

Hope this explians....

------------------------
I think, therefore I am - Rene Descartes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 15:07:01

Because database associated with that logins doesn't exist in target server. Thats why when i run Sp_revolve_login script, i filter out what i need in target server.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 15:11:25

quote:
Originally posted by sodeep
Thats why when i run Sp_revolve_login script, i filter out.....

what is - Sp_revolve_login script?

------------------------
I think, therefore I am - Rene Descartes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 15:55:06

That is SP of microsoft to sync logins and role while migrating server.

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-10 : 16:02:41

Sodeep, are you confusing Sp_revolve_login with sp_help_revlogin or am I missing something?

------------------------
I think, therefore I am - Rene Descartes

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-04-10 : 17:49:10

Yah! that one .I always get confused.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-04-10 : 22:47:30

Try sp_helplogins, find out which login doesn't have db access.

djdanrt
Starting Member

15 Posts

Posted - 2008-04-11 : 08:17:43

quote:
Originally posted by ravilobo

Like orphan users, is there a way to figure out orphan logins?

------------------------
I think, therefore I am - Rene Descartes

You try this?
sp_change_users_login 'Report'

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-11 : 09:06:12

quote:
Originally posted by djdanrt
You try this?
sp_change_users_login 'Report'

Yes. Where as my requirement is slightly different. I need orphan logins not orphan users!

------------------------
I think, therefore I am - Rene Descartes

ravilobo
Master Smack Fu Yak Hacker

1184 Posts

Posted - 2008-04-11 : 09:07:54

quote:
Originally posted by rmiao

Try sp_helplogins, find out which login doesn't have db access.

Thanks rmiao! Great approach!!
It is not very direct, but I guess i can figure out from there. Thanks again.

------------------------
I think, therefore I am - Rene Descartes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources