Yes and no. First of all, at least in SSMS it appears you don’t have a GUI. I don’t use the GUI often unless I’m working on a T-SQL command I haven’t used much before but this could be major shock for some people. I right clicked on Security under the database and went to New -> User and a new query window opened up with the following:

-- ========================================================================================
-- Create User as DBO template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ========================================================================================
-- For login <login_name, sysname, login_name>, create a user in the database
CREATE USER <user_name, sysname, user_name>
	FOR LOGIN <login_name, sysname, login_name>
	WITH DEFAULT_SCHEMA = <default_schema, sysname, dbo>
GO

-- Add user to the database owner role
EXEC sp_addrolemember N'db_owner', N'<user_name, sysname, user_name>'
GO

Awesome! I did say I preferred code didn’t I? I am noticing a slight problem though. I don’t actually have a login yet. So I look in object explorer and there is no instance level security tab. On top of that when I try to create a login with code I get the following error:

Msg 5001, Level 16, State 2, Line 1
User must be in the master database.

Well, ok. That’s at least a pretty useful error. When I connect to the master database in SSMS (remember, you can only connect to one database at a time in Azure SQL DB) I do see security tab for the instance level and get the option to create a new login. Still script but that’s fine.

-- ======================================================================================
-- Create SQL Login template for Azure SQL Database and Azure SQL Data Warehouse Database
-- ======================================================================================

CREATE LOGIN <SQL_login_name, sysname, login_name> 
	WITH PASSWORD = '<password, sysname, Change_Password>' 
GO

So in the end you just need to create your login in master and your user in your user database. But do you really need to create a login? No, in fact you don’t. Azure SQL DBs act like partially contained databases when it comes to users. I.e. if you one of these commands you can create a user that does not require a login and authenticates through the database.

CREATE USER Test WITH PASSWORD = '123abc*#$' -- SQL Server ID
CREATE USER Test FROM EXTERNAL PROVIDER -- Uses AAD

That said I still recommend using a login in master. You can still specify the SID and that means that if you are using a SQL Id (SQL holds the password) you can create a new DB and associate it to the same login without knowing the password.

To that end I’m posting a set of queries that I’ve written to help me to find out who has what permissions on a given database or server. I’ve found these handy for comparing the permissions for two users, copying the permissions from one user (or AD group) to another (please be very careful when doing this, you can grant someone far more permissions than you intend), confirming that someone has the permissions expected, or just reviewing someones permissions in general. Below are the database set with some descriptions. I’ll post the server set soon.

The last two columns of each query are “drop & create” scripts. (This includes revoke & grant for permissions and sp_droprolemember & sp_addrolemember for roles.) These scripts are a work in progress, and to be honest I’m not that great when it comes to Certificates and Keys, so I haven’t even started those yet. I will continue to update them over time, and if anyone finds a bug PLEASE let me know so that I can fix it. For that matter if anyone want’s to add to these columns feel free to post it and I’ll add it to the scripts.

Commented out at the bottom of each script is a WHERE clause. I put it there as a place holder for when I need to look up a specific individuals permissions instead of everyone’s. Obviously this isn’t the only criteria I ever need, it’s just the most common.

First here is a list of users/database principals (read about users/database principals vs logins/server principals here).

-- Database Principals
SELECT DBPrincipals.name, DBPrincipals.sid, DBPrincipals.type, DBPrincipals.type_desc, 
	DBPrincipals.default_schema_name, DBPrincipals.create_date, DBPrincipals.modify_date,
	DBPrincipals.is_fixed_role, Authorizations.name AS Role_Authorization,
	CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
			'DROP ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
				WHEN 'K' THEN NULL
				WHEN 'R' THEN 'ROLE'
				WHEN 'A' THEN 'APPLICATION ROLE'
				ELSE 'USER' END +
			' '+QUOTENAME(DBPrincipals.name) + ';' ELSE NULL END AS Drop_Script,
	CASE WHEN DBPrincipals.is_fixed_role = 0 THEN
			'CREATE ' + CASE DBPrincipals.[type] WHEN 'C' THEN NULL
				WHEN 'K' THEN NULL
				WHEN 'R' THEN 'ROLE'
				WHEN 'A' THEN 'APPLICATION ROLE'
				ELSE 'USER' END +
			' '+QUOTENAME(DBPrincipals.name) END +
			CASE WHEN DBPrincipals.[type] = 'R' THEN
				ISNULL(' AUTHORIZATION '+QUOTENAME(Authorizations.name),'')
				WHEN DBPrincipals.[type] = 'A' THEN 
					''	
				WHEN DBPrincipals.[type] NOT IN ('C','K') THEN 
					ISNULL(' FOR LOGIN ' + QUOTENAME(SrvPrincipals.name),' WITHOUT LOGIN') + 
					ISNULL(' WITH DEFAULT_SCHEMA =  '+QUOTENAME(DBPrincipals.default_schema_name),'')
			ELSE ''
			END + ';'
		AS Create_Script
