다음을 통해 공유

Windows Azure SQL Database SQL Authentication

  • 아티클

Windows Azure SQL Database supports only SQL Server authentication. Windows authentication (integrated security) is not supported. You must provide credentials every time when you connect to SQL Database.
... now supports both SQL and AAD authentication

The SQL Database provisioning process gives you a SQL Database server, a master
database, and a server-level principal login of your SQL Database server. This
server-level principal is similar to the sa login in SQL Server. Additional SQL Databases and logins can then be created in the
server, as needed.

You can use Transact-SQL to administrate additional users and logins using either Database Manager for Windows Azure SQL Database or SQL Server Management Studio 2008 R2. Both tools will list the users and logins associated with the databases; however, at this time it does not provide a graphical user interface for creating the users and logins.

Note: The current version of SQL Database supports only one Account Administrator and one Service administrator account.

In this Article

The Master Database

A SQL Database Server is a logical group of databases. Databases associated with one server Azure server may spread on different physical computers at the
Microsoft data center. You must perform server-level administration for all of the database on the master database. For example, the master database keeps track of the logins. You must connect to the master database to create and drop logins.

Creating Logins

Logins are server wide login and password pairs, where the login has the samepassword across all databases. You must be connected to the master database on SQL Database with the administrative login to execute the CREATE LOGIN command.
Some of the common SQL Server logins can be used like sa, Admin, root. For a complete list, see Managing Databases and Logins in SQL Database at http://msdn.microsoft.com/en-us/library/ee336235.aspx.

--create a login named "login1"
CREATE LOGIN login1 WITH password='pass@word1';

--list logins. You must run this statement separately from the CREATE LOGIN statement
SELECT * FROM sys.sql_logins;

Note: SQL Database does not allow the USE Transact-SQL statement, which means that you cannot create a single script to execute both the CREATE LOGIN and CREATE USER statements, since those statements need to be executed on different databases.

Creating Users

Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database.

--create a user named "user1"
CREATE USER user1 FROM LOGIN login1;

Configuring User Permissions

Just creating the user does not give them permissions to the database. You have to grant them access. For a full list of roles, see Database-level roles

--give user1 read-only permissions to the database via the db-datareader role
EXEC sp_addrolemember 'db_datareader', 'user1';

Deleting Users and Logins

Fortunately, SQL Server Management Studio 2008 R2 does allow you to delete users and logins. To do this traverse the Object Explorer tree and find the Security node, right click on the user or login and choose Delete. You can also use the DROP LOGIN and the DROP USER statements.

See Also