public void TestUserNameShouldBeEscaped()
{
var user = new SqlUser("foo.bar", "dbo");
var createScript = user.ScriptCreate();
StringAssert.StartsWith("CREATE USER [foo.bar]", createScript);
}
private void LoadUsersAndLogins(SqlCommand cm) {
// 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 (var 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);
}
}
try {
// 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 (var dr = cm.ExecuteReader()) {
while (dr.Read()) {
u = FindUser((string) dr["name"]);
if (u != null && !(dr["password_hash"] is DBNull))
u.PasswordHash = (byte[]) dr["password_hash"];
}
}
} catch (SqlException) {
// SQL server version (i.e. Azure) doesn't support logins, nothing to do here
}
}