-
Notifications
You must be signed in to change notification settings - Fork 1
/
GetDatabasePermission.cs
35 lines (31 loc) · 1.12 KB
/
GetDatabasePermission.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
using Dapper;
using System;
using System.Collections.Generic;
using System.Text;
namespace Albatross.Database.SqlServer
{
public class GetDatabasePermission : IGetDatabasePermission {
IGetDbConnection getDbConnection;
public GetDatabasePermission(IGetDbConnection getDbConnection) {
this.getDbConnection = getDbConnection;
}
public IEnumerable<DatabasePermission> Get(IDatabaseObject @object) {
using (var db = getDbConnection.Get(@object.Database)) {
return db.Query<DatabasePermission>(@query, new { schema = @object.Schema, name = @object.Name });
}
}
const string query = @"
SELECT
permission.state_desc as [State],
permission.permission_name as Permission,
principals.[name] as Principal
FROM sys.objects obj
join sys.schemas on schemas.schema_id = obj.schema_id
join sys.database_permissions permission ON permission.major_id = obj.object_id
join sys.database_principals principals on principals.principal_id = permission.grantee_principal_id
WHERE
permission.state IN ('G', 'W') -- GRANT or GRANT WITH GRANT
and obj.name = @name and schemas.name = @schema
";
}
}