FROM sys.database_principals DBPrincipals
LEFT OUTER JOIN sys.database_principals Authorizations
	ON DBPrincipals.owning_principal_id = Authorizations.principal_id
LEFT OUTER JOIN sys.server_principals SrvPrincipals
	ON DBPrincipals.sid = SrvPrincipals.sid
	AND DBPrincipals.sid NOT IN (0x00, 0x01)
--WHERE DBPrincipals.name LIKE '%MyUserName%'

Here are the database level roles. This one is pretty simple so I’m fairly confident about the drop/create scripts. This script has two commented out WHERE clauses, since I’m just as likely to pull for a given role (db_datareader for example) as a given user.

-- Database Role Members
SELECT Users.name AS UserName, Roles.name AS RoleName,
	'EXEC sp_droprolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Users.name,'''')+';' AS Drop_Script,
	'EXEC sp_addrolemember '+QUOTENAME(Roles.name,'''')+','+QUOTENAME(Users.name,'''')+';' AS Add_Script
FROM sys.database_role_members RoleMembers
JOIN sys.database_principals Users
	ON RoleMembers.member_principal_id = Users.principal_id
JOIN sys.database_principals Roles
	ON RoleMembers.role_principal_id = Roles.principal_id
--WHERE Users.name LIKE '%MyUserName%'
--WHERE Roles.name LIKE '%DBRoleName%'

And last but not least, the database and object level permissions. This includes permissions such as standard GDR (although not revoke obviously) permissions for tables/stored procedures/functions etc and database level permissions such as CONNECT and VIEW DATABASE STATE.

-- Database & object Permissions
SELECT Grantee.name AS Grantee_Name, Grantor.name AS Grantor_Name,
	Permission.class_desc, Permission.permission_name,
	[Objects].name AS ObjectName, Permission.state_desc, 
	'REVOKE ' +
	CASE WHEN Permission.[state]  = 'W' THEN 'GRANT OPTION FOR ' ELSE '' END +
	' ' + Permission.permission_name COLLATE SQL_Latin1_General_CP437_CI_AS + 
		CASE WHEN Permission.major_id <> 0 THEN ' ON ' + QUOTENAME([Objects].name) + ' ' ELSE '' END +
		' FROM ' + QUOTENAME(Grantee.name)  + '; ' AS Revoke_Statement,
	CASE WHEN Permission.[state]  = 'W' THEN 'GRANT' ELSE Permission.state_desc COLLATE SQL_Latin1_General_CP437_CI_AS  END +
		' ' + Permission.permission_name  + 
		CASE WHEN Permission.major_id <> 0 THEN ' ON ' + QUOTENAME([Objects].name) + ' ' ELSE '' END +
		' TO ' + QUOTENAME(Grantee.name)  + ' ' + 
		CASE WHEN Permission.[state]  = 'W' THEN ' WITH GRANT OPTION ' ELSE '' END + 
		' AS '+ QUOTENAME(Grantor.name)+';' AS Grant_Statement
FROM sys.database_permissions Permission
JOIN sys.database_principals Grantee
	ON Permission.grantee_principal_id = Grantee.principal_id
JOIN sys.database_principals Grantor
	ON Permission.grantor_principal_id = Grantor.principal_id
LEFT OUTER JOIN sys.all_objects [Objects]
	ON Permission.major_id = [Objects].object_id
--WHERE Grantee.name LIKE '%MyUserName%'