Creating Logins and Users in Azure SQL

This is one of those things that comes up every now and then. Each time I have to go digging to remember the right syntax and steps involved.

Not any more! Today I spent a few extra minutes capturing a slightly re-usable script.

I’ll detect if you’re on master for the login creation, or not on master for user creation.

For further customisation you can comment out / tweak lines before running it.

For more useful info on permissions, see:
https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-role-members-transact-sql?view=sqlallproducts-allversions
Create logins and users on Azure SQL databases.
Adjust roles and schemas appropriately
DECLARE @user varchar(255) = 'USERNAME';
DECLARE @pass varchar(255) = 'PASSWORD';
DECLARE @schema varchar(255) = '[dbo]';
IF ((SELECT DB_NAME()) = 'master')
BEGIN
PRINT 'Running on MASTER';
PRINT 'Creating login and user ' + @user + '…';
Note, creating a user in master is useful as most tools such as SSMS will
connnect to master by default to present a list of databases to the user.
If you don't want to leak/reveal the databases on the server, you should
only create a login. Don't create a user in master.
DECLARE @create_login_cmd varchar(max) = '
CREATE LOGIN ' + @user + ' WITH password=''' + @pass + ''';
CREATE USER ' + @user + ' FOR LOGIN ' + @user + ' WITH DEFAULT_SCHEMA=[dbo];
';
EXEC (@create_login_cmd);
PRINT 'Done';
END
ELSE
BEGIN
PRINT 'NOT running on MASTER';
Create user in the relevant database
PRINT 'Creating user ' + @user + ' with schema ' + @schema + '…';
DECLARE @create_user_cmd varchar(max) = '
CREATE USER ' + @user + ' FOR LOGIN ' + @user + ' WITH DEFAULT_SCHEMA=' + @schema + ';
';
EXEC (@create_user_cmd);
PRINT 'Done';
PRINT 'Adding user to roles…';
EXEC sp_addrolemember 'db_datareader', @user;
EXEC sp_addrolemember 'db_datawriter', @user;
PRINT 'Done';
PRINT 'Granting specific permissions…';
If required
DECLARE @grant_on_schema_cmd varchar(max) = '
GRANT EXECUTE ON SCHEMA :: ' + @schema + ' TO ' + @user + ';
';
EXEC (@grant_on_schema_cmd);
PRINT 'Done';
END

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s