Extract specific GRANT permission from Export-DbaUser

[L3onard80]

Summary of new feature

Often it happens that grants are given directly in prod to users without starting from the test databases so when we bring new developments these are overwritten. Export-DbaUser it already contains what it would take to restore them but it is together with many other commands that for my daily operations are "too many" and I have to go to extrapolate from the output file the only rows that interest me.

Proposed technical details (if applicable)

It would be interesting to have a parameter like: -Permissions = 'GRANT SELECT'

Actual output from Export-DbaUser (extract) USE [MyDB] GO IF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'CONTOSO\BI_REPORT') CREATE USER [CONTOSO\BI_REPOR] FOR LOGIN [CONTOSO\BI_REPORT] WITH DEFAULT_SCHEMA=[dbo] GO GRANT CONNECT TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW1] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW2] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW3] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW4] TO [CONTOSO\BI_REPOR] AS [dbo];

My desire Export-DbaUser -SqlInstance SQLSRV -Database MyDB -Permissions = 'GRANT SELECT'

GRANT SELECT ON OBJECT::[dbo].[VIEW1] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW2] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW3] TO [CONTOSO\BI_REPOR] AS [dbo]; GO GRANT SELECT ON OBJECT::[dbo].[VIEW4] TO [CONTOSO\BI_REPOR] AS [dbo];

Obviously he should consider all the other grants: 'GRANT CONNECT', 'GRANT VIEW DEFINITION', 'GRANT VIEW DATABASE' etc. etc.

Latest version of dbatools as of writing

I'm using 1.0.109

[wsmelton]

Why don't you just sync permissions from prod to lower environments?

[L3onard80]

Why don't you just sync permissions from prod to lower environments?

Hi Shawn, often bi manager assigns grants directly in production without aligning the developers and when they bring the changes into production these permissions are lost. People would like to run this script on their own to directly save grants without asking who and when it was modified.

[potatoqualitee]

Great idea! We could potentially address this by adding [string[]]Type and then Grant, Revoke, Whatever?. Then set the default to the current output? What you think @ClaudioESSilva?

[wsmelton]

Being able to deal with filtering based on what was granted I would see being problematic to maintain because those change in both what you assign and syntax by version.

I think limit it to the type of permission will be best. (Just grant and not grant select)

[wsmelton]

Thinking about this more, should an export command have this level of filtering? We do not allow this level in any of the export commands we have to date.

Would it not be more proper to have you get the permissions you want that a user has assigned and pipe those to an export command?

Get-DbaUserPermission ... | Export-DbaUser

Or just use

Get-DbaUserPermission ... | Export-DbaScript

[L3onard80]

I think limit it to the type of permission will be best. (Just grant and not grant select)

I guess it is easier to make, however, it would force the user to put his hand to the output to remove the unnecessary grants.

[wsmelton]

I guess it is easier to make, however, it would force the user to put his hand to the output to remove the unnecessary grants.

Use of Get-DbaPermission solves that problem and I think what you are wanting in this issue as well...based on what I see the code doing.

Having the ownership be on our-side adds overhead of maintaining code that has to account for every feature and permission option in SQL Server for every version we support, including the builds in Azure PaaS. Consider the Get command itself has specific T-SQL to filter out what permissions it is pulling for the database level and this alone may already be out-dated with what is available in SQL Server 2019, and even Azure SQL DB/MI:

https://github.com/sqlcollaborative/dbatools/blob/fb31a745b0ea3554c0ca0a3e77955227c8791f5e/functions/Get-DbaPermission.ps1#L193-L203

[L3onard80]

Also in this command, however, something is missing that keep out ONLY grants (for example)

Get-DbaPermission -SqlInstance SQLSRV -Database MyDB -Grant(will read from the GrantStatement source)

(extract from Get-DbaPermission) ... GrantStatement : GRANT SELECT ON VIEW1 TO [CONTOSO\BI_REPORT]]

[wsmelton]

That would simply be fixing the query used I'm sure, or how the filter is being applied to the dataset.

[PowerDBAKlaas]

@L3onard80 Get-DbaPermission ... | Select grantstatement or Get-DbaPermission ... | Where PermState -eq 'GRANT' won't do?

[L3onard80]

@L3onard80 Get-DbaPermission ... | Select grantstatement I LIKE IT! Thanks! Get-DbaPermission ... | Where PermState -eq 'GRANT' This DOESN'T work as expected and does an infinite swipe of commands.

[PowerDBAKlaas]

@L3onard80 I'm sorry, that was meant as pseudo code. Be careful: Get-DbaPermission will always create a grantstatement and a revokestatement, even if the actual permstate is DENY or anything else. You probably want to execute Get-DbaPermission -SqlInstance ... | Where-Object {$_.permstate -eq 'GRANT'} | Select-Object grantstatement

So what this issue concerns, instead of adding this functionality to Export-DbaUser , we probably rather need a 'CurrentStatement' property on Get-DbaPermission ? @potatoqualitee @wsmelton

[L3onard80]

Perfect works exactly like the first command (Get-DbaPermission ... | Select grantstatement). Thanks again, Klaas.

[andreasjordan]

I'm not sure if this is still a request for a new feature. @L3onard80 : Do you still want to have the new parameter -Permission? At all that have taken part in this issue: Is there a request for a feature inside of the discussion that I've missed? Please repeat here to plan futher steps. Thanks.

[L3onard80]

I'm not sure if this is still a request for a new feature. @L3onard80 : Do you still want to have the new parameter -Permission? At all that have taken part in this issue: Is there a request for a feature inside of the discussion that I've missed? Please repeat here to plan futher steps. Thanks.

Hi Andreas, the posts above gave me a workaround solution (that works, btw) but yes, i think having a parameter for the command is really helpfull.

[PowerDBAKlaas]

I would agree if such a parameter made the function faster. If not, filtering should be done with a Where-Object or Where() method. What I see as added value is an extra property 'AppliedStatement' or something, which would be a copy of either the Grant or the Revokestatement. This would be the easiest way to select all statements that need to be saved or applied in a sync or restore process.

[wsmelton]

A param of that nature means we also have to clean up additional lines and is not just a string filter.