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.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| — 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 |
Šmonland 