I'm trying to use SQL Server SMO in PowerShell to get a list of objects permissioned to a database role but the following code doesn't seem to work:
[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$instanceName = 'DEVECPVM010346\DMGBVSQL01'
$SMOserver = New-Object ('Microsoft.SqlServer.Management.Smo.Server') -argumentlist $instanceName
$SMOserver.Databases['SomeDatabase'].Roles['WebAppPoolRole'].EnumObjectPermissions()
A bit of searching turns up posts from SQL Server 2005 days stating that EnumObjectPermissions() doesn't work but that MS has said it'd be fixed in SQL Server 2008. Well I'm using SQL Server 2017 and it still doesn't work.
Unless I'm doing something wrong?
I was hoping to avoid having to use T-SQL to get the list of objects permissioned to the db role.
I've even tried the SQL Server PS module (which I guess uses SMO?) and even the following doesn't work:
$db = Get-SqlDatabase -ServerInstance 'DEVECPVM009562\DMGBVSQL01' -Name 'SomeDb' $db.Roles['WebAppPoolRole'].EnumObjectPermissions()
From what I've been able to tell from working with SMO over the years, permissions belong to the securable, not to the grantee. So calling $role.EnumObjectPermissions() will get any explicit permissions for which the role is the explicit securable. For example, if I do this in my database:
create role Blah;
create user BlahUser without login;
grant select on dbo.myTable to Blah;
grant take ownership on role::Blah to BlahUser;
(so that I have permissions where Blah is both the securable and the grantee) and then run this powershell (though any method of working with SMO would be fine)
$db = Get-DbaDatabase -SqlInstance . -Database foobar;
$role = $db.Roles['Blah'];
$role.EnumObjectPermissions();
I get only the permission where Blah is the securable.
That said, all may not be lost! Just messing around, it seems that if you ask for permissions at the database level, you can get what you're looking for.
$db.EnumObjectPermissions('Blah');