A quick thought regarding "orphaned users"

Hi there,

this is jut a quick thought (or a reminder) regarding „orphaned users“: There are several scripts out there on how to find an orphaned user account inside a database (I just stumbled across a script a few hours ago, that’s why I’m writing down these words).

Most times DBAs have to deal with an orphaned user when they restore a database to a different server. What then happens is that the SID of the user account won’t match any longer the one from the „old“ server. Therefore you have to “re-map” the database user to a valid SQL Server login.  For further information take a look at BOL.

But why am I writing stuff you all already know? Well, there are a few scripts out there which do not only find orphaned users for you but also gives you the chance to delete them.  And now my reminder: You have to double check if the database user really isn’t used.

Do you’ve ever heard of a so called „loginless user“?  This is a user which only exists inside a database (e.g. there is no corresponding server account). Maybe this user is used as a replacement for an application role? That means a user connects through a application with his credentials to SQL Server, the app then initiates an „Execute as“ and changes the context to our loginless user. If that’s the case then it wouldn’t be the best idea to drop that database user….

So be aware:  loginless user != orphaned user

Advertisements

Über Dirk Hondong

A MS server and ms sql server admin guy from germany. want to improve my skills a little bit, sharing my daily experience
Dieser Beitrag wurde unter SQL Server, SQL Server Administration abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s