Check out my new blog at https://shibumiware.blogspot.com

Thursday, June 05, 2008

Creating Microsoft SQL Users with Windows Logins Using SQL Management Objects & Directory Services

Here is a little wrapper I put together today to assist in creating creating Microsoft SQL Server database users with Windows logins.

   1: using System;
   2: using System.Collections.Generic;
   3: using System.Diagnostics;
   4: using System.DirectoryServices;
   5: using Microsoft.SqlServer.Management.Smo;
   6: using SqlUser = Microsoft.SqlServer.Management.Smo.User;
   7:  
   8: namespace IIL.TSD.Security
   9: {
  10:     /// <summary>
  11:     /// Provides a simple wrapper around key data and functionality for creating a Windows database user in SQL Server 2005
  12:     /// </summary>
  13:     public class SqlUserHelper
  14:     {
  15:         #region Instance Data
  16:  
  17:         private readonly Server _Server;
  18:         private readonly List<string> _DomainUsers = new List<string>();
  19:  
  20:         #endregion
  21:  
  22:         #region Constructor
  23:  
  24:         /// <summary>
  25:         /// Create a SqlUserHelp object.  
  26:         /// 
  27:         /// Domain information defaults to the executing code's domain via Directory.SearchRoot.Path.
  28:         /// </summary>
  29:         /// <param name="sqlServer"> SQL Server Instance</param>
  30:         public SqlUserHelper(string sqlServer)
  31:         {
  32:             if (string.IsNullOrEmpty(sqlServer))
  33:             {
  34:                 throw new ArgumentException("Invalid SQL Server Name", sqlServer);
  35:             }
  36:  
  37:             _Server = new Server(sqlServer);
  38:  
  39:             int count = _Server.Logins.Count; // Force the load of the logins
  40:  
  41:             //  Load the domain user account information
  42:             DirectorySearcher directorySearcher = new DirectorySearcher();
  43:             DirectoryEntry domainEntry = new DirectoryEntry(directorySearcher.SearchRoot.Path);
  44:  
  45:             directorySearcher.SearchRoot = domainEntry;
  46:             directorySearcher.SearchScope = SearchScope.Subtree;
  47:             directorySearcher.PropertiesToLoad.Add("samaccountname");
  48:             directorySearcher.Filter = "(&(objectClass=user))";
  49:  
  50:             SearchResultCollection searchResults = directorySearcher.FindAll();
  51:  
  52:             // Create a simple list of the research results
  53:             foreach (SearchResult result in searchResults)
  54:             {
  55:                 Debug.Assert(result.Properties["samaccountname"].Count == 1);
  56:  
  57:                 DomainUsers.Add(result.Properties["samaccountname"][0].ToString().ToUpper());
  58:             }
  59:         }
  60:  
  61:         #endregion
  62:  
  63:         #region Public Properties
  64:  
  65:         /// <summary>
  66:         /// Get simple list of current domain users
  67:         /// </summary>
  68:         public List<string> DomainUsers
  69:         {
  70:             get { return _DomainUsers; }
  71:         }
  72:  
  73:         /// <summary>
  74:         /// Get SQL SMO LoginCollection for current SQL Server instance
  75:         /// </summary>
  76:         public LoginCollection SqlLogins
  77:         {
  78:             get { return _Server.Logins; }
  79:         }
  80:  
  81:         #endregion
  82:  
  83:         #region Public Methods
  84:  
  85:         /// <summary>
  86:         /// Create a new database user.  Optionally create new login.  Windows users only.
  87:         /// </summary>
  88:         /// <param name="domainUserName">Windows domain user name</param>
  89:         /// <param name="userName">Database user name</param>
  90:         /// <param name="databaseName">Database name</param>
  91:         /// <param name="autoCreateLogin">Indicates whether or not to create the login if it doesn't already exist.</param>
  92:         /// <returns></returns>
  93:         public SqlUser CreateUser(string domainUserName,
  94:                                   string userName,
  95:                                   string databaseName,
  96:                                   bool autoCreateLogin)
  97:         {
  98:             if (!IsValidDomainUser(domainUserName))
  99:             {
 100:                 throw new ArgumentException("Invalid domain user name.", domainUserName);
 101:             }
 102:  
 103:             if (!_Server.Databases.Contains(databaseName))
 104:             {
 105:                 throw new ArgumentException("Invalid database name.", databaseName);
 106:             }
 107:  
 108:             Database database = _Server.Databases[databaseName];
 109:  
 110:             if (database.Users.Contains(userName))
 111:             {
 112:                 throw new InvalidOperationException("User already exists.");
 113:             }
 114:  
 115:             bool loginCreated = false;
 116:  
 117:             Login login;
 118:  
 119:             try
 120:             {
 121:                 if (_Server.Logins.Contains(domainUserName))
 122:                 {
 123:                     login = _Server.Logins[domainUserName];
 124:                 }
 125:                 else
 126:                 {
 127:                     if (autoCreateLogin)
 128:                     {
 129:                         login = new Login(_Server, domainUserName) 
 130:                                           {
 131:                                               DefaultDatabase = databaseName, 
 132:                                               LoginType = LoginType.WindowsUser
 133:                                           };
 134:  
 135:                         login.Create();
 136:  
 137:                         loginCreated = true;
 138:                     }
 139:                     else
 140:                     {
 141:                         throw new FailedOperationException("Login does not exist and auto-create is set to false");
 142:                     }
 143:                 }
 144:  
 145:                 SqlUser user = new SqlUser {Name = userName, Parent = database, Login = login.Name};
 146:  
 147:                 user.Create();
 148:  
 149:                 return user;
 150:             }
 151:             catch (Exception)
 152:             {
 153:                 if (loginCreated)
 154:                 {
 155:                     Login createdLogin = _Server.Logins[domainUserName];
 156:                     createdLogin.Drop();
 157:                 }
 158:  
 159:                 throw;
 160:             }
 161:         }
 162:  
 163:         /// <summary>
 164:         /// Determine if specified user name is a valid domain user name. NOTE: Will throw exception is malformed.
 165:         /// </summary>
 166:         /// <param name="domainUserName">Domain user name to check.</param>
 167:         /// <returns></returns>
 168:         public bool IsValidDomainUser(string domainUserName)
 169:         {
 170:             int domainNameLen = domainUserName.IndexOf(@"\");
 171:  
 172:             if (domainNameLen != -1)
 173:             {
 174:                 if (domainUserName.Length == domainNameLen)
 175:                 {
 176:                     throw new ArgumentException("Invalid domain user name.", domainUserName);
 177:                 }
 178:  
 179:                 domainUserName = domainUserName.Substring(domainNameLen + 1);
 180:             }
 181:  
 182:             domainUserName = domainUserName.ToUpper();
 183:  
 184:             return DomainUsers.Contains(domainUserName);
 185:         }
 186:  
 187:         /// <summary>
 188:         /// Determines whether specified user is a user of a particular database.
 189:         /// </summary>
 190:         /// <param name="databaseName">Database Name</param>
 191:         /// <param name="userName">User Name</param>
 192:         /// <param name="searchInLogins">True to look at the logins </param>
 193:         /// <returns></returns>
 194:         public bool IsDatabaseUser(string databaseName,
 195:                                    string userName,
 196:                                    bool searchInLogins)
 197:         {
 198:             if (_Server.Databases.Contains(databaseName))
 199:             {
 200:                 Database database = _Server.Databases[databaseName];
 201:  
 202:                 if (searchInLogins)
 203:                 {
 204:                     foreach (SqlUser user in database.Users)
 205:                     {
 206:                         if (user.Login == userName)
 207:                         {
 208:                             return true;
 209:                         }
 210:                     }
 211:                 }
 212:  
 213:                 return database.Users.Contains(userName);
 214:             }
 215:  
 216:             return false;
 217:         }
 218:  
 219:         #endregion
 220:     }
 221: }

No comments :

Disclaimer

Content on this site is provided "AS IS" with no warranties and confers no rights. Additionally, all content on this site is my own personal opinion and does not represent my employer's view in any way.