Minimal SQL Server permissions for ApexSQL Developer tools (4216304)

Applies to
ApexSQL Developer tools, ApexSQL Plan and ApexSQL Propagate

Summary
This article describes minimal SQL Server permissions for ApexSQL Developer tools, ApexSQL Plan and ApexSQL Propagate

Description

In order to properly use ApexSQL Developer tools certain minimal SQL Server permissions need to be allowed on databases that are being used.

Required permissions

Required permissions are public SQL Server role and the public role membership that need to be configured along with the login.

Minimal SQL Server permissions that need to be set on a database are:

• Connect
• Select
• View definition
• db_owner (in specific cases on the destination database only)
• Show plan (applies only on ApexSQL Plan)

Configuration

To demonstrate how to set these permissions, an example login Max will be created, along with minimal SQL Server permissions required to use ApexSQL Developer tools

To create a new login, in the Object Explorer of SQL Server Management Studio, expand the Security folder, right-click the Logins folder and click the New Login command:

Once the Login – New window is shown, under its General tab, the Login name can be defined, along with authentication way, password, and default database and language:

Switch to the Server Roles tab and check the public server role:

The last thing before creating a new login is to switch to the User Mapping tab, check a database for which the newly created user will be mapped and check the public role membership for a checked database:

Once the OK button is clicked, the new login Max will be created and shown under the Logins folder:

In addition to already permissions set above, the following minimal SQL Server permissions should be set on a database in order to be properly used by ApexSQL Developer tools and ApexSQL Propagate:

• Connect
• Select
• View definition

To set these permissions, right-click a desired database in Object Explorer and click the Properties:

After the Database Properties window is shown, switch to the Permissions tab, select the previously created user, and check the above-mentioned permissions, so they can be granted for the selected user, once the OK button is clicked:

As for ApexSQL Plan, one additional permission to above mentioned ones that needs to be granted for a user, is the Show plan permission:

The Show plan permission is required to view execution plans.

At the end, the db_owner database role membership is required on the destination database, for the following ApexSQL Developer tools and ApexSQL Propagate in the cases below:

• ApexSQL Build – to execute the update script
• ApexSQL Data Diff – to execute the data synchronization script
• ApexSQL Decrypt – to execute the decryption script
• ApexSQL Diff – to execute the schema synchronization script
• ApexSQL Generate – to execute the populate script
• ApexSQL Propagate – to execute scripts on specified databases
• ApexSQL Refactor – to execute the following commands:
  • Add surrogate key
  • One to many relationships
  • Safe rename
  • Split table
  • Change parameters
• ApexSQL Search – to execute the Safe rename command

All of these commands in GUI could be written in T-SQL, in order to create new login and allow set minimal SQL Server permissions:

USE [DBName]
GO

CREATE LOGIN [Max] WITH PASSWORD = '<password>'; 
GO

GRANT CONNECT TO [Max]
GO

GRANT SELECT TO [Max]
GO

GRANT VIEW DEFINITION TO [Max]
GO

CREATE USER [Max] FOR LOGIN [Max]
ALTER ROLE [db_owner] ADD MEMBER [Max]
GO

GRANT SHOWPLAN TO [Max]
GO

Troubleshooting

Depending on missing permission, the following issues might be encountered:

1. If the Connect permission is not granted for a database the below error message will be shown:

   Cannot connect to ‘ServerName’.
   Cannot open user default database.
   Login failed for user ‘Max’

2. If the Select permission is not granted for a database the below error message will be shown:

   The SELECT permission was denied on the object 'ObjectName', database 'DBname', schema 'SchemaName'

3. If the View definition permission is not granted for a database the below error message will be shown:

   User does not have permission to perform this action

4. If the db_owner database role membership is not granted for a destination database, the below error message will be shown:

   You are not a member of ‘db_owner’ role in database ‘DBname’.
   You can continue but some functions may not work because of lack of permissions.
   Do you want to continue?

5. If the Show plan permission is not granted for a database the below error message will be shown:

   SHOWPLAN permission denied in database ‘DBname’