functions/Get-DbaPermission.ps1

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
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
Function Get-DbaPermission
{
<#
.SYNOPSIS
Get a list of Server and Database level permissions
 
.DESCRIPTION
Retrieves a list of permissions
 
Permissions link principals to securables.
Principals exist on Windows, Instance and Database level.
Securables exist on Instance and Database level.
A permission state can be GRANT, DENY or REVOKE.
The permission type can be SELECT, CONNECT, EXECUTE and more.
  
See https://msdn.microsoft.com/en-us/library/ms191291.aspx for more information
 
.PARAMETER SqlServer
The SQL Server that you're connecting to.
 
.PARAMETER Credential
Credential object used to connect to the SQL Server as a different user
 
.PARAMETER Databases
Return information for only specific databases
 
.PARAMETER Exclude
Return information for all but these specific databases
 
.PARAMETER Detailed
Shows detailed information
 
.PARAMETER IncludeServerLevel
Shows also information on Server Level Permissions
 
.PARAMETER NoSystemObjects
Excludes all permissions on system securables
 
.NOTES
Author: Klaas Vandenberghe ( @PowerDBAKlaas )
  
dbatools PowerShell module (https://dbatools.io)
Copyright (C) 2016 Chrissy LeMaire
This program is free software: you can redistribute it and/or modify it under the terms of the GNU General Public License as published by the Free Software Foundation, either version 3 of the License, or (at your option) any later version.
This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
You should have received a copy of the GNU General Public License along with this program. If not, see <http://www.gnu.org/licenses/>.
 
  
.LINK
https://dbatools.io/Get-DbaPermission
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987
 
Returns a custom object with Server name, Database name, permission state, permission type, grantee and securable
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987 -Detailed | Format-Table -AutoSize
 
Returns a formatted table displaying Server, Database, permission state, permission type, grantee, granteetype, securable and securabletype
 
.EXAMPLE
Get-DbaPermission -SqlServer ServerA\sql987 -NoSystemObjects -IncludeServerLevel
 
Returns a custom object with Server name, Database name, permission state, permission type, grantee and securable
in all databases and on the server level, but not on system securables
  
.EXAMPLE
Get-DbaPermission -SqlServer sql2016 -Databases master
 
Returns a custom object with permissions for the master database
 
#>

[CmdletBinding()]
Param (
[parameter(Mandatory = $true, ValueFromPipeline = $true)]
[Alias("ServerInstance", "SqlInstance")]
[string[]]$SqlServer,
[PsCredential]$Credential,
[switch]$Detailed,
[switch]$IncludeServerLevel,
[switch]$NoSystemObjects
)

DynamicParam
{
if ($SqlServer)
{
return Get-ParamSqlDatabases -SqlServer $SqlServer[0] -SqlCredential $Credential
}
}

BEGIN
{
# Convert from RuntimeDefinedParameter object to regular array
$databases = $psboundparameters.Databases
$exclude = $psboundparameters.Exclude

if ($NoSystemObjects)
{
$ExcludeSystemObjectssql = "WHERE major_id > 0 "
}
$ServPermsql = @"
SELECT
   [Server] = @@SERVERNAME
    , [Database] = ''
    , [PermState] = state_desc
 , [PermissionName] = permission_name
 , [SecurableType] = COALESCE(O.type_desc,sp.class_desc)
 , [Securable] = CASE WHEN class = 100 THEN @@SERVERNAME
       WHEN class = 105 THEN OBJECT_NAME(major_id)
       ELSE OBJECT_NAME(major_id)
       END
 , [Grantee] = SUSER_NAME(grantee_principal_id)
 , [GranteeType] = pr.type_desc
 
FROM sys.server_permissions sp
 JOIN sys.server_principals pr ON pr.principal_id = sp.grantee_principal_id
 LEFT OUTER JOIN sys.all_objects o ON o.object_id = sp.major_id
 
$ExcludeSystemObjectssql
 
;
"@


$DBPermsql = @"
SELECT
   [Server] = @@SERVERNAME
 , [Database] = DB_NAME()
 , [PermState] = state_desc
    , [PermissionName] = permission_name
 , [SecurableType] = COALESCE(O.type_desc,dp.class_desc)
    , [Securable] = CASE WHEN class = 0 THEN DB_NAME()
       WHEN class = 1 THEN ISNULL(s.name + '.','')+OBJECT_NAME(major_id)
       WHEN class = 3 THEN SCHEMA_NAME(major_id) END
    , [Grantee] = USER_NAME(grantee_principal_id)
 , [GranteeType] = pr.type_desc
 
FROM sys.database_permissions dp
 JOIN sys.database_principals pr ON pr.principal_id = dp.grantee_principal_id
 LEFT OUTER JOIN sys.all_objects o ON o.object_id = dp.major_id
 LEFT OUTER JOIN sys.schemas s ON s.schema_id = o.schema_id
 
$ExcludeSystemObjectssql
 
;
"@

}

PROCESS
{
foreach ($servername in $SqlServer)
{
try
{
$server = Connect-SqlServer -SqlServer $servername -SqlCredential $Credential
}
catch
{
if ($SqlServer.count -eq 1)
{
throw $_
}
else
{
Write-Warning "Can't connect to $servername. Moving on."
Continue
}
}

if ($server.versionMajor -lt 9)
{
Write-Warning "Get-DbaPermission is only supported on SQL Server 2005 and above. Skipping Instance."
Continue
}

if ($IncludeServerLevel)
{
Write-Debug "T-SQL: $ServPermsql"

$resultTable = $null
$resultTable = $server.Databases["master"].ExecuteWithResults($ServPermsql).Tables[0]
foreach ($row in $resultTable)
{
$obj = [PSCustomObject]@{
Server = $row.Server
Database = ""
PermState = $row.PermState
PermName = $row.PermissionName
Securable = $row.Securable
Grantee = $row.Grantee
SecurableType = $row.SecurableType
GranteeType = $row.GranteeType
}
if ($detailed) { $obj }
else { $obj | Select-Object Server, Database, PermState, PermName, Securable, Grantee }
}
}

$dbs = $server.Databases

if ($databases.count -gt 0)
{
$dbs = $dbs | Where-Object { $databases -contains $_.Name }
}

if ($exclude.count -gt 0)
{
$dbs = $dbs | Where-Object { $exclude -notcontains $_.Name }
}

# $dbs = $dbs | Where-Object {$_.IsAccessible}

foreach ($db in $dbs)
{
Write-Verbose "Processing $($db.name) on $servername"

if ($db.IsAccessible -eq $false)
{
Write-Warning "The database $($db.name) is not accessible. Skipping database."
Continue
}


Write-Debug "T-SQL: $DBPermsql"

$resultTable = $null
$resultTable = $db.ExecuteWithResults($DBPermsql).Tables[0]
foreach ($row in $resultTable)
{
$obj = [PSCustomObject]@{
Server = $row.Server
Database = $row.Database
PermState = $row.PermState
PermName = $row.PermissionName
Securable = $row.Securable
Grantee = $row.Grantee
SecurableType = $row.SecurableType
GranteeType = $row.GranteeType
}
if ($detailed) { $obj }
else { $obj | Select-Object Server, Database, PermState, PermName, Securable, Grantee }
}
}
}
}
END
{ }
}