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

2021-12-06 00:00:00 azure-sql-database azure sql-server

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

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

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

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.

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

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?

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

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

给予:

消息 102，级别 15，状态 1，第 1 行 ',' 附近的语法不正确.

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

和:

CREATE USER user1
    FOR LOGIN user1
    WITH DEFAULT_SCHEMA = dbo
GO

给予:

消息 15007，级别 16，状态 1，第 1 行 'user1' i

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

不是有效登录名或您没有权限.

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

推荐答案

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

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

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

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

CREATE LOGIN username WITH password='password';

此命令需要在 master db 中运行.只有在此之后，您才能运行命令在数据库中创建用户.SQL Azure 或 SQL Server 的工作方式是首先在服务器级别创建一个登录名，然后将其映射到每个数据库中的用户.

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.

HTH

相关文章