CODEWITHSUNDEEP
sql-serversmo
Yoopergeek
Yoopergeek

Reputation: 5642

Fixing Orphaned Users with SQL SMO?

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?

Upvotes: 8

Views: 2845

Answers (3)

Mirek Michalak
Mirek Michalak

Reputation: 1085

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

Upvotes: 0

C-Pound Guru
C-Pound Guru

Reputation: 16368

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

Upvotes: 5

gbn
gbn

Reputation: 432271

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.

Upvotes: 2

Related Questions