4

I have a bunch of user accounts and server accounts which I need to create on SQL Azure (and then in 4 other environments for (dev,qa,uat & pre-production) and I'm running into some problems.

Problem 1. CREATE LOGIN must be done on master / CREATE USER must be done in the database. but since you can't switch databases with a Use statement, at the very least I'm going to need to do this in 2 batches.

Problem 2. You don't seem to be able to execute the CREATE LOGIN/CREATE USER code in either dynamic sql or as part of an IF block so I can't do an IF NOT EXISTS check around each create statement.

Am I snookered here, or are there any other easy/robust ways to script the creation of SQL Logins & DB Users with some basic existance checking in SQL Azure ?

Improve this question

2 Answers 2

Reset to default
2
+50

You should be able to use Powershell for Azure to accomplish what you need.

Here is an example of how to create database: http://blogs.msdn.com/b/windowsazure/archive/2013/02/07/windows-azure-sql-database-management-with-powershell.aspx

Here is a powershell example of creating sql users: http://sqldbawithabeard.com/2013/09/23/add-user-to-database-role-with-powershell/

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

1 Comment

any idea on how create Azure AD users w/ PowerShell?
2

I'm sure you've resolved this, but I've just used this script in Azure SQL to create a login if it doesn't exist (running against master):

IF NOT EXISTS (SELECT * FROM sys.sql_logins WHERE name = 'Blah')
    CREATE LOGIN Blah WITH PASSWORD = 'BS#ah12!!@#' 
ELSE
    PRINT 'Already exist'

and this script to create the user if it doesn't exist (running against the actual database):

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name='Blah')
    CREATE USER Blah FOR LOGIN Blah WITH DEFAULT_SCHEMA = dbo
ELSE
    PRINT 'Already exists'

(As a side note I should be pulling the sid value from sys.sql_logins and using that to lookup sys.sysusers, but I'm using convention to work around that. See https://stackoverflow.com/a/36654590/1462905)

Improve this answer

2 Comments

Correct me if I am wrong, but this does not actually answer the question posed above does it? This sysuser table exists in every database, and your script is only creating it in the context of the database it is running. (master).
The first script needs to run in a connection against master, and the second script runs against the database. I'll update the answer to make it more clear.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.