Introduction

By default, when you create an Azure SQL Server, a login with administrative privileges is created. How can we create Azure SQL Data Warehouse (ASDW) logins with lower privileges?,  which roles are available in ASDW?, how can I verify the roles, users and logins created in ASDW?

In this new chapter, we will learn how to:

• Create a new SQL login
• Verify that the user was created using SSMS and system views
• Create a database user linked to a login
• Verify if the user was created successfully using SSMS system views
• Contained users in Azure
• Special Azure Database Roles
• Create roles, users in ASDW and how to verify the creation. 

We will talk about special database roles, like the dbmanager role, and show how to assign users to logins, how to create contained users, and how to assign users to different roles.

Requirements

1. SQL Server Management Studio (SSMS)
2. An Azure SQL Server Subscription.
3. An ASDW database with the firewall rule enabled to run queries from SSMS.

Getting started

Connect to your ASDW using SSMS:

Create a new SQL login

To create a new login, you can do it using the following code in the master database:

CREATE LOGIN securityadmin
WITH PASSWORD = 'Azuresqlcentralpwdsecret!#' 
GO

The code creates a login named securityadmin with the password specified.

Verify that the user was created using SSMS and system views

You can verify that the login securityadmin was created successfully in SSMS in the security folder:

You can also verify that the login was created by doing a select statement to the table sys.sql_logins:

select * from [sys].[sql_logins]

As you can see the securityadmin was created and the principal id is 4. The type is S which is a SQL login . The account is not disabled (is_disabled is equal to 0) and you also have the creation data. Note that the sp_helplogins stored procedure and other stored procedures related to get user, roles and login information are only available in SQL Server on-premises and not in ASDW.

In Azure SQL, you do not have the server roles that you have in SQL Server on-premises:

Create a database user linked to a login

The login allows you to have access to several databases with the same login. The following example shows how to link a new database user created with the login:

CREATE USER securityadmin FROM LOGIN securityadmin; 

Verify if the user was created successfully using SSMS system views

If the database user was created successfully you will be able to see it in the Database>Security>User folder:

Select * from sys.database_principals

You will be able to see the new database user created:

Contained Users in Azure

If you do not want to have a login, you can create a user with access to one single database. This is a contained user. To create a database user that logins directly to the database use the following code:

CREATE USER containeduser
 WITH PASSWORD = 'Containedpwd778&%';

The codes create a database user that con logins directly to the database.

To connect a contained user, you need to specify the database when you connect in SSMS. In the connect window, select Options:

In the connection Properties tab, specify the name of the database where you want to connect with the credentials:

Contained users can be migrated from one database to another. The logins instead are harder to migrate because they are in the master database.

Special Azure database roles

In Azure SQL and ASDW, you have the same database roles than SQL Server on-premises except two roles that are exclusive Azure:

1. The loginmanager
2. The dbmanager

The loginmanager role is a role in the master database that allows to create logins in the master database. To add a user to the loginmanager role you need to run the following code in the master database:

ALTER ROLE loginmanager ADD MEMBER securityadmin; 

The dbmanager is another role that can be assigned in the master database. This role allows to create new databases. If you create a database with this role, you will be the database owner. To add someone to this role, run the following code:

ALTER ROLE dbmanager ADD MEMBER securityadmin; 

In Azure is not so easy to verify if a user is member of a database role. To have a list of roles and users, you can run the following query:

select dp.name as db_role, dp2.name as db_user
from sys.database_role_members drm
  join sys.database_principals dp on (drm.role_principal_id = dp.principal_id)
  join sys.database_principals dp2 on (drm.member_principal_id = dp2.principal_id)
order by dp.name

The result of the query will be the database role and the user names. You can verify in this query if a user is member of a specified role:

Conclusion

In this article, we learn how to create logins, contained users, add users to roles, we learn some Azure database roles and we checked some system table to verify the creation of logins, users and role membership.

References

• Controlling and granting database access
• Database-Level Roles
• Server-Level Roles