如何在SQL Azure数据库中创建新用户?

I am trying to use the following template:

我正在尝试使用以下模板:

-- =================================================
-- Create User as DBO template for SQL Azure 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

I would like to create a user called user1 with a password of 'user1pass'. I connected with my default database 'authentication' and I have a query window open.

我想创建一个用户user1，密码为“user1pass”。我连接了我的默认数据库“身份验证”，并打开了一个查询窗口。

But the template does not make sense for me. For example what's sysname, where do I supply the password and what should I use as the default_schema?

但是这个模板对我来说没有意义。例如，什么是sysname，我在哪里提供密码，我应该使用什么作为default_schema?

The particular user needs to have the power to do everything. But how do I set it up so he can do everything, is that done if I make the user a database owner?

特定的用户需要有做任何事情的能力。但是我如何设置它以便他可以做所有的事情，如果我让用户成为数据库所有者，这就完成了吗?

So far I have tried:

到目前为止，我尝试过:

CREATE USER user1, sysname, user1
    FOR LOGIN user1, sysname, user1
    WITH DEFAULT_SCHEMA = dbo, sysname, dbo
GO

Giving:

给:

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near ','.

Msg 102，第15级，状态1，第1行，语法错误。

and:

和:

CREATE USER user1
    FOR LOGIN user1
    WITH DEFAULT_SCHEMA = dbo
GO

Giving:

给:

Msg 15007, Level 16, State 1, Line 1 'user1' i

Msg 15007, 16级，状态1，第1行" user1 " i

s not a valid login or you do not have permission.

这不是有效的登录名，否则你没有权限。

7 个解决方案

#1

Check out this link for all of the information : https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

查看此链接获取所有信息:https://azure.microsoft.com/en-us/blog/adding-users-to- sql- az-database/

First you need to create a login for SQL Azure, it's syntax is as follows:

首先需要为SQL Azure创建一个登录，它的语法如下:

CREATE LOGIN username WITH password='password';

This command needs to run in master db. Only afterwards can you run commands to create a user in the database. The way SQL Azure or SQL Server works is that there is a login created first at the server level and then it is mapped to a user in every database.

这个命令需要在主db中运行。之后，您才能运行命令在数据库中创建用户。SQL Azure或SQL Server的工作方式是，首先在服务器层创建一个登录，然后将它映射到每个数据库中的用户。

HTH

HTH

#2

Edit - Contained User (v12 and later)

编辑-包含用户(v12及以后)

As of Sql Azure 12, databases will be created as Contained Databases which will allow users to be created directly in your database, without the need for a server login via master.

从Sql Azure 12开始，数据库将被创建为包含的数据库，允许用户直接在数据库中创建，而不需要通过master登录服务器。

CREATE USER [MyUser] WITH PASSWORD = 'Secret';
ALTER ROLE [db_datareader] ADD MEMBER [MyUser];

Note when connecting to the database when using a contained user that you must always specify the database in the connection string.

注意，当使用包含的用户连接到数据库时，必须始终在连接字符串中指定数据库。

Traditional Server Login - Database User (Pre v 12)

传统服务器登录-数据库用户(Pre v12)

Just to add to @Igorek's answer, you can do the following in Sql Server Management Studio:

只需添加@Igorek的答案，您可以在Sql Server Management Studio中执行以下操作:

Create the new Login on the server
In master (via the Available databases drop down in SSMS - this is because USE master doesn't work in Azure):

在master中在服务器上创建新的登录(通过SSMS中可用的数据库下拉——这是因为在Azure中使用master不起作用):

create the login:

创建登录:

CREATE LOGIN username WITH password='password';

Create the new User in the database
Switch to the actual database (again via the available databases drop down, or a new connection)

在数据库切换到实际数据库时创建新用户(再次通过可用的数据库或新连接)

CREATE USER username FROM LOGIN username;

(I've assumed that you want the user and logins to tie up as username, but change if this isn't the case.)

(我假设您希望用户和登录名以用户名绑定，但如果不是这样，就进行更改。)

Now add the user to the relevant security roles

现在将用户添加到相关的安全角色

EXEC sp_addrolemember N'db_owner', N'username'
GO

(Obviously an app user should have less privileges than dbo.)

(显然，应用程序用户应该比dbo拥有更少的权限。)

#3

I followed the answers here but when I tried to connect with my new user, I got an error message stating "The server principal 'newuser' is not able to access the database 'master' under the current security context".

我在这里遵循了答案，但是当我尝试连接我的新用户时，我得到了一条错误消息，表明“服务器主体‘newuser’在当前安全上下文中无法访问数据库‘master’”。

I had to also create a new user in the master table to successfully log in with SSMS.

我还必须在主表中创建一个新用户，才能成功地使用SSMS登录。

USE [master]
GO
CREATE LOGIN [newuser] WITH PASSWORD=N'blahpw'
GO
CREATE USER [newuser] FOR LOGIN [newuser] WITH DEFAULT_SCHEMA=[dbo]
GO

USE [MyDatabase]
CREATE USER newuser FOR LOGIN newuser WITH DEFAULT_SCHEMA = dbo
GO
EXEC sp_addrolemember N'db_owner', N'newuser'
GO

#4

I use the Azure Management console tool of CodePlex, with a very useful GUI, try it. You can save type some code.

我使用CodePlex的Azure管理控制台工具，使用非常有用的GUI，尝试一下。你可以保存一些代码。

#5

You can simply create a contained user in SQL DB V12.

您可以简单地在SQL DB V12中创建一个包含的用户。

Create user containeduser with password = 'Password'

Contained user login is more efficient than login to the database using the login created by master. You can find more details @ http://www.sqlindepth.com/contained-users-in-sql-azure-db-v12/

包含的用户登录比使用master创建的登录更有效。您可以在http://www.sqlindepth.com/containes-users-in -sql- az-db-v12/找到更多的详细信息

#6

I think the templates use the following notation: variable name, variable type, default value.

我认为模板使用以下符号:变量名、变量类型、默认值。

Sysname is a built-in data type which can hold the names of system objects.

Sysname是一种内置的数据类型，可以保存系统对象的名称。

It is limited to 128 Unicode character.

它被限制为128个Unicode字符。

-- same as sysname type
declare @my_sysname nvarchar(128);

#7

I found this link very helpful:
https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/

我发现这个链接非常有用:https://azure.microsoft.com/en-gb/documentation/articles/sql-database-manage-logins/。

It details things like:
- Azure SQL Database subscriber account
- Using Azure Active Directory users to access the database
- Server-level principal accounts (unrestricted access)
- Adding users to the dbmanager database role

它详细描述了以下内容:- Azure SQL数据库订阅器帐户—使用Azure Active Directory用户访问数据库—服务器级主体帐户(无限制访问)—将用户添加到dbmanager数据库角色

I used this and Stuart's answer to do the following:
On the master database (see link as to who has permissions on this):

我使用了这个和Stuart的回答来完成以下操作:在主数据库上(请参阅关于谁对这个有权限的链接):

CREATE LOGIN [MyAdmin] with password='ReallySecurePassword'

And then on the database in question:

然后在数据库中

CREATE USER [MyAdmin] FROM LOGIN [MyAdmin]
ALTER ROLE db_owner ADD MEMBER [MyAdmin]

You can also create users like this, according to the link:

你也可以创建这样的用户，根据链接:

CREATE USER [[e-mail]] FROM EXTERNAL PROVIDER;