Orphaned Users

Question

Orphaned Users

2Tall

SSChampion

Points: 14644
More actions
December 19, 2007 at 2:03 am

#121501

Hi once again. I found a nice script on this site for Auditing SQL
When I ran this it returned a number of 'Orphaned' server logins to various databases (see attached file 'AuditLog.txt)).
Can anyone if this will cause problems (currently system is not 'Live').
Can these 'Orphaned' logins be easily removed?
Any advice as always very much appreciated.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 10 posts - 1 through 9 (of 9 total)

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

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Be careful of your terms. Orphaned users are users in a database that have no server login associated with them. They will not typically hrut anything and can be easily removed with:

sp_dropuser

I don't know what an orphaned login would be, but it would probably be bad.

Adam Haines SSC-Insane Points: 23217 More actions · Answer 2

Having orphaned users is not a good thing, but luckily is not that big a deal; unless you are the user trying to access the application or database 😀

SQL has some neat functionality to detect and repair orphaned users. More info can be found at http://msdn2.microsoft.com/en-us/library/ms175475.aspx

All-in-all, orphaned users should be maintaned properly and not doing so could cause users and yourself more headaches down the road.

My blog: http://jahaines.blogspot.com

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks for the response guys. Did you review the attached file?

I will follow the links provided.

Many Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 4

My review of the attached file is that the formattng is awful...

2Tall SSChampion Points: 14644 More actions · Answer 5

You are right, just looked myself! It was not like that when I reviewed it locally.......honest!

I will have another go.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

noeld SSC Guru Points: 96590 More actions · Answer 6

noeld

SSC Guru

Points: 96590

December 20, 2007 at 3:44 pm

#761939

you are missing the Logins..

* Noel

2Tall SSChampion Points: 14644 More actions · Answer 7

Hi.

[Quote]You are missing the Logins[/Quote]

How do I rectify or clear (purge)? Or maybe it is not a problem?

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

Key DBA SSCertifiable Points: 6029 More actions · Answer 8

Phil,

Here are my notes on dealing with (detecting and correcting) Orphaned Users. Everything should work with SQL Server 2000 and SQL Server 2005.

--

Source: http://support.microsoft.com/kb/274188/

PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete

Steps To Resolve Orphaned Users

1. Run the following command for the orphaned user from the preceding step:

Use Northwind

go

sp_change_users_login 'update_one', 'test', 'test'

--

First, make sure that this is the problem. This will lists the orphaned users:

--SQL 2000

--SQL 2005

-- USE

EXEC sp_change_users_login 'Report'

If you already have a login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user'

If you want to create a new login id and password for this user, fix it by doing:

EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'

--

Source: http://ijsid.wordpress.com/2007/01/02/sql-server-2000-way-to-make-active-orphan-users-in-database/

This is more of a note for my server administrators. Every time they restore

databases for past projects they recieve error on unable to connect to login

id associated with database. Now when they click on users in database they

can see them but cant access them.

The problem is associated with orphan users hence simple problem calls

for simple solution:

Step 1 : Login using Super Admin into Query Analyzer

Step 2: select database which has been restored

Step 3: Run following command "sp_revokedbaccess 'username' "

Step 4: Run following command "sp_droplogin "

The user will be deleted and then you can create a new account and link

it back to database.

--

How to resolve permission issues when you move a database between servers that are running SQL Server.

http://support.microsoft.com/kb/240872/EN-US/

REFERENCES

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

274188 (http://support.microsoft.com/kb/274188/) "Troubleshooting orphaned users" topic in Books Online is incomplete

246133 (http://support.microsoft.com/kb/246133/) How to transfer logins and passwords between instances of SQL Server

168001 (http://support.microsoft.com/kb/168001/) User logon and/or permission errors after restoring dump

298897 (http://support.microsoft.com/kb/298897/) SAMPLE: Mapsids.exe helps map SIDs between user and master databases when database is moved

"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP

2Tall SSChampion Points: 14644 More actions · Answer 9

Thanks Damon.

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper