Skip to main content

We are developing with our SQL Server for Thinkwise on Azure.

We have various users as sf_developers. Unfortunately it is currently only possible to deploy changes in our (data) model when logged in as tsf_admin. Other users are not allowed to make changes to the application database. What do I need to do so that other sf_devolepers can also deploy their changes to the application database?

Related to this:
Currently I also have to create users for each application database. So also for all sf_developers so that they can test their own changes. Can this be automated in some way?

Hi Roy, 

Assuming your developers can already login to the Azure SQL Server, you have to add them to the database owner role of the application database. To do this, right click the Users node in SQL Server Management Studio and select New User. This will open a new query window with a  SQL template to add a user to the db_owner role:

-- ==================================================
-- Create User as DBO template for Azure SQL Database 
-- ==================================================
-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
	FOR LOGIN <login_name, sysname, login_name>
	WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

Tip: Press Ctrl+Shift+M to specify values for the template parameters.

 

For the users of an application database, you can use the apply rights tasks in IAM. 

Hi Jasper,

Thank you for this information. This works for existing databases. Unfortunately it does not seem to work for creating a new database. It seems that this first step still needs a user with more rights. Probably not on the database (as this still must be generated), but on the server.

Creating an empty database (even through SSMS) requires some sort of superuser kind of rights. After creating an empty database and giving a user the rights through the indicated way (create user and add role member), this user can push updates to the database.

Is there a way that "regular developers” (without the superuser-rights) can also create an empty database?

To create a database, a login must be one of the following:

  • The server-level principal login
  • The Azure AD administrator for the local Azure SQL Server
  • A login that is a member of the dbcreator database role

 

Source: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-transact-sql?redirectedfrom=MSDN&view=azuresqldb-mi-current&tabs=sqlpool#permissions-2

Terms & ConditionsCookie settings