SQL Server 授予登录名（用户）db_owner 访问数据库的权限
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7232559/
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
SQL Server giving logins(users) db_owner access to database
提问by user516883
We have a test database and some test logins that we would like to give db_owner access to through a script. Usually we would have to go into logins and right click on the username and go to user mapping and select the database to associate it with and give it owner access and click OK.
我们有一个测试数据库和一些测试登录，我们希望通过脚本授予 db_owner 访问权限。通常我们必须进入登录并右键单击用户名并转到用户映射并选择要关联的数据库并授予其所有者访问权限并单击OK。
回答by Tadmas
You need to do two things, both running in the context of the target database (i.e., execute USE (database)first):
您需要做两件事，都在目标数据库的上下文中运行（即USE (database)先执行）：
- Add that user as a login to the database:
CREATE USER [LoginName] FOR LOGIN [LoginName] - Add that user to the role:
EXEC sp_addrolemember N'db_owner', N'LoginName'
- 将该用户添加为数据库的登录名：
CREATE USER [LoginName] FOR LOGIN [LoginName] - 将该用户添加到角色：
EXEC sp_addrolemember N'db_owner', N'LoginName'
In general, if you have SQL Server Management Studio 2005 or higher, you can go into the UI for an operation, fill out the dialog box (in this case, assigning the user to the database & adding roles), and then click the "Script" button at the top. Instead of executing the command, it will write a script for the action to a new query window.
一般来说，如果你有 SQL Server Management Studio 2005 或更高版本，你可以进入 UI 进行操作，填写对话框（在这种情况下，将用户分配到数据库并添加角色），然后单击“脚本”按钮位于顶部。它不会执行命令，而是将操作的脚本写入新的查询窗口。
回答by HardCode
Use sp_addrolemember
EXEC sp_addrolemember 'db_owner', 'MyUser'
EXEC sp_addrolemember 'db_owner', 'MyUser'
回答by user1477388
I'd like to propose another solution which may help someone...
我想提出另一种可能对某人有所帮助的解决方案......
-- create the user on the master database
USE [master]
GO
CREATE LOGIN [MyUserName] WITH PASSWORD=N'MyPassword'
CREATE USER [MyUserName] FOR LOGIN [MyUserName]
GO
-- create the user on the target database for the login
USE [MyDatabaseName]
GO
CREATE USER [MyUserName] FOR LOGIN [MyUserName]
GO
-- add the user to the desired role
USE [MyDatabaseName]
GO
ALTER ROLE [db_owner] ADD MEMBER [MyUserName]
GO
回答by Shaun Luttin
Here is how to use ALTER ROLEwith an existing server login named MyLogin.
以下是如何使用ALTER ROLE名为 的现有服务器登录名MyLogin。
USE MyDatabase
CREATE USER MyLogin FOR LOGIN MyLogin
ALTER ROLE db_owner ADD MEMBER MyLogin
GO
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql
