Managing Databases and Logins in Windows Azure SQL Database

Security administration in Windows Azure SQL Database is similar to security administration for an on-premise instance of SQL Server. Managing security at the database-level is almost identical, with differences only in the parameters available. Because SQL Databases can scale to one or more physical computers, Windows Azure SQL Database uses a different strategy for server-level administration. The following table summarizes how security administration for an on-premise SQL Server is different than in Windows Azure SQL Database.

Your SQL Database server is an abstraction that defines a grouping of databases. Databases associated with your SQL Database server may reside on separate physical computers at the Microsoft data center. Perform server-level administration for all of them by using a single database named master.

The master database keeps track of logins, and which logins have permission to create databases or other logins. You must be connected to the master database whenever you CREATE, ALTER, or DROP logins or databases. The master database also has the sys.sql_logins and sys.databases views that you can use to view logins and databases, respectively.

Note
The USE command is not supported for switching between databases. Establish a connection directly to the target database.

You can manage database-level security for users and objects in Windows Azure SQL Database the same way you do for an on-premise instance of SQL Server. There are differences only in the parameters available to the corresponding commands. For more information, see Transact-SQL Reference (Windows Azure SQL Database).

Manage logins with the server-level principal login by connecting to the master database. You can use the CREATE LOGIN, ALTER LOGIN, or DROP LOGIN statements. The following example creates a login named login1:

-- first, connect to the master database
CREATE LOGIN login1 WITH password='<ProvidePassword>';

Note
You must use a strong password when creating a login. For more information, see Strong Passwords.

Using New Logins

In order for logins other than the server-level principal to manage server-level security, Windows Azure SQL Database offers two security roles: loginmanager, for creating logins and dbmanager for creating databases. Only users in the master database can be added to these database roles.

Note
To create logins or databases, you must be connected to the master database (which is a logical representation of master).

The loginmanager Role

The dbmanager Role

How to Assign SQL Database Server-Level Roles

To create a login and associated user that can create databases or other logins, perform the following steps:

1. Connect to the master database using the credentials of the server-level principal login (created by the provisioning process) or the credentials of an existing member of the loginmanager database role.
   
   
2. Create a login using the CREATE LOGIN command. For more information, see CREATE LOGIN (Windows Azure SQL Database).
   
   
3. Create a new user for that login in the master database using the CREATE USER command. For more information, see CREATE USER (Windows Azure SQL Database).
   
   
4. Use the stored procedure sp_addrolememeber to add new user to the dbmanager database role, the loginmanager database role, or both.
   
   

The following code example shows how to create a login, login1, and a corresponding database user named login1User that is able to create databases or other logins while connected to the master database:

Copy

-- first, connect to the master database
CREATE LOGIN login1 WITH password='<ProvidePassword>';
CREATE USER login1User FROM LOGIN login1;
EXEC sp_addrolemember 'dbmanager', 'login1User';
EXEC sp_addrolemember 'loginmanager', 'login1User';

Note
You must use a strong password when creating a login. For more information, see Strong Passwords.

All logins must be created in the master database. After a login has been created, you can create a user account in another database for that login. Windows Azure SQL Database also supports database roles in the same way that an on-premise instance of SQL Server does.

To create a user account in another database, assuming you have not created a login or a database, perform the following steps:

1. Connect to the master database (with a login having the loginmanager and dbmanager roles).
   
   
2. Create a new login using the CREATE LOGIN command. For more information, see CREATE LOGIN (Windows Azure SQL Database). Windows Authentication is not supported.
   
   
3. Create a new database using the CREATE DATABASE command. For more information, see CREATE DATABASE (Windows Azure SQL Database).
   
   
4. Establish a connection to the new database (with the login that created the database).
   
   
5. Create a new user on the new database using the CREATE USER command. For more information, see CREATE USER (Windows Azure SQL Database).
   
   

The following code example shows how to create a login named login1 and a database named database1:

-- first, connect to the master database
CREATE LOGIN login1 WITH password='<ProvidePassword>';
CREATE DATABASE database1;

Note
You must use a strong password when creating a login. For more information, see Strong Passwords.

This next example shows how to create a database user named login1User in the database database1 that corresponds to the login login1:

-- Establish a new connection to the database1 database
CREATE USER login1User FROM LOGIN login1;

This database-level permission model in Windows Azure SQL Database is same as an on-premise instance of SQL Server. For information, see the following topics in SQL Server Books Online references.

Identity and Access Control (Database Engine)

Managing Logins, Users, and Schemas How-to Topics

Lesson 2: Configuring Permissions on Database Objects

Note
Security-related Transact-SQL statements in Windows Azure SQL Database may differ slightly in the parameters that are available. For more information, see Transact-SQL Reference (Windows Azure SQL Database).

To view logins and databases on your SQL Database server, use the master database's sys.sql_logins and sys.databases views, respectively. The following example shows how to display a list of all the logins and databases on your SQL Database server.

-- first, connect to the master database
SELECT * FROM sys.sql_logins;
SELECT * FROM sys.databases; 

Concepts