Réparer les utilisateurs orphelins avec SQL SMO ?

Question

Existe-t-il un moyen de réparer un utilisateur orphelin dans une base de données SQL 2005/2008 en utilisant SQL SMO ?

Vous pouvez trouver les utilisateurs orphelins relativement facilement en énumérant les utilisateurs et en recherchant une adresse vide. User.Login propriété :

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;
    }

Malheureusement, la solution n'est pas aussi simple que de régler le paramètre User.Login au nom de connexion du serveur correspondant. User.Login a bien un setter, mais je ne connais pas de moyen de le propager vers le serveur. Il semble qu'il ne soit utilisable que lorsque vous créez un nouvel objet User .

J'ai envisagé de supprimer l'utilisateur de la base de données et de lier à nouveau le login du serveur à la base de données, mais cela entraîne des complications supplémentaires. Des complications telles que la réaffectation des schémas par défaut, des rôles, et si l'utilisateur possède un schéma dans la base de données, vous risquez d'avoir encore plus de problèmes à mesure que vous effectuez ces changements en cascade. C'est suffisant pour vous donner envie de mettre le SQL en ligne et d'en finir :

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

Cependant, je préférerais ne pas mettre en ligne un appel à une procédure stockée du système.

Des suggestions ?

Answer 1

Malheureusement, SMO n'est pas beaucoup plus performant que SQL-DMO pour ce qui est de fournir des méthodes qui devrait être disponible. Tu vas devoir utiliser le SQL en ligne :

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

o

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

Answer 2

Depuis T-SQL MODIFIER LE LOGIN ... AVEC LOGIN = ...

LOGIN = nom de login

Reconfigurer un utilisateur pour un autre login en changeant l'identifiant de sécurité (SID) de l'utilisateur l'utilisateur pour qu'il corresponde au SID du login. l'identifiant de connexion.

Je ne l'ai pas encore essayé car je synchroniserais les SID entre les serveurs (et j'utilise rarement les logins SQL ces jours-ci).

Cependant, cela correspond à la Méthode User.Alter .

Donc, ça pourrait marcher...

S'il n'aime pas utiliser le même login, je pense que vous pouvez faire un aller-retour avec un autre login.

Answer 3

Pour moi, cette syntaxe a bien fonctionné

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

Je l'ai trouvé ici : http://dbadiaries.com/using-sp_change_users_login-to-fix-sql-server-orphaned-users#sthash.Q85ewEr9.dpuf