Creating a Database User - SQL Azure

References¶

Adapted from https://blogs.msdn.microsoft.com/azuresqlemea/2016/10/05/create-sql-login-and-sql-user-on-your-azure-sql-db/

Procedure¶

1. Connect to your SQL Azure instance as an administrator to the MASTER database (That is, within SSMS Object Explorer, right-click the MASTER database and choose "New Query")

2. Execute the following script in the MASTER database

CREATE LOGIN MyUserName WITH PASSWORD=N'MyPassword'
GO
CREATE USER MyUserName FOR LOGIN MyUserName
GO
ALTER USER MyUserName WITH DEFAULT_SCHEMA=[dbo]
GO

3. Open a NEW QUERY against the application database

4. Execute the following script in the application database

CREATE USER MyUserName from LOGIN MyUserName
go
/* Grant permission for SELECT statements */
ALTER ROLE [db_datareader] ADD MEMBER MyUserName
GO
/* Grant permission for INSERT, UPDATE, and DELETE statements */
ALTER ROLE [db_datawriter] ADD MEMBER MyUserName
GO
/* Grant permission to execute stored procs in the "dbo" schema */
--GRANT EXECUTE ON schema::dbo TO MyUserName
GO

SQL Script¶

The following script will generate the above scripts for a set of users.

declare
    @Users table(Username varchar(100), Pwd varchar(25))

;with cte as (
          select username='User1', pwd = 'MyPassword1'
    union select username='User2', pwd = 'MyPassword2'
    )
insert into @Users (Username, Pwd)
select Username, Pwd from cte

/*--- Create Logins (Master DB) ---*/
select 
     Sort1 = 10
    ,Sort0 = username
    ,SqlCode = 'create login ' + Username + ' with password=N''' + pwd + ''''
from 
    @Users
union select
    Sort1 = 20
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
union select
    Sort1 = 30
    ,Sort0 = username
    ,SqlCode = 'create user ' + username + ' for login ' + username
from
    @Users
union select
    Sort1 = 40
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
union select
    Sort1 = 50
    ,Sort0 = username
    ,SqlCode = 'alter user ' + username + ' with default_schema=[dbo]'
from
    @Users
union select
    Sort1 = 60
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
order by Sort0, Sort1


/*--- Create Users (app DB) ---*/
select 
     Sort1 = 10
    ,Sort0 = username
    ,SqlCode = 'create user ' + Username + ' from login ' + username
from 
    @Users
union select
    Sort1 = 20
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
union select
    Sort1 = 30
    ,Sort0 = username
    ,SqlCode = 'alter role [db_datareader] add member ' + username
from
    @Users
union select
    Sort1 = 40
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
union select
    Sort1 = 50
    ,Sort0 = username
    ,SqlCode = 'grant execute on schema::dbo to ' + username
from
    @Users
union select
    Sort1 = 60
    ,Sort0 = username
    ,SqlCode = 'go'
from
    @Users
order by Sort0, Sort1