public static List <DatabasePrincipal> GetSnapshotDbRoleMembers( int snapshotid, int dbid, int roleid ) { Debug.Assert(snapshotid != 0); // Init return. List <DatabasePrincipal> members = new List <DatabasePrincipal>(); // Open connection to repository. using (SqlConnection connection = new SqlConnection(Program.gController.Repository.ConnectionString)) { // Open the connection. connection.Open(); // Query the repository for server roles. SqlParameter paramSnapshotid = new SqlParameter(ParamSnapshotid, snapshotid); SqlParameter paramDbid = new SqlParameter(ParamDbid, dbid); SqlParameter paramRoleid = new SqlParameter(ParamRoleid, roleid); using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text, QueryGetSnapshotDbRoleMembers, new SqlParameter[] { paramSnapshotid, paramDbid, paramRoleid })) { while (rdr.Read()) { // Read the fields. SqlInt32 id = rdr.GetSqlInt32((int)RoleMemberColumns.RoleMemberUid); // Get user/role associated with id. if (!id.IsNull) { DatabasePrincipal dbp = DatabasePrincipal.GetSnapshotUser(snapshotid, dbid, id.Value); if (dbp == null) { dbp = DatabasePrincipal.GetSnapshotDbRole(snapshotid, dbid, id.Value); } if (dbp != null) { members.Add(dbp); } } } } } return(members); }
public static DatabasePrincipal GetSnapshotUser( int snapshotid, int dbid, int uid ) { Debug.Assert(snapshotid != 0); // Init return. DatabasePrincipal user = null; // Open connection to repository and get users. using (SqlConnection connection = new SqlConnection(Program.gController.Repository.ConnectionString)) { // Open the connection. connection.Open(); // Query the repository for db principals. SqlParameter paramSnapshotid = new SqlParameter(ParamSnapshotid, snapshotid); SqlParameter paramDbid = new SqlParameter(ParamDbid, dbid); SqlParameter paramUid = new SqlParameter(ParamUid, uid); using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text, QueryGetSnapshotUser, new SqlParameter[] { paramSnapshotid, paramDbid, paramUid })) { if (rdr.Read()) { // Read the fields. SqlInt32 id = rdr.GetSqlInt32((int)UserColumns.Uid); SqlString name = rdr.GetSqlString((int)UserColumns.Name); SqlString type = rdr.GetSqlString((int)UserColumns.Type); SqlString owner = rdr.GetSqlString((int)UserColumns.Owner); SqlString login = rdr.GetSqlString((int)UserColumns.Login); SqlString isalias = rdr.GetSqlString((int)UserColumns.IsAlias); SqlString altname = rdr.GetSqlString((int)UserColumns.AltName); SqlString hasaccess = rdr.GetSqlString((int)UserColumns.HasAccess); SqlString defaultschemaname = rdr.GetSqlString((int)UserColumns.DefaultSchemaName); SqlBoolean isContainedUser = rdr.GetSqlBoolean((int)UserColumns.IsContainedUser); SqlString AuthenticationType = rdr.GetSqlString((int)UserColumns.AuthenticationType); // Create the user. user = new DatabasePrincipal(id, name, type, owner, login, isalias, altname, hasaccess, defaultschemaname, isContainedUser, AuthenticationType); } } } return(user); }
public static DatabasePrincipal GetSnapshotDbRole( int snapshotid, int dbid, string rolename, bool casesensitive ) { Debug.Assert(snapshotid != 0); // Init return. DatabasePrincipal role = null; // Open connection to repository and get roles. using (SqlConnection connection = new SqlConnection(Program.gController.Repository.ConnectionString)) { // Open the connection. connection.Open(); // Query the repository for db roles. SqlParameter paramSnapshotid = new SqlParameter(ParamSnapshotid, snapshotid); SqlParameter paramDbid = new SqlParameter(ParamDbid, dbid); SqlParameter paramName = new SqlParameter(ParamName, rolename); using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text, casesensitive ? QueryGetSnapshotDbRoleByNameCaseSensitive : QueryGetSnapshotDbRoleByName, new SqlParameter[] { paramSnapshotid, paramDbid, paramName })) { while (rdr.Read()) { // Read the fields. SqlInt32 id = rdr.GetSqlInt32((int)RoleColumns.Uid); SqlString name = rdr.GetSqlString((int)RoleColumns.Name); SqlString type = rdr.GetSqlString((int)RoleColumns.Type); SqlString owner = rdr.GetSqlString((int)RoleColumns.Owner); if (!casesensitive || (casesensitive && name == rolename)) { // Create the role and add to list. role = new DatabasePrincipal(id, name, type, owner); } } } } return(role); }
public static List <DatabasePrincipal> GetSnapshotUserRoles( int snapshotid, int dbid, int uid ) { Debug.Assert(snapshotid != 0); // Init return. List <DatabasePrincipal> roles = new List <DatabasePrincipal>(); // Open connection to repository and get users. using (SqlConnection connection = new SqlConnection(Program.gController.Repository.ConnectionString)) { // Open the connection. connection.Open(); // Query the repository for db principals. SqlParameter paramSnapshotid = new SqlParameter(ParamSnapshotid, snapshotid); SqlParameter paramDbid = new SqlParameter(ParamDbid, dbid); SqlParameter paramRolememberid = new SqlParameter(ParamRoleMemberUid, uid); using (SqlDataReader rdr = Sql.SqlHelper.ExecuteReader(connection, null, CommandType.Text, QueryGetSnapshotUserRoles, new SqlParameter[] { paramSnapshotid, paramDbid, paramRolememberid })) { while (rdr.Read()) { // Read the fields. SqlInt32 id = rdr.GetSqlInt32((int)UserRoleColumns.Uid); SqlString name = rdr.GetSqlString((int)UserRoleColumns.Name); SqlString type = rdr.GetSqlString((int)UserRoleColumns.Type); // Create the role and add to list. DatabasePrincipal role = new DatabasePrincipal(id, name, type); roles.Add(role); } } } return(roles); }