Fixing Orphaned Users with SQL SMO?

Question

Is there a way to fix an orphaned user in a SQL 2005/2008 database using SQL SMO?

You can find orphaned users relatively easily by enumerating through the users and looking for an empty User.Login property:

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;    
public static IList<string> GetOrphanedUsers(Server smoServer, string database) {
       Database db = smoServer.Databases[database];

       List<string> orphanedUsers = new List<string>();
       foreach (User user in db.Users) {
          if (!user.IsSystemObject && user.Login == string.Empty) {
             orphanedUsers.Add(user.Name);
          }
       }

       return orphanedUsers;
    }

Unfortunately, the fix isn't as simple as setting the User.Login property to the matching server login name. User.Login does have a setter, but I'm not aware of a way to propogate that back to the server. It appears only usable when you're creating a new User.

I considered dropping the user from the database and re-binding the server login to the database, but with that comes extra complications. Complications like re-assigning default schemas, roles, and if they own a schema in the database you're in for more trouble as you cascade through those changes. It's enough to make you want to inline the SQL and be done with it:

ServerConnection server = new ServerConnection("MyBox\SQLInstance");
Database db = server.Databases["MyDatabase"];
db.ExecuteNonQuery("sp_change_users_login 'auto_fix', 'ORPHANED_USERNAME'")

However, I'd prefer to not inline a call to a system stored procedure.

Any suggestions?

Blader

Answer 1

Unfortunately SMO isn't much better than SQL-DMO for providing methods that should be available. You're gonna have to use in-line SQL:

db.ExecuteNonQuery("sp_change_users_login 'auto_fix', 'ORPHANED_USERNAME'")

or

db.ExecuteNonQuery("sp_change_users_login 'update_one', 'ORPHANED_USERNAME', 'ORPHANED_USERNAME'")

Annihilation

This is pretty much what I was concluding from gbn's answer, and the comments thus far. This feels like an answer-steal, but, as far as I can tell, it is the right answer. – Blader

Just a heads up, the MSDN documentation, msdn.microsoft.com/en-us/library/ms174378.aspx, states that this method will be removed in future versions of SQL server and to user the Alter User, which as Yoopergeek stated below does not work with SMO. – Plosion

Answer 2

From T-SQL ALTER LOGIN ... WITH LOGIN = ...

LOGIN = login_name

Re-maps a user to another login by changing the user's Security Identifier (SID) to match the login's SID.

Now, I haven't tried it because I would synch SIDs across servers (and rarely use SQL logins these days)

However, this maps to the User.Alter Method.

So, it might work...

If it doesn't like using the same login, I reckon you could map to another login and back.

Tillietillinger

Excellent suggestion. Not sure how I missed the User.Alter() method. However, it looks like setting the User.Login, and then calling User.Alter() is not allowed. It throws a FailedOperationException stating: "Alter failed for User 'MyOrphanedUser'." The FailedOperationException has an inner exception of type SmoException that states: "Modifying the Login property of the User object is not allowed. You must drop and recreate the object with the desired property." Perhaps it's simply not possible to de-orphan through the SMO API? – Blader

just do Smo.Database.ExecuteNonQuery("Alter User FOO With Login FooLogin"). – Ftlb

That's what I was originally hoping to avoid - in-lining SQL to the database. But, that's the route I've ended up taking. However, I'm using the 'sp_change_users_login' system stored procedure I mentioned my question. – Blader

Answer 3

For me this syntax worked fine

    db.ExecuteNonQuery("sp_change_users_login 'Auto_Fix', 'login_from_Report', NULL, 'p@ssword123!'")

I found it here: http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users#sthash.Q85ewEr9.dpuf

Butterball