The Clone Users utility allows you to completely script and clone logins, with associated users and permissions, to one or more servers in the environment. With this module, you can:
  • Clone a login as either a SQL or a Windows login, no matter what type the original login is.
  • Clone login as a database role.
  • Clone a login on all servers and all databases on which it has rights.
  • Script all permissions on all servers, or by SLA, or by a number of other filtering criteria.

Tables:
  • Clone.LoginsToPush – Holds statements (such as CREATE LOGIN, sp_addsrvrolemember, CREATE USER, GRANT VIEW DEFINITION, and so on) to be pushed to other instances, for the purpose of cloning users.
  • History.LoginsToPush – Holds results for the login push operations.  As rows are processed from the Clone.LoginsToPush table, they are copied here and deleted from the above table. Any errors will be logged in the ErrorCode and ErrorMessage columns.
  • Clone.LoginScripts – This is the login history table.  Jobs run nightly to script out all permission statements and store them in this table.  Reference this table to see what the permissions were on a particular day.

Note: You can, of course, schedule the login scripting to run more frequently, if you need a more fine-grained history of permissions.

Side note: It might seems like Clone.LoginScripts should be in the History or the Collector schema. Our internal standards are that the History schema tables log non-scheduled events (like alerts, or login clone pushes), whereas Collector tables hold collected data. The Clone.LoginScripts table holds data that isn’t technically collected; it’s generated from collected data.  And if none of that really counts, then we also had this unbreakable reasoning: it means we can keep all of the Clone module objects logically together.

Procedures:
  • Clone.Login – Clones a single login on a single server (and all users in all databases on that server).  This is the procedure used to change the login type (to a Windows account, or to a SQL login, or to a role).  This actually creates a new login off of the one you specify. Use @Push = 0 to simply script the login, or @Push = 1 to push the login to another server. Note that the @LoginType parameter refers to the login type for the destination login. So, you could create the new login as a Windows login, a SQL login, or a Role.
  • Clone.LoginAllDBsAll – This clones all logins on an instance – both logins and database permissions for the entire instance. This is perfect for copying production permissions to a development server.
  • Clone.LoginAllServers – Clones a login on every server it has access to.  So if User1 has access to 27 servers, we can clone that user to User2, who ends up with all the same permissions on all the same servers. This is a very quick way to clone a login across your entire environment.
  • Clone.LoginMaster – Clones a login as another login on all servers. For example, you can clone Login1 as Login2 (with a new password) on ALL servers where Login1 has permissions.
  • Clone.LoginScriptByServerName – This is the same as above, only it accepts a delimited list of server names.  This is for specialized calls you need on a different schedule from the SLA schedule.
  • Clone.LoginScriptBySLA – Jobs call this procedure to script all logins for a given SLA. 

NOTE: This procedure relies on the login and user data in the Minion Enteprise repository being up to date.  To ensure this, make sure you run the CollectorLoginsGet and CollectorDBUsersGet jobs.  If you don’t, you may be scripting out outdated security.  When you tune this feature to your environment, look at how long those Collector jobs take, and run the ScriptSQLPerms job(s) after they’re complete.  A good way would be to run the supporting jobs in the evening, and this job early the following morning. 

Jobs:
  • ScriptSQLPerms-GOLD
  • ScriptSQLPerms-SILVER
  • ScriptSQLPerms-BRONZE
  • PushLogins – Pushes the clone statements (create login, and other permissions statements) from the Clone.LoginsToPush table, to the destination instances.

Executables:
  • CloneLoginPush.exe – Pushes any statements that need to be run from the main table above.  Once it pushes a row, it copies the row to the history table, and then deletes the original row.  Errors are also logged to the history table.
 
