使用 SQL 脚本创建用户、映射到数据库并为该数据库分配角色
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4603155/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating a user, mapping to a database and assigning roles to that database using SQL scripts
提问by NLV
I've done this before but not through scripts. I've to create a new user in SQL server (SQL Authentication) and map the user to a database and assign the roles for the user to that database using SQL scripts.
我以前做过这个，但不是通过脚本。我必须在 SQL 服务器（SQL 身份验证）中创建一个新用户，并将用户映射到数据库，并使用 SQL 脚本将用户的角色分配给该数据库。
How can I do it?
我该怎么做？
回答by Matt
Try:
尝试：
CREATE USER [Username] FOR LOGIN [Domain\Username]
EXEC sp_addrolemember N'DatabaseRole', N'Username'
Obviously changing Username, and Domain\Username and the database role to being the username that you wish to grant rights to and the level of access, such as 'db_datareader'
显然将用户名、域\用户名和数据库角色更改为您希望授予权限的用户名和访问级别，例如“db_datareader”
回答by Fin McCarthy
The above solution works for me. However if the Username doesnt yet exist in that database as a USER, that username will not be fully mapped to the database role.
上述解决方案对我有用。但是，如果该用户名在该数据库中尚不作为 USER 存在，则该用户名将不会完全映射到数据库角色。
In this situation I first add the user:
在这种情况下，我首先添加用户：
USE databasename
CREATE USER [DOMAIN\svce_name] FOR LOGIN [DOMAIN\svce_name] WITH DEFAULT_SCHEMA=[dbo]
GO
Then I add the role:
然后我添加角色：
USE databasename
EXEC sp_addrolemember N'db_ssisoperator', N'DOMAIN\svce_name'
GO
