May 19, 2014 at 3:56 am
I'm building a Database Project in VS2013 with the intention of incorporating the database into a new Cloud App.
I'm encountering issues around the management of Logins using scripts within the Database Project by the following restriction within SQL Azure Database:
Msg 40530, Level 16, State 1, Line 2
The CREATE LOGIN statement must be the only statement in the batch.
Traditionally I would've used logic which determines whether the Login already exists but that seems problematic now that the CREATE LOGIN statement must be the only statement in the batch.
IF NOT EXISTS (SELECT 1 FROM sys.server_principals WHERE name = N'<LoginName>')
BEGIN
CREATE LOGIN [<LoginName>] WITH PASSWORD ='<StrongPassword>'
END
In the meantime, I've resorted to standalone scripts which I apply manually and arbitrarily after deploying (ie. 'Publishing') the Database Project but this itself will become problematic as the project environment grows.
I've googled this and searched this forum and others, can anyone think of a way to incorporate the Login management into the Database project?
May 19, 2014 at 7:34 am
Security has frequently been a problem in SSDT. My recommendation would be to use the post-deployment scripts. That way you can break it down as needed, but still have it as part of the project, maintained in the source control system, etc.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 19, 2014 at 9:49 am
Good call, thanks Grant. I was intending to include the scripts in the Project but the issue with Azure SQL Database syntax around CREATE LOGIN means you cannot wrap any logic around the statement.
The project similarly fails with errors when you try to Publish (because the LOGINS already exist).
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply