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

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