165 C H A P T E R 8 | Improved Azure SQL Database
principal account for your SQL Database server. This login is analogous to the system administrator
(sa) login for an on-premises instance of SQL Server. It manages all server-level and database-level
security and has permission to create other accounts that can manage logins and databases in SQL
Database.
Server-level roles
Although you can use the server-level principal account to manage server-level security, you also have
the option to use sp_addrolemember to assign logins to the following SQL Database security roles:
loginmanager This role grants permission to create logins in SQL Database, much like you use
the securityadmin role in on-premises SQL Server. Example 8-1 shows how to assign this role to a
login.
dbmanager This role grants permission to create databases by executing the CREATE
DATABASE command in the master database. It is similar to the dbcreator role in on-premises SQL
Server.
Example 8-1: Creating a SQL Database login and assigning a role
-- connect to the master database
CREATE LOGIN adminlogin WITH password='';
CREATE USER adminuser FROM LOGIN adminlogin;
EXEC sp_addrolemember 'loginmanager', 'adminuser';
Important Be careful to assign these roles only to a limited set of administrators and not to
general user accounts. Because these roles scope to the server, a user with permissions in the
master database can access data in any SQL Database on the same server by using server-scoped
DMVs.
Database-level roles
The built-in security roles at the database level are similar to on-premises SQL Server security roles. To
implement database-level security, you can use the same fixed database roles, such as db_datareader
or db_datawriter. Optionally, you can create custom roles for your application when you need explicit
permissions for selected database objects.
Important As a security best practice, you should always use role-based security to manage
database access.
Contained database users
SQL Database supports contained database users so that you can isolate a user account to a single
database instead of creating a server login to which you must then grant database-level permissions.
By setting up a contained database user, as shown in Example 8-2, you can move a database between
servers and consolidate the user identity and permissions within the individual database.
Example 8-2: Creating a contained database user
-- connect to the database
CREATE USER userA WITH password='';
