SQL Estate Manager

SQL Estate Manager is a completely agentless install. Only a single instance of SQL Estate Manager must be deployed in your environment in order to document all the SQL Servers in your organization. In order to do so, you must make sure your system has been properly configured.

There are two modes of taking a snapshot with SQL Estate Manager. You can do so “manually” from the UI; in this case, the SQL discovery and data gathering will be performed through the user that is currently logged in. The other approach is to configure a service to create automatic snapshots on predetermined schedule. In the latter case, the SQL discovery and data gathering will be executed through the defined service user. Unless otherwise specified, both users are required to have all the privileges listed further in this article.

To run SQL Estate Manager and retrieve all the SQL Server settings you want to document, the user needs to have proper privileges. Here is the list of required privileges to load the SQL Server settings:

Local Administrators group on every machine. (required to retrieve the information about RAM, processors, disk space and other)
Sysadmin SQL Server role on all monitored SQL Servers
Special privileges for the Service Account. (Service account needs to have the privileges listed above along with the Log on as a Service right.)

Instructions

Follow these steps to give a user these privileges:

To add a user account to the Local Administrators group

On the server, click Start, right-click Computer and then click Manage.
Navigate to Configuration, expand Local Users and Groups, and then click Groups.
Right-click the Administrators group and then click Add to Group.
In the Administrators Properties dialog box, click Add.
In the Select User, Computers, or Groups dialog box, type the account name on which you want your worker process to run (for example, DomainName\YourAccount) in the Enter the object names to select box and then click OK.
In the Administrators dialog box, click OK.
Close the Server Manager screen.

To give a user account a SQL Server sysadmin role:

Connect to the SQL Server using SQL Server Management Studio.
Under Security, go to Logins, find the desired user, and right-click Properties.
Under Server Roles, make sure sysadmin is checked and click OK.

SQL Estate Manager database requirements

Creating a new database

To create a new SQL Estate Manager dedicated database, the user account running the installation and configuration wizard (i.e. the install account) should be granted dbcreator and securityadmin roles on the preferred SQL Server. This allows the account to create a new database and assign proper privileges after creation. The install account will be automatically given db_owner privileges on the newly created database if possible. Otherwise, the account should be given that privilege manually, because it is needed to upgrade the database.

Privileges required to run the application

These privileges will be granted automatically when a new SQL Estate Manager database is created or during a database upgrade.

The SQL Estate Manager service account needs to be granted SPDocKit_service_role role in the SQL Estate Manager database. This role will give the member of the service account db_datawriter and db_datareader roles and grant execute permissions on all the stored procedures in the database.
The account running the load from the SQL Estate Manager console must have the same privileges as the SQL Estate Manager service account (see above).

Active Directory Permissions

If you plan to use the Auto-discovery feature, the user that is creating a snapshot must have the ability to read objects (computers) from the Active Directory. If the user does not have read privileges on the entire directory, you can choose to discover objects only within a predefined set of Organizational Units.

This permission is required so that we can gather all the computers in your organization. Apply OU filtering if you would like to discover SQL Servers only on a particular set of computers in your organization.

Windows Server Privileges

The user running the tool must to be part of the local Administrators group on each computer that has the SQL Server installed. This privilege is required in order to obtain the list of installed SQL Server instances, components, and statuses.

If you are running the tool for the first time and would like to perform an initial discovery of all SQL Servers in your organization, we would recommend running the tools as a Domain Admin user.

SQL Server Privileges

The user running the tool must have a sysadmin role on each SQL Server and instance in order to retrieve information about SQL Server configuration, system, and user databases. When a new SQL Server is discovered, the user will be alerted if information about its settings could not be retrieved.

Additional resources

Connect to SQL Server When System Administrators Are Locked Out