Automate Azure SQL database creation and add a database user

Question

I'm configuring a pipeline to deploy a test environment for an app, along with app services and storage there is a SQL database. I have already automated the creation of all of them in azure and is working.

After the test is finished, the environment is destroyed to save resources until a new test is required.

Now I need to add a SQL user to the test database while deploying, it's on the same server, to simplify the login already exists, so I want to assign the same to the new database.

Something like this.

Server1 already has user1 as login (was manually created)
Database1 already has user1 as user (was manually assigned)

Desired:

Add User1 as user of database2

There is a way to automate this while building the test environment?

Thanks in advance.

Venkataraman R · Accepted Answer · 2020-06-12 04:12:41Z

1

You can go for IF NOT EXISTS and create login and user accordingly.

IF NOT EXISTS(SELECT name 
                FROM [sys].[server_principals]
                WHERE name = N'test')
 BEGIN
    CREATE LOGIN test WITH password = 'TestPassword'
 END

 IF NOT EXISTS(SELECT name 
                FROM [sys].[database_principals]
                WHERE name = N'test')
 BEGIN
    CREATE USER test for login test;
 END

answered Jun 12, 2020 at 4:12

Venkataraman R

13.2k2 gold badges38 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

niva rojas Over a year ago

Hi @Venkataraman R thanks for your sugestion, it's working now, but instead of server_principals am using sys.sql_logins.

Collectives™ on Stack Overflow

Automate Azure SQL database creation and add a database user

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related