CREATE USER in Dynamic SQL on SQL Azure

Question

I want to write a stored procedure that checks the environment a database resides in (Based on the name) and creates the appropriate user and role for our application.

This would allow us to automate setting up permissions if we move a database between environments (Currently due to the limitations of Windows Azure SQL Database we have to manually run a script which is not ideal and prone to human error).

So the syntax we are using is:

DECLARE @UserToAdd VARCHAR(50) = (
    SELECT  CASE 
            WHEN @Environment = 'Development' THEN 'DevelopmentApplicationUser'
            WHEN @Environment = 'Test' THEN 'TestingApplicationUser'
            ELSE ''
        END
)

IF (@UserToAdd != '')
BEGIN
    EXEC ('CREATE USER [' + @UserToAdd + '] FOR LOGIN [' + @UserToAdd + '];')

    EXEC ('EXEC sp_addrolemember N''WebUser'', N''' + @UserToAdd + ''';')
END

This works correctly on our development server (SQL Server 2008 R2) but in Windows Azure SQL Database we get the below error:

The CREATE USER statement must be the only statement in the batch

Now the MSDN documentation does state:

If the CREATE USER statement is the only statement in a SQL batch, Windows Azure SQL Database supports the FOR | FROM LOGIN clause. If the CREATE USER statement is not the only statement in a SQL batch or is executed in dynamic SQL, the FOR | FROM LOGIN clause is not supported.

However this means that we cannot automate our permissions whatsoever.

Has anyone got around this issue and been able to produce dynamic sql that creates a user? Alternatively is there a way around this in a stored procedure?

As a note on this; the same issue occurs with ALTER USER. You can run CREATE USER [x] WITHOUT LOGIN however ALTER USER will not let you rebind it to a login — Luke Merrett
– Luke Merrett, Commented Oct 25, 2013 at 12:46

user844705 · Accepted Answer · 2013-12-04 11:34:51Z

2

I opened a support case with Microsoft to see if this is possible and the response was that if you want to check the existence of a login or a user and then create that login or user, you MUST use separate connections for the check and then the creation. It is NOT possible to check and create if not exists in the same transaction or batch. In Sql Azure.

Hth, Oli

answered Dec 4, 2013 at 11:34

user844705

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

2 Comments

Luke Merrett Over a year ago

Thank you Oli; so we could write a custom C# program or SSIS package that does this for us using 2 seperate batches, but we cannot perform the same logic is a stored procedure.

user844705 Over a year ago

Exactly. I used powershell to do it.

Maxui · Accepted Answer · 2013-10-30 09:18:04Z

1

I speak under correction here. you can create the user without FOR LOGIN then use sp_change_users_login to map the user to a login after the fact

answered Oct 30, 2013 at 9:18

Maxui

2101 gold badge6 silver badges15 bronze badges

1 Comment

Luke Merrett Over a year ago

Thank you @Maxui; that would work perfectly on a standard SQL Server instance, however I've just tried sp_change_users_login and it appears it is not supported on Windows Azure SQL Database. Bad times as that would've been exactly what we needed

Collectives™ on Stack Overflow

CREATE USER in Dynamic SQL on SQL Azure

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related