public static void DropDatabaseUser(DatabaseSetupInfo setupInfo) { Action <SqlConnection> dropLoginMethod = (connection) => { using (SqlCommand dropLoginCommand = new SqlCommand("sp_droplogin", connection)) { dropLoginCommand.CommandType = CommandType.StoredProcedure; dropLoginCommand.Parameters.Add("@loginame", SqlDbType.VarChar).Value = setupInfo.EffectiveApplicationAuthenticationInfo.UserName; dropLoginCommand.ExecuteNonQuery(); } }; using (SqlConnection adminConnection = new SqlConnection(setupInfo.CreateConnectionString(true, false))) { adminConnection.Open(); if (!setupInfo.ApplicationAuthenticationSameAsAdmin && !setupInfo.ApplicationAuthenticationInfo.IntegratedSecurity) { if (!setupInfo.IsServerRoleMember("sysadmin", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnection)) { using (SqlCommand selectUsersCommand = new SqlCommand("exec sp_MSloginmappings @User", adminConnection)) { selectUsersCommand.Parameters.Add("@User", SqlDbType.VarChar).Value = setupInfo.ApplicationAuthenticationInfo.UserName; selectUsersCommand.CommandTimeout *= 4; SqlDataAdapter adpt = new SqlDataAdapter(selectUsersCommand); DataTable usersTable = new DataTable(); adpt.Fill(usersTable); if (usersTable.Rows.Count == 0) { dropLoginMethod(adminConnection); } else if (usersTable.Rows.Count == 1 && usersTable.Rows[0]["DBName"].ToString().Equals(setupInfo.DatabaseName, StringComparison.InvariantCultureIgnoreCase)) { using (var adminConnectionWithDB = new SqlConnection(setupInfo.CreateConnectionString(true, true))) { adminConnectionWithDB.Open(); if (!setupInfo.IsDatabaseRoleMember("db_owner", setupInfo.ApplicationAuthenticationInfo.UserName, adminConnectionWithDB)) { using (var dropUserCommand = new SqlCommand(string.Format("DROP USER [{0}]", setupInfo.ApplicationAuthenticationInfo.UserName), adminConnectionWithDB)) { dropUserCommand.ExecuteNonQuery(); } dropLoginMethod(adminConnection); } } } } } } } }
/// <summary> /// Creates a new user in the database if needed /// </summary> public static void CreateDatabaseUser(DatabaseSetupInfo databaseSetupInfo, string[] neededTables) { if (!databaseSetupInfo.ApplicationAuthenticationSameAsAdmin) { string userName; Func <SqlConnection, SqlCommand> createLoginMethod; if (databaseSetupInfo.EffectiveApplicationAuthenticationInfo.IntegratedSecurity) { userName = @"NT AUTHORITY\NETWORK SERVICE"; createLoginMethod = (connection) => new SqlCommand(string.Format("CREATE LOGIN [{0}] FROM WINDOWS", userName), connection); } else { userName = databaseSetupInfo.EffectiveApplicationAuthenticationInfo.UserName; createLoginMethod = (connection) => new SqlCommand( string.Format( "CREATE LOGIN [{0}] WITH PASSWORD='******', DEFAULT_DATABASE=[{2}], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF", userName, databaseSetupInfo.EffectiveApplicationAuthenticationInfo.Password, databaseSetupInfo.DatabaseName ), connection ); } using (SqlConnection adminConnection = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, false))) { adminConnection.Open(); using (SqlConnection adminConnectionWithDb = new SqlConnection(databaseSetupInfo.CreateConnectionString(true, true))) { adminConnectionWithDb.Open(); SqlCommand selectUserCommand = new SqlCommand("SELECT COUNT(*) FROM sys.database_principals AS db INNER JOIN sys.server_principals AS S ON db.sid = s.sid WHERE db.name=@UserName OR S.name=@UserName", adminConnectionWithDb); selectUserCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName; bool userExists = (int)selectUserCommand.ExecuteScalar() > 0; if (!userExists) { using (SqlCommand loginExistsCommand = new SqlCommand("SELECT COUNT(*) FROM sys.server_principals WHERE NAME=@UserName", adminConnection)) { loginExistsCommand.Parameters.Add("@UserName", System.Data.SqlDbType.VarChar).Value = userName; var loginExists = (int)loginExistsCommand.ExecuteScalar() > 0; if (!loginExists) { SqlCommand createLoginCommand = createLoginMethod(adminConnection); createLoginCommand.ExecuteNonQuery(); } SqlCommand createUserCommand = new SqlCommand(string.Format("CREATE USER [{0}] FOR LOGIN [{0}]", userName), adminConnectionWithDb); createUserCommand.ExecuteNonQuery(); } } using (SqlCommand permissionsCommand = new SqlCommand("", adminConnectionWithDb)) { if (neededTables == null || neededTables.Length == 0) { if (!databaseSetupInfo.IsDatabaseRoleMember("db_owner", userName, adminConnectionWithDb)) { permissionsCommand.CommandText = "sp_addrolemember"; permissionsCommand.CommandType = System.Data.CommandType.StoredProcedure; permissionsCommand.Parameters.Add("@rolename", System.Data.SqlDbType.VarChar).Value = "db_owner"; permissionsCommand.Parameters.Add("@membername", System.Data.SqlDbType.VarChar).Value = userName; permissionsCommand.ExecuteNonQuery(); } } else { permissionsCommand.CommandType = System.Data.CommandType.Text; foreach (string tableName in neededTables) { permissionsCommand.CommandText = string.Format("GRANT DELETE,INSERT,REFERENCES,SELECT,UPDATE,VIEW DEFINITION ON [dbo].[{0}] TO [{1}]", tableName, userName); permissionsCommand.ExecuteNonQuery(); } } } } } } }