This module depends on “Current” views from several other modules, including:
  • Collector.DatabasePrincipalsCurrent
  • Collector.DBPermissionsCurrent
  • Collector.LoginsCurrent
  • Collector.DBRoleMembersCurrent
  • Collector.SysObjectsCurrent
  • Collector.TableColumnsCurrent
  • Collector.DBSymmetricKeysCurrent
  • Collector.DBASymmetricKeysCurrent
  • Collector.CertificateDatabaseCurrent
 

Use Cases

Clone or script one login:
If you want to… Use…
Script out the SQL login Login1 and all permissions EXEC Clone.Login
                @InstanceID = 1,
                @LoginToClone = ‘Login1’,
                @NewLogin = NULL,
                @CopyPword = 1,
                @LoginType = ‘SQL’,
                @Pword = NULL,
                @Push = 0;
Clone Login1 as Login2 on Server1, with a new password. EXEC Clone.Login
                @InstanceID = 1,
                @NewInstanceID = NULL,
                @LoginToClone = ‘Login1’,
                @NewLogin = ‘Login2’,
                @CopyPword = 0,
                @LoginType = 'SQL',
                @Pword = ‘NewPassword100!!0!’,
                @Push = 1;
Clone Windows login DOMAIN\Login1 as a role called “Login1Role” From Server1 to Server2 EXEC Clone.Login
                @InstanceID = 1,
                @NewInstanceID = 2,
                @LoginToClone = ‘DOMAIN\Login1’,
                @NewLogin = ‘Login1Role’,
                @CopyPword = 0,
                @LoginType = 'Role',
                @Pword = ‘NewPassword100!!0!’,
                @Push = 1;
Clone Login1 as Login2 (with a new password) on ALL servers where Login1 has permissions EXEC Clone.LoginMaster
                @LoginToClone = ‘Login1’,
                @NewLogin = ‘Login2’,
                @CopyPword = 0,
                @LoginType = 'SQL',
                @Pword = ‘NewPassword100!!0!’,
                @Push = 1;
 
Clone or script multiple logins:
If you want to… Use…
Script out all logins on Server1 EXEC Clone.LoginAllDBsAll
                @InstanceID = 1,
                @LoginToClone = ‘All’,
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 0;
Clone a list of logins on Server1 to Server2 EXEC Clone.LoginAllDBsAll
                @InstanceID = 1,
                @NewInstanceID = 2,
                @LoginToClone = ‘Login1,Login2,Login10,DOMAIN\Login3’,
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 1;
Clone all Windows logins on Server1 to Server2 EXEC Clone.LoginAllDBsAll
                @InstanceID = 1,
                @NewInstanceID = 2,
                @LoginToClone = ‘AllWindows’,
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 1;
Script out all logins on Server1 and Server2 EXEC Clone.LoginScriptByServerName
                @ServerName = 'Server1,Server2',
                @LoginToClone = 'All',
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 0;
Script out all SQL logins on all Gold level servers EXEC Clone.LoginScriptBySLA
                @ServiceLevel = 'GOLD',
                @LoginToClone = 'AllSQL',
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 0;
Script out all logins on all Silver level servers to the table of record (Clone.LoginScripts) EXEC Clone.LoginScriptBySLA
                @ServiceLevel = 'SILVER',
                @LoginToClone = 'All',
                @NewLogin = NULL,
                @CopyPword = 1,
                @Push = 1;
 

Further Discussion

The LoginScripts table is a fabulous way to add permissions to your processes.  You can get the latest version of the permissions, and you can also use the columns to filter the results as you need them.  For example, if you only need to script logins (and not database permissions), then use the StmtName column and to retrieve only the CREATE LOGIN statements.  If you want to also script server roles, add “OR StmtName = ‘sp_addsrvrolemember’”. 
To script the permissions for a specific user in a database (or group of databases), filter by the StmtLevel, which holds the database name. Additionally, you can filter by AcctName. 
And of course, you’ll never had orphaned user issues with our process because Minion Enterprise always script logins with the original SID.
 
 
https://minionware.desk.com/customer/portal/articles/2572238-clone-users-module
http://www.MinionWare.net