public SqlAssemblyEx( SqlAssembly assembly, string assemblyFileName) { mAssembly = assembly; mAssemblyFileName = assemblyFileName; }
private void AddAssemblyButton_Click(System.Object sender, System.EventArgs e) { Cursor csr = null; Database db; SqlAssembly asm; UserDefinedFunction udf; UserDefinedFunctionParameter parm; ListViewItem AssemblyListViewItem; try { csr = this.Cursor; // Save the old cursor this.Cursor = Cursors.WaitCursor; // Display the waiting cursor // Get selected database db = (Database)DatabasesComboBox.SelectedItem; asm = new SqlAssembly(db, "UtilityConversion"); asm.Owner = "dbo"; asm.AssemblySecurityLevel = AssemblySecurityLevel.Safe; // This allows the assembly to be on a different server from SQL Server // Use string array version which serializes the assembly asm.Create(new String[] { AssemblyFileTextBox.Text }); udf = new UserDefinedFunction(db, "StringToInt32"); udf.TextMode = false; udf.ImplementationType = ImplementationType.SqlClr; udf.AssemblyName = "UtilityConversion"; udf.ClassName = "Microsoft.Samples.SqlServer.Conversions"; udf.MethodName = "StringToInt32"; udf.FunctionType = UserDefinedFunctionType.Scalar; udf.DataType = DataType.Int; parm = new UserDefinedFunctionParameter(udf, "@Input"); udf.Parameters.Add(parm); parm.DataType = DataType.NVarChar(255); udf.Create(); ShowAssemblies(true); // Select the assembly just added AssemblyListViewItem = AssembliesListView.FindItemWithText( asm.Name); AssemblyListViewItem.Selected = true; AssemblyListViewItem.EnsureVisible(); } catch (SmoException ex) { ExceptionMessageBox emb = new ExceptionMessageBox(ex); emb.Show(this); } finally { this.Cursor = csr; // Restore the original cursor } }
public void Assembly_WithPermissionSetCases(string permissionSet, string scriptedPermissionSet) { var assembly = new SqlAssembly(permissionSet, "SchemazenAssembly"); assembly.Files.Add(new KeyValuePair <string, byte[]>("mydll", new byte[0])); var expected = @"CREATE ASSEMBLY [SchemazenAssembly] FROM 0x WITH PERMISSION_SET = " + scriptedPermissionSet; Assert.AreEqual(expected, assembly.ScriptCreate()); }
public void CreateDatabaseFromScriptFile( string dbName, string scriptFilePath, StringCollection assembliesToDeploy) { if (!File.Exists(scriptFilePath)) { throw new UpdateDatabaseException( string.Format( "Cannot create database, database script file {0} does not exist.", scriptFilePath)); } DropDatabase(dbName); using (var cn = new SqlConnection(mConnectionString)) { cn.Open(); var sc = new ServerConnection(cn); var srv = new Server(sc); var db = new Microsoft.SqlServer.Management.Smo.Database(srv, dbName); // // Set trustworthy options, if we have assemblies // db.DatabaseOptions.Trustworthy = true; db.Create(); foreach (string assemblyPath in assembliesToDeploy) { var assembly = new SqlAssembly(db, Path.GetFileNameWithoutExtension(assemblyPath)) { AssemblySecurityLevel = AssemblySecurityLevel.Unrestricted }; assembly.Create(Environment.ExpandEnvironmentVariables(assemblyPath)); } using (var sr = new StreamReader(scriptFilePath)) { var createDatabaseScript = sr.ReadToEnd(); db.ExecuteNonQuery(createDatabaseScript); } } }
/// <summary> /// Collect Script Items /// </summary> /// <param name="items2Drop">Drop items list</param> /// <param name="items2Create">Create items list</param> /// <param name="items2Alter">Akter items list</param> private void CollectScriptItems( ICollection <IDroppable> items2Drop, ICollection <ICreatable> items2Create, ICollection <IAlterable> items2Alter) { var dropItems = new List <MySmoObjectBase>(); var alterColumns = new List <MySmoObjectBase>(); WriteTime("Start collect CLR assemblies"); #region Generate DROP and CREATE script for CLR assemblies Trace.TraceInformation(string.Format("Database={0} Collecting items for CLR assemblies...\r\n", mProductionDatabase.Name)); var createItems = Database.GetCLRAssembliesInfo(mSourceDatabase); var dropAssemblyItems = Database.GetCLRAssembliesInfo(mProductionDatabase); #endregion WriteTime("Start collect tables items"); #region Generate DROP and CREATE script for table items // // Generate DROP script for table related stuff // Trace.TraceInformation(string.Format("Database={0} Collecting items for tables...\r\n", mProductionDatabase.Name)); CollectTableItems(dropItems, createItems, alterColumns); #endregion WriteTime("Start collect UDFs"); #region Generate DROP and CREATE script for user defined function Trace.TraceInformation(string.Format("Database={0} Collecting items for user defined function...\r\n", mProductionDatabase.Name)); DifferentItems( Database.GetUserDefinedFunctionsInfo(mProductionDatabase), Database.GetUserDefinedFunctionsInfo(mSourceDatabase), dropItems, createItems); #endregion WriteTime("Start collect views"); #region Generate DROP and CREATE script for views Trace.TraceInformation(string.Format("Database={0} Collecting items for views...\r\n", mProductionDatabase.Name)); DifferentItems( Database.GetViewsInfo(mProductionDatabase), Database.GetViewsInfo(mSourceDatabase), dropItems, createItems); #endregion WriteTime("Start collect stored procedures"); #region Generate DROP and CREATE script for stored procedures CollectStoreProcedureItems(dropItems, createItems); // Add dropped assemblies to drop list dropItems.AddRange(dropAssemblyItems); #endregion WriteTime("Start create object collection"); #region Create object collections foreach (MySmoObjectBase mySmoObject in dropItems) { items2Drop.Add((IDroppable)mySmoObject.SourceSmoObject); } foreach (MySmoObjectBase mySmoObject in createItems) { if (mySmoObject.SourceSmoObject.GetType() != typeof(SqlAssembly)) { items2Create.Add((ICreatable)mySmoObject.SourceSmoObject); } else { #region Create ICreatable intefrace for SqlAssembly // SqlAssembly not implement ICreatable interfase var assembly = (SqlAssembly)mySmoObject.SourceSmoObject; var newAssembly = new SqlAssembly(mProductionDatabase, assembly.Name) { PublicKey = assembly.PublicKey, AssemblySecurityLevel = assembly.AssemblySecurityLevel }; // // Save assembly to the file, assume single file assembly. // string assemblyFileName = Path.Combine( Configuration.Default.OutputFolder, Path.ChangeExtension(assembly.Name, "dll")); byte[] assemblyBody = assembly.SqlAssemblyFiles[0].GetFileBytes(); using (var file = new FileStream(assemblyFileName, FileMode.CreateNew)) { file.Write(assemblyBody, 0, assemblyBody.Length); } using (var file = new StreamWriter(assemblyFileName, true)) { file.Write("\r\nwith permission_set = unsafe"); } var tempAssembly = new SqlAssemblyEx(newAssembly, assemblyFileName); items2Create.Add(tempAssembly); #endregion } } // Collect alter items for indexes and columns foreach (MySmoObjectBase mySmoObject in alterColumns) { var item = (IAlterable)mySmoObject.MProductionSmoObject; var productionColumn = (Column)mySmoObject.MProductionSmoObject; var sourceColumn = (Column)mySmoObject.SourceSmoObject; if (productionColumn.Nullable != sourceColumn.Nullable) { ((Column)item).Nullable = sourceColumn.Nullable; } if (productionColumn.Identity != sourceColumn.Identity) { ((Column)item).Identity = sourceColumn.Identity; } if (productionColumn.IdentitySeed != sourceColumn.IdentitySeed) { ((Column)item).IdentitySeed = sourceColumn.IdentitySeed; } if (productionColumn.IdentityIncrement != sourceColumn.IdentityIncrement) { ((Column)item).IdentityIncrement = sourceColumn.IdentityIncrement; } if (productionColumn.Collation != sourceColumn.Collation) { ((Column)item).Collation = sourceColumn.Collation; } if (productionColumn.DefaultConstraint != null) { if (sourceColumn.DefaultConstraint == null) { items2Drop.Add(((Column)mySmoObject.MProductionSmoObject).DefaultConstraint); } else { if (productionColumn.DefaultConstraint.Text != sourceColumn.DefaultConstraint.Text) { items2Drop.Add(((Column)mySmoObject.MProductionSmoObject).DefaultConstraint); items2Create.Add(((Column)mySmoObject.SourceSmoObject).DefaultConstraint); } } } else { if (sourceColumn.DefaultConstraint != null) { items2Create.Add(((Column)mySmoObject.SourceSmoObject).DefaultConstraint); } } items2Alter.Add(item); } #endregion }
public void Load() { var cnStrBuilder = new SqlConnectionStringBuilder(Connection); Tables.Clear(); Routines.Clear(); ForeignKeys.Clear(); DataTables.Clear(); using (var cn = new SqlConnection(Connection)) { cn.Open(); using (SqlCommand cm = cn.CreateCommand()) { // query schema for database properties cm.CommandText = @" select [compatibility_level], [collation_name], [is_auto_close_on], [is_auto_shrink_on], [snapshot_isolation_state], [is_read_committed_snapshot_on], [recovery_model_desc], [page_verify_option_desc], [is_auto_create_stats_on], [is_auto_update_stats_on], [is_auto_update_stats_async_on], [is_ansi_null_default_on], [is_ansi_nulls_on], [is_ansi_padding_on], [is_ansi_warnings_on], [is_arithabort_on], [is_concat_null_yields_null_on], [is_numeric_roundabort_on], [is_quoted_identifier_on], [is_recursive_triggers_on], [is_cursor_close_on_commit_on], [is_local_cursor_default], [is_trustworthy_on], [is_db_chaining_on], [is_parameterization_forced], [is_date_correlation_on] from sys.databases where name = @dbname "; cm.Parameters.AddWithValue("@dbname", cnStrBuilder.InitialCatalog); using (IDataReader dr = cm.ExecuteReader()) { if (dr.Read()) { SetPropString("COMPATIBILITY_LEVEL", dr["compatibility_level"]); SetPropString("COLLATE", dr["collation_name"]); SetPropOnOff("AUTO_CLOSE", dr["is_auto_close_on"]); SetPropOnOff("AUTO_SHRINK", dr["is_auto_shrink_on"]); if (dr["snapshot_isolation_state"] != DBNull.Value) { FindProp("ALLOW_SNAPSHOT_ISOLATION").Value = (byte) dr["snapshot_isolation_state"] == 0 || (byte) dr["snapshot_isolation_state"] == 2 ? "OFF" : "ON"; } SetPropOnOff("READ_COMMITTED_SNAPSHOT", dr["is_read_committed_snapshot_on"]); SetPropString("RECOVERY", dr["recovery_model_desc"]); SetPropString("PAGE_VERIFY", dr["page_verify_option_desc"]); SetPropOnOff("AUTO_CREATE_STATISTICS", dr["is_auto_create_stats_on"]); SetPropOnOff("AUTO_UPDATE_STATISTICS", dr["is_auto_update_stats_on"]); SetPropOnOff("AUTO_UPDATE_STATISTICS_ASYNC", dr["is_auto_update_stats_async_on"]); SetPropOnOff("ANSI_NULL_DEFAULT", dr["is_ansi_null_default_on"]); SetPropOnOff("ANSI_NULLS", dr["is_ansi_nulls_on"]); SetPropOnOff("ANSI_PADDING", dr["is_ansi_padding_on"]); SetPropOnOff("ANSI_WARNINGS", dr["is_ansi_warnings_on"]); SetPropOnOff("ARITHABORT", dr["is_arithabort_on"]); SetPropOnOff("CONCAT_NULL_YIELDS_NULL", dr["is_concat_null_yields_null_on"]); SetPropOnOff("NUMERIC_ROUNDABORT", dr["is_numeric_roundabort_on"]); SetPropOnOff("QUOTED_IDENTIFIER", dr["is_quoted_identifier_on"]); SetPropOnOff("RECURSIVE_TRIGGERS", dr["is_recursive_triggers_on"]); SetPropOnOff("CURSOR_CLOSE_ON_COMMIT", dr["is_cursor_close_on_commit_on"]); if (dr["is_local_cursor_default"] != DBNull.Value) { FindProp("CURSOR_DEFAULT").Value = (bool) dr["is_local_cursor_default"] ? "LOCAL" : "GLOBAL"; } SetPropOnOff("TRUSTWORTHY", dr["is_trustworthy_on"]); SetPropOnOff("DB_CHAINING", dr["is_db_chaining_on"]); if (dr["is_parameterization_forced"] != DBNull.Value) { FindProp("PARAMETERIZATION").Value = (bool) dr["is_parameterization_forced"] ? "FORCED" : "SIMPLE"; } SetPropOnOff("DATE_CORRELATION_OPTIMIZATION", dr["is_date_correlation_on"]); } } //get schemas cm.CommandText = @" select s.name as schemaName, p.name as principalName from sys.schemas s inner join sys.database_principals p on s.principal_id = p.principal_id where s.schema_id < 16384 and s.name not in ('dbo','guest','sys','INFORMATION_SCHEMA') order by schema_id "; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { Schemas.Add(new Schema((string) dr["schemaName"], (string) dr["principalName"])); } } //get tables cm.CommandText = @" select TABLE_SCHEMA, TABLE_NAME from INFORMATION_SCHEMA.TABLES where TABLE_TYPE = 'BASE TABLE'"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { Tables.Add(new Table((string) dr["TABLE_SCHEMA"], (string) dr["TABLE_NAME"])); } } //get columns cm.CommandText = @" select t.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.IS_NULLABLE, c.CHARACTER_MAXIMUM_LENGTH, c.NUMERIC_PRECISION, c.NUMERIC_SCALE from INFORMATION_SCHEMA.COLUMNS c inner join INFORMATION_SCHEMA.TABLES t on t.TABLE_NAME = c.TABLE_NAME and t.TABLE_SCHEMA = c.TABLE_SCHEMA and t.TABLE_CATALOG = c.TABLE_CATALOG where t.TABLE_TYPE = 'BASE TABLE' "; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { var c = new Column(); c.Name = (string) dr["COLUMN_NAME"]; c.Type = (string) dr["DATA_TYPE"]; c.IsNullable = (string) dr["IS_NULLABLE"] == "YES"; switch (c.Type) { case "binary": case "char": case "nchar": case "nvarchar": case "varbinary": case "varchar": c.Length = (int) dr["CHARACTER_MAXIMUM_LENGTH"]; break; case "decimal": case "numeric": c.Precision = (byte) dr["NUMERIC_PRECISION"]; c.Scale = (int) dr["NUMERIC_SCALE"]; break; } FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]).Columns.Add(c); } } //get column identities cm.CommandText = @" select s.name as TABLE_SCHEMA, t.name as TABLE_NAME, c.name AS COLUMN_NAME, i.SEED_VALUE, i.INCREMENT_VALUE from sys.tables t inner join sys.columns c on c.object_id = t.object_id inner join sys.identity_columns i on i.object_id = c.object_id and i.column_id = c.column_id inner join sys.schemas s on s.schema_id = t.schema_id "; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { try { Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]); Column c = t.Columns.Find((string) dr["COLUMN_NAME"]); string seed = dr["SEED_VALUE"].ToString(); string increment = dr["INCREMENT_VALUE"].ToString(); c.Identity = new Identity(seed, increment); } catch (Exception ex) { throw new ApplicationException( string.Format("{0}.{1} : {2}", dr["TABLE_SCHEMA"], dr["TABLE_NAME"], ex.Message), ex); } } } //get column defaults cm.CommandText = @" select s.name as TABLE_SCHEMA, t.name as TABLE_NAME, c.name as COLUMN_NAME, d.name as DEFAULT_NAME, d.definition as DEFAULT_VALUE from sys.tables t inner join sys.columns c on c.object_id = t.object_id inner join sys.default_constraints d on c.column_id = d.parent_column_id and d.parent_object_id = c.object_id inner join sys.schemas s on s.schema_id = t.schema_id"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]); t.Columns.Find((string) dr["COLUMN_NAME"]).Default = new Default((string) dr["DEFAULT_NAME"], (string) dr["DEFAULT_VALUE"]); } } //get constraints & indexes cm.CommandText = @" select s.name as schemaName, t.name as tableName, t.baseType, i.name as indexName, c.name as columnName, i.is_primary_key, i.is_unique_constraint, i.is_unique, i.type_desc, isnull(ic.is_included_column, 0) as is_included_column from ( select object_id, name, schema_id, 'T' as baseType from sys.tables union select object_id, name, schema_id, 'V' as baseType from sys.views ) t inner join sys.indexes i on i.object_id = t.object_id inner join sys.index_columns ic on ic.object_id = t.object_id and ic.index_id = i.index_id inner join sys.columns c on c.object_id = t.object_id and c.column_id = ic.column_id inner join sys.schemas s on s.schema_id = t.schema_id order by s.name, t.name, i.name, ic.key_ordinal, ic.index_column_id"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { Table t = (string) dr["baseType"] == "V" ? new Table((string) dr["schemaName"], (string) dr["tableName"]) : FindTable((string) dr["tableName"], (string) dr["schemaName"]); Constraint c = t.FindConstraint((string) dr["indexName"]); if (c == null) { c = new Constraint((string) dr["indexName"], "", ""); t.Constraints.Add(c); c.Table = t; if ((string) dr["baseType"] == "V") ViewIndexes.Add(c); } c.Clustered = (string) dr["type_desc"] == "CLUSTERED"; c.Unique = (bool) dr["is_unique"]; if ((bool) dr["is_included_column"]) { c.IncludedColumns.Add((string) dr["columnName"]); } else { c.Columns.Add((string) dr["columnName"]); } c.Type = "INDEX"; if ((bool) dr["is_primary_key"]) c.Type = "PRIMARY KEY"; if ((bool) dr["is_unique_constraint"]) c.Type = "UNIQUE"; } } //get foreign keys cm.CommandText = @" select TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE = 'FOREIGN KEY'"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { Table t = FindTable((string) dr["TABLE_NAME"], (string) dr["TABLE_SCHEMA"]); var fk = new ForeignKey((string) dr["CONSTRAINT_NAME"]); fk.Table = t; ForeignKeys.Add(fk); } } //get foreign key props cm.CommandText = @" select CONSTRAINT_NAME, UPDATE_RULE, DELETE_RULE, fk.is_disabled from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc inner join sys.foreign_keys fk on rc.CONSTRAINT_NAME = fk.name"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]); fk.OnUpdate = (string) dr["UPDATE_RULE"]; fk.OnDelete = (string) dr["DELETE_RULE"]; fk.Check = !(bool) dr["is_disabled"]; } } //get foreign key columns and ref table cm.CommandText = @" select fk.name as CONSTRAINT_NAME, c1.name as COLUMN_NAME, OBJECT_SCHEMA_NAME(fk.referenced_object_id) as REF_TABLE_SCHEMA, OBJECT_NAME(fk.referenced_object_id) as REF_TABLE_NAME, c2.name as REF_COLUMN_NAME from sys.foreign_keys fk inner join sys.foreign_key_columns fkc on fkc.constraint_object_id = fk.object_id inner join sys.columns c1 on fkc.parent_column_id = c1.column_id and fkc.parent_object_id = c1.object_id inner join sys.columns c2 on fkc.referenced_column_id = c2.column_id and fkc.referenced_object_id = c2.object_id order by fk.name, fkc.constraint_column_id "; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { ForeignKey fk = FindForeignKey((string) dr["CONSTRAINT_NAME"]); if (fk == null) { continue; } fk.Columns.Add((string) dr["COLUMN_NAME"]); fk.RefColumns.Add((string) dr["REF_COLUMN_NAME"]); if (fk.RefTable == null) { fk.RefTable = FindTable((string) dr["REF_TABLE_NAME"], (string) dr["REF_TABLE_SCHEMA"]); } } } //get routines cm.CommandText = @" select s.name as schemaName, o.name as routineName, o.type_desc, m.definition, m.uses_ansi_nulls, m.uses_quoted_identifier, t.name as tableName from sys.sql_modules m inner join sys.objects o on m.object_id = o.object_id inner join sys.schemas s on s.schema_id = o.schema_id left join sys.triggers tr on m.object_id = tr.object_id left join sys.tables t on tr.parent_id = t.object_id"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { if (dr["definition"] is DBNull) { Console.ForegroundColor = ConsoleColor.Magenta; Console.WriteLine("Warning: Unable to get definition for {0} {1}.{2}", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]); Console.ForegroundColor = ConsoleColor.White; } else { if (!((string)dr["definition"]).Contains((string)dr["routineName"])) { Console.ForegroundColor = ConsoleColor.Magenta; Console.WriteLine("Warning: {0} {1}.{2} has been renamed since it's definition.", (string)dr["type_desc"], (string)dr["schemaName"], (string)dr["routineName"]); Console.ForegroundColor = ConsoleColor.White; } var r = new Routine((string)dr["schemaName"], (string)dr["routineName"]); r.Text = (string)dr["definition"]; r.AnsiNull = (bool)dr["uses_ansi_nulls"]; r.QuotedId = (bool)dr["uses_quoted_identifier"]; Routines.Add(r); switch ((string)dr["type_desc"]) { case "SQL_STORED_PROCEDURE": r.RoutineType = Routine.RoutineKind.Procedure; break; case "SQL_TRIGGER": r.RoutineType = Routine.RoutineKind.Trigger; break; case "SQL_SCALAR_FUNCTION": case "SQL_INLINE_TABLE_VALUED_FUNCTION": r.RoutineType = Routine.RoutineKind.Function; break; case "VIEW": r.RoutineType = Routine.RoutineKind.View; break; } } } } // get xml schemas cm.CommandText = @"select s.name as DBSchemaName, x.name as XMLSchemaCollectionName, xml_schema_namespace(s.name, x.name) as definition from sys.xml_schema_collections x inner join sys.schemas s on s.schema_id = x.schema_id where s.name != 'sys'"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { var r = new Routine((string) dr["DBSchemaName"], (string) dr["XMLSchemaCollectionName"]) { Text = string.Format("CREATE XML SCHEMA COLLECTION {0}.{1} AS N'{2}'", dr["DBSchemaName"], dr["XMLSchemaCollectionName"], dr["definition"]), RoutineType = Routine.RoutineKind.XmlSchemaCollection }; Routines.Add(r); } } // get CLR assemblies cm.CommandText = @"select a.name as AssemblyName, a.permission_set_desc, af.name as FileName, af.content from sys.assemblies a inner join sys.assembly_files af on a.assembly_id = af.assembly_id where a.is_user_defined = 1 order by a.name, af.file_id"; SqlAssembly a = null; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { if (a == null || a.Name != (string) dr["AssemblyName"]) a = new SqlAssembly((string) dr["permission_set_desc"], (string) dr["AssemblyName"]); a.Files.Add(new KeyValuePair<string, byte[]>((string) dr["FileName"], (byte[]) dr["content"])); if (!Assemblies.Contains(a)) Assemblies.Add(a); } } // get users that have access to the database cm.CommandText = @"select dp.name as UserName, USER_NAME(drm.role_principal_id) as AssociatedDBRole, default_schema_name from sys.database_principals dp left outer join sys.database_role_members drm on dp.principal_id = drm.member_principal_id where dp.type_desc = 'SQL_USER' and dp.sid not in (0x00, 0x01) --ignore guest and dbo and dp.is_fixed_role = 0 order by dp.name"; SqlUser u = null; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { if (u == null || u.Name != (string) dr["UserName"]) u = new SqlUser((string) dr["UserName"], (string) dr["default_schema_name"]); if (!(dr["AssociatedDBRole"] is DBNull)) u.DatabaseRoles.Add((string) dr["AssociatedDBRole"]); if (!Users.Contains(u)) Users.Add(u); } } // get sql logins cm.CommandText = @"select sp.name, sl.password_hash from sys.server_principals sp inner join sys.sql_logins sl on sp.principal_id = sl.principal_id and sp.type_desc = 'SQL_LOGIN' where sp.name not like '##%##' and sp.name != 'SA' order by sp.name"; using (SqlDataReader dr = cm.ExecuteReader()) { while (dr.Read()) { u = Users.SingleOrDefault(user => user.Name == (string) dr["name"]); if (u != null && !(dr["password_hash"] is DBNull)) u.PasswordHash = (byte[]) dr["password_hash"]; } } } } }