December 4, 2009 at 9:28 am
I have VB.Net executable that I am trying to convert to PowerShell. This is the sub that extracts permissions for a database role:
Public Sub ObjectPermissions(ByVal dbrname As String)
' this code will enumerate permissions on objects for a role - need to exclude db_* and public roles
Dim dbrp As ObjectPermissionInfo()
dbrp = db.EnumObjectPermissions(dbrname)
Dim opi As ObjectPermissionInfo
If db.Roles(dbrname).IsFixedRole = False And db.Roles(dbrname).Name <> "public" Then
For Each opi In dbrp
fs.WriteLine("GRANT " + opi.PermissionType.ToString + _
" ON [" + opi.ObjectSchema + "].[" + opi.ObjectName + "] TO [" + opi.Grantee + "]")
fs.Flush()
Next
End If
End Sub
I am having trouble with PowerShell not extracting the permissions. This is what I have so far but it doesn't do anything with extracting the permissions:
$objPermission = new-object Microsoft.SqlServer.Management.Smo.ObjectPermissionInfo
foreach ($dbrole in $db.Roles)
{
if (!$dbrole.IsFixedRole -and $dbrole.Name -ne "public")
{
$spcontent=$dbrole.script()
Out-File -InputObject $spcontent -FilePath $filepath -Encoding "Default" -Append
$objPermission=$db.EnumObjectPermissions($dbrole) | Select-Object objectschema, objectname, permissiontype
if ($objPermission)
{
foreach ($rp in $objPermission)
{
$spcontent="GRANT " + $rp.permissiontype.tostring() + " ON [" + $rp.objectschema + "].[" + $rp.objectname + "] TO [" + $dbrole.Name + "]"
Out-File -InputObject $spcontent -FilePath $filepath -Encoding "Default" -Append
}
}
}
}
Any help would be greatly appreciated.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 4, 2009 at 9:47 am
$objPermission=$db.EnumObjectPermissions($dbrole)
You are using $dbrole where before you used dbrname.
Could that be it?
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
December 4, 2009 at 9:53 am
Hi Gary - dbrname was the database name being passed to the VB.Net sub. In the POSH script, $db is the database and $dbrole is the role in the database.
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
December 4, 2009 at 12:41 pm
My point is that you are using $dbrole as the parameter when previously you where using dbrname. I am guessing that $dbrole and dbrname are not equivalent.
Good luck.
Gaz
-- Stop your grinnin' and drop your linen...they're everywhere!!!
December 7, 2009 at 3:18 pm
I figured out my problem. I was trying to get the permissions on a Role. Wrong place. It all works well when using the proper objects such as tables, views, stored procedures, etc. :-D:-D
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply