ME Trial 3 – Logins module and Clone Users utility

A quick glance behind, and ahead

Last time, we introduced the Database Files module and the Script Schemas module.

This time, we’ll look at the Logins module and the Clone Users utility.

The Logins module

You might have guessed by now that the Logins module collects SQL Server login information from your managed servers. What this gives you is a record of all logins – and their create date, default database, language, login type, policy enforcement, and more – for the whole enterprise.

This is the foundation for a huge amount of security-related functionality, including research, alerts, and audits. As a matter of fact, the information collected here is used in a number of utilities and reports…but we’ll get to that later.

The Logins module does more than just gather login information. Because the login collection is meant to serve the higher purpose – specifically, of providing a snapshot of server level security – the logins collection also gathers server level permissions, and information on endpoints.

Let’s look at the Logins module objects.

Objects in the Logins Module

Tables

Logins – Holds collected information about logins from each server.
ServerPermissions – Holds collected server permissions data from each server.
Endpoints – Holds collected endpoint data from each server.

Views

There are %Current and %Previous views for each one of these tables (e.g., Collector.LoginsCurrent, etc.)

Procedures

Report.LoginADGroupMembershipByLogin – This procedure answers the question “What servers does this AD account have access to, and via what AD groups?” It shows the chains of access that enable it to login to managed SQL Server instances. Note that this report uses collected data from both the Logins module, and the AD Group Members module.

So, if we run this for my own account (EXEC Report.LoginADGroupMembershipByLogin @LoginName = N’Midnight\Jen’;), we’ll get back some very useful information about where I have rights, and via what Active Directory groups:

Report.LoginCountsOnServers – This procedure is called from the SSRS report SALoginCountPerServer. It combines data from the Logins module with collected AD information to give a count of logins per server. If you run it manually for one or more servers (e.g., EXEC Report.LoginCountsOnServers @ServerList = ‘MINIONDEVCON,SQLCON’;), you’ll receive a list of logins, their types, and the servers they have access to:

NOTE: There are additional, login-related objects in the following utilities:

Utility: Clone Users
Utility: SID Server
Utility: Login Password Strength Audit

The Clone Users utility

As of Minion Enterprise version 2.2, 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.

Clone Logins using Clone.Login

Today we’ll look at a few examples using the Clone.Login stored procedure.

To script out the SQL login Login1 and all permissions, use @LoginToClone = ‘Login1’, @NewLogin = NULL, @CopyPword = 1, and @Push = 0:

EXEC Clone.Login

   @InstanceID = 1,

   @LoginToClone = 'Login1',

   @NewLogin = NULL,

   @CopyPword = 1,

   @LoginType = 'SQL',

   @Pword = NULL,

   @Push = 0;

To clone Login1 as Login2 on Server1 (the same server), with a new password, use @NewLogin = ‘Login2’, @CopyPword = 0, @Push = 1, and be sure to set the new password in @Pword:

EXEC Clone.Login

  @InstanceID = 1,

   @NewInstanceID = NULL,

   @LoginToClone = 'Login1',

   @NewLogin = 'Login2',

   @CopyPword = 0,

   @LoginType = 'SQL',

   @Pword = 'NewPassword100!!0!',

   @Push = 1;

To clone Windows login DOMAIN\Login1 as a role called “Login1Role” From Server1 to Server2, be sure to set the new instance ID, the new login name, and set @LoginType = ‘Role’:

EXEC Clone.Login

   @InstanceID = 1,

   @NewInstanceID = 2,

   @LoginToClone = 'DOMAIN\Login1',

   @NewLogin = 'Login1Role',

   @CopyPword = 0,

   @LoginType = 'Role',

   @Pword = 'NewPassword100!!0!',

   @Push = 1;

There’s a lot more functionality in the Clone Logins utility, and even other utilities…all available because Minion Enterprise gathers a comprehensive set of security data.

Write to us at Sales@MinionWare.net for a demo, a trial, and a quote. Write to us at https://minionware.freshdesk.com for help and suggestions. And, get more information on our Minion Enterprise YouTube playlist.

Next time, we’ll look at the Drive Space Module.