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?

Answer 1

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.

Answer 2

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'")