CODEWITHSUNDEEP
sqlazurepermissionsazure-sql-database
Luke Merrett
Luke Merrett

Reputation: 5824

CREATE USER in Dynamic SQL on SQL Azure

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?

Upvotes: 3

Views: 1089

Answers (2)

user844705
user844705

Reputation:

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

Upvotes: 2

Maxui
Maxui

Reputation: 210

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

Upvotes: 1

Related Questions