private async Task<IEnumerable<SqlUserGranting>> FetchDatabaseUserInfo(SqlConnectionInfo connInfo, string database)
{
IEnumerable<SqlPermission> perms;
IEnumerable<SqlRoleMembership> roles;
using(var connection = await connInfo.Connect(database))
{
// Fetch Permissions and role memberships
perms = (await connection.QueryAsync<SqlPermission>(@"
SELECT
u.principal_id,
u.name,
p.class_desc,
p.state_desc,
p.permission_name,
u.[sid],
(CASE WHEN p.class_desc = 'SCHEMA' THEN s.name
WHEN p.class_desc = 'DATABASE' THEN NULL END) AS object_name
FROM sys.database_permissions p
INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
LEFT OUTER JOIN sys.schemas s ON p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id
WHERE u.[type] = 'S'
")).ToList();
roles = (await connection.QueryAsync<SqlRoleMembership>(@"
SELECT mem_prin.name AS member, role_prin.name AS role, mem_prin.[sid] as [sid]
FROM sys.database_role_members mem
INNER JOIN sys.database_principals mem_prin ON mem.member_principal_id = mem_prin.principal_id
INNER JOIN sys.database_principals role_prin ON mem.role_principal_id = role_prin.principal_id
")).ToList();
}
return Enumerable.Concat(
perms.Select(p => SqlUserGranting.Create(p, database)),
roles.Select(r => SqlUserGranting.Create(r, database)));
}
private async Task <IEnumerable <SqlUserGranting> > FetchDatabaseUserInfo(SqlConnectionInfo connInfo, string database)
{
IEnumerable <SqlPermission> perms;
IEnumerable <SqlRoleMembership> roles;
using (var connection = await connInfo.Connect(database))
{
// Fetch Permissions and role memberships
perms = (await connection.QueryAsync <SqlPermission>(@"
SELECT
u.principal_id,
u.name,
p.class_desc,
p.state_desc,
p.permission_name,
u.[sid],
(CASE WHEN p.class_desc = 'SCHEMA' THEN s.name
WHEN p.class_desc = 'DATABASE' THEN NULL END) AS object_name
FROM sys.database_permissions p
INNER JOIN sys.database_principals u ON p.grantee_principal_id = u.principal_id
LEFT OUTER JOIN sys.schemas s ON p.class_desc = 'SCHEMA' AND p.major_id = s.schema_id
WHERE u.[type] = 'S'
")).ToList();
roles = (await connection.QueryAsync <SqlRoleMembership>(@"
SELECT mem_prin.name AS member, role_prin.name AS role, mem_prin.[sid] as [sid]
FROM sys.database_role_members mem
INNER JOIN sys.database_principals mem_prin ON mem.member_principal_id = mem_prin.principal_id
INNER JOIN sys.database_principals role_prin ON mem.role_principal_id = role_prin.principal_id
")).ToList();
}
return(Enumerable.Concat(
perms.Select(p => SqlUserGranting.Create(p, database)),
roles.Select(r => SqlUserGranting.Create(r, database))));
}