Applies to
Summary
This article will define basic SQL Server permissions to create an SQL database with the help of ApexSQL Build.
Description
There is specific basic SQL Server permission that needs to be checked to properly create an SQL Server database.
For the basic SQL Server role and the role, membership public is required permissions that need to be arranged along with login for all ApexSQL Developer tools.
Besides public roles, these basic SQL Server permissions should also be set on the database:
- Connect
- Select
- View definition
- Db_owner (in specific cases on the destination database only)
- The Minimal SQL Server permissions for ApexSQL Developer tools article contains more information about minimal SQL permission and how to set it
Further, in the article, additional permissions besides previously enumerated permissions to create the SQL database will be explained.
Minimal SQL Server permissions to create SQL databases
Through the process of creating a Login and User, how and which permissions are added will be explained.
To create a new Login, when connected to the desired SQL Server, from the Object Explorer expand the Security node and right-click on the Logins tab, then from the opened context menu choose the New Login option:
The Login – New window will open, wherein the General tab for the article a Test login will be created as shown below:
Then go to the Server Roles tab to check if Test login has a public server role:
After that, visiting the User Mapping tab is the next step of creating the login where the mapping will be set to this newly created login. The phone database will be checked for this article, and the Test user will be created for this database. Also, the public database role membership is checked by default. When all is checked and finished, by clicking the OK button the new Login Test will be created:
After the new Login is created, one more step is needed so that the newly created Login can create the database.
To do that, open the context menu for the server in the Object Explorer pane, and click on the Properties option:
In the Server Properties window, click on the Permissions tab, and from the Logins and roles grid select the Test login. After that, from the Explicit tab in the Permission grid, check the Grant checkbox for the Create any database option and click the OK button to close the window:
In this way, permissions for the create SQL database are given to the log-in.
Permissions for updating SQL databases
ApexSQL Build can, besides creating SQL databases, also update them. In this section, what permissions for updating the database are needed will be explained.
To have the ability to update the existing database, a new user needs to be created for the previously mentioned Test Login on the master database.
In the Object Explorer, expand the System Databases to locate the master database. Then expand the content of the master database, and from the Security folder open right-click context menu for the Users folder. Choose the New User option and start the process of adding a new user to the master database:
The Database User – New window will be opened. Click the General tab and type the name of the User and the Login name. For this article, Test will be used as the user and the login name:
Next, in the Membership tab select the db_owner database role. Click the OK button to create the user with db_owner database role membership:
When the new user is created, open the context menu for the master database and click on the Properties option:
The Database Properties window will be opened. Click on the Permissions tab and from the Users or roles grid, select the Test user. Then go to Permissions grid and from Explicit tab give Grant permission for Create database and Execute. When permissions are given, click the OK button to save changes and close the window:
In this way, and with previously mentioned permissions for the Login and User, ApexSQL Build will create the database and also update the existing database without any problems.
