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 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:
- View definition
- db_owner (in specific cases on the destination database only)
- Show plan (applies only on ApexSQL Plan)
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:
- 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.
- 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
Depending on missing permission, the following issues might be encountered:
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’
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’
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
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?
If the Show plan permission is not granted for a database the below error message will be shown:
SHOWPLAN permission denied in database ‘DBname’