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