1

I'm using ARM templates to instantiate a new Sql Server resource on Azure and, as part of the setup, I need to create a few user logins.

It seems that ARM templates do not offer the options to create new user principals therefore I'm considering running a SQL command to do so:

CREATE LOGIN WITH PASSWORD = ''

The problem with using this command is that it fails if the user already exists and therefore it is not idempotent, which is something that we need.

How do I check if a login already exists on an Azure SQL Server?

simoneL
  • 602
  • 1
  • 7
  • 23

2 Answers2

3

The following script will create the login on the master database at the logical Azure SQL server level.

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

The following acript will create the user at the database level, if it does not exist already.

IF NOT EXISTS (SELECT * FROM sys.sysusers WHERE name='simonel')
    CREATE USER simonel FOR LOGIN Blah WITH DEFAULT_SCHEMA = dbo
ELSE
    PRINT 'Already exists'
Alberto Morillo
  • 13,893
  • 2
  • 24
  • 30
1

You can use this query:

If not Exists (select loginname from master.dbo.syslogins 
    where name = @loginName and dbname = 'PUBS')
Begin
    Select @SqlStatement = 'CREATE LOGIN ' + QUOTENAME(@loginName) + ' 
    FROM WINDOWS WITH DEFAULT_DATABASE=[PUBS], DEFAULT_LANGUAGE=[us_english]')

    EXEC sp_executesql @SqlStatement
End

For more details, you can reference this blog: Checking if a SQL Server login already exists

Hope this helps.

Leon Yue
  • 15,693
  • 1
  • 11
  • 23