New Feature - Clone login with all DB-level permissions included

[nvarscar]

---

name: New feature - clone a certain login including all the DB-level permissions 💣
about: Either a new function, or a new switch in New-DbaLogin to get permissions from each database of the source login and apply them to a new login.

---

Summary of new feature

• Right now there is no certain way of cloning user permissions fully. Sync-DbaLoginPermissions only does so on the login level, using underlying function Update-SqlPermissions, that only covers database level permissions and roles; all the object-level permissions are ignored. There is Export-DbaDbUser, which, if used together with Export-DbaDbLogin and some regex magic, might do the trick. However, I would rather have that functionality built-in into module, as this is one of the most popular questions I get: create new login A with permissions like user B.
• Options to consider:
  • Get-DbaLogin LoginA | New-DbaLogin LoginB -IncludeDBPermissions
  • Copy-DbaDbPrivileges -From LoginA -To LoginB
  • (Export-DbaUser -Login LoginA -IncludeLoginScript) -replace 'LoginA','LoginB'

Proposed technical details (if applicable)

This will all come down to the following actions inside a function:

1. Get databases that source login has users in.
2. Get DB permissions script for each of the users in that list.
3. Add login creation part if necessary
4. Replace names from LoginA to LoginB using regex.
5. Run the statements against the target server

Latest version of dbatools as of writing

• 0.9.382

[nvarscar]

I'm ready to do the coding part, but will need help to decide what's the best approach here

[niphlod]

IMHO the less regex magic the better. I'm notoriously not good at naming convention but I'd say that this is a good candidate for:

• new-dbalogin -Sqlinstance dest (creates a shallow login, just to be overwritten)
• get-dbalogin -sqlinstance source
• Set-dbalogin -sqlinstance -dest -name theshallowfrombefore
  It's funny, but indeed there are a lot of steps involved, and almost all of our copy-smth just deal with an element being transferred from instances, not creating a new entity with a different name.
  Maybe Get-DbaLoginTemplate -sqlinstance source -name thesourcelogin | New-DbaLogin -sqlinstance dest -name thedestlogin !?!?
  Dunno if we may have similar requests for other "Copy" but this is indeed a Copy, just with the same Source and Dest instances, but different target name.
  As always, the reason why get-verb | where name -eq 'clone' is empy makes me wanna cry.

[nvarscar]

Trying to get back on track with this issue. All of the workarounds that I had in mind are not working as I would want them to. And yes, I would rather go without Regex magic, but for some items that would be quite impossible.
After spending another 4 hours of my life in a mess that is SMO database permissions, I say, T-SQL is a way to go, which means the best way to do this is adjusting Export-DbaUser to change user/login name appropriately and creating a new function, that would take a source login object from the pipe and import it (Import-DbaLogin?) under a different name onto the same (or different) server with the following steps:

• creating a new login by passing a login object into New-dbalogin with a new name
• adjusting server roles and permissions (re-use the code of Export-DbaLogin)
• for all of the database in $oldLogin.EnumDatabaseMappings() , run Export-DbaUser with renaming options enabled
• ... anything else?

[leedsdata]

I like the approach below for what it's worth...

Get-DbaLogin LoginA | New-DbaLogin LoginB -IncludeDBPermissions

But you could run into issues if the login already exists? What to do in that scenario? Remove the login and re-add it with the permissions from the Get?

[enolke]

I have been watching this item for awhile and wanted to say I think this would be amazing and really popular if you guys got this into development again.

Thanks!

[nvarscar]

I have a prototype that enables Copy-DbaLogin to clone existing logins with a different name. I just need to make another push to include object level permissions. Since that would change the function behaviour, I was going to lock it down behind a switch and hope that it would be enough to make it work for everyone.

[enolke]

Fantastic! I would love to help test when possible.

…

On Tue, Jun 9, 2020 at 3:14 PM Kirill Kravtsov ***@***.***> wrote: I have a prototype that enables Copy-DbaLogin to clone existing logins with a different name. I just need to make another push to include object level permissions. Since that would change the function behaviour, I was going to lock it down behind a switch and hope that it would be enough to make it work for everyone. — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub <#3864 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/ANXXU36EKRNC2WPSIDYSOSLRV2X3XANCNFSM4FNFQLUQ> .

[nvarscar]

Fantastic! I would love to help test when possible.

I created a Draft PR, feel free to test it from the source branch. FYI, object level permissions have made it too.