SQL Server授予登录用户db_owner访问数据库的权限

Question

我们有一个测试数据库和一些测试登录帐户，希望通过脚本为它们授予db_owner访问权限。通常情况下，我们需要进入登录名单，右键单击用户名并转到用户映射，选择要关联的数据库，并授予其owner访问权限，然后点击OK。

- user516883

Answer 1

你需要做两件事情，都在目标数据库的上下文中运行（即首先执行USE (database)）：

1. 将该用户添加为数据库的登录名：CREATE USER [LoginName] FOR LOGIN [LoginName]
2. 将该用户添加到角色中：EXEC sp_addrolemember N'db_owner', N'LoginName'

通常情况下，如果你拥有SQL Server Management Studio 2005或更高版本，可以进入操作的UI界面，填写对话框（在本例中是分配用户到数据库和添加角色），然后点击顶部的“脚本”按钮。它会将操作的脚本写入一个新的查询窗口，而不是执行命令。

- Tadmas

---

如果用户已经存在，只需要先映射到数据库，然后运行sp_addrolemember存储过程，这样可以吗？ - user516883

映射到数据库 就是 第一个查询。它假定登录（用户名/密码）已经在服务器上创建。您可以通过查看目标数据库中的 sys.database_principals 来检查是否已经映射。 - Tadmas

可以运行，但根据 MSDN 文档，这个存储过程现在已被弃用。我尝试使用文档建议的 ALTER ROLE 作为替代方法，但正如指出的那样，它无法与内置角色一起使用。有没有什么办法可以在不使用这个存储过程的情况下完成此操作？ - NightOwl888

Answer 2

我想提出另一种解决方案，这可能会对某些人有所帮助...

-- 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

- user1477388

---

太好了！我花了一个小时寻找一种多合一的解决方案，而这正是我需要的。干杯！ - Craig Poole

最好的中的最好！！！ - Abdennour TOUMI

Answer 3

以下是如何使用ALTER ROLE 修改名为MyLogin 的现有服务器登录的方法。

USE MyDatabase
CREATE USER MyLogin FOR LOGIN MyLogin
ALTER ROLE db_owner ADD MEMBER MyLogin
GO

https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-role-transact-sql

- Shaun Luttin

Answer 4

使用 sp_addrolemember

执行 sp_addrolemember 'db_owner', 'MyUser'

- HardCode

---

我尝试了那个，但它显示消息15410，级别11，状态1，过程sp_addrolemember，行75。用户或角色“QA-Tester1”在此数据库中不存在。 - user516883

你必须先添加角色，然后才能将用户添加到该角色中。 - Storm Muller

Answer 5

添加登录，使用默认模式“dbo”添加用户，将用户添加到角色中，并且能够在Azure或本地SQL服务器上无误运行：

--note login and user have same name
-- change the names of: DBNAME, PASSWORDHERE and LOGINNAMEHERE

--1 create login
use master
GO
IF not EXISTS (SELECT * FROM sys.sql_logins WHERE name = N'LOGINNAMEHERE')
BEGIN
    CREATE LOGIN [LOGINNAMEHERE] WITH PASSWORD=N'PASSWORDHERE'
END

--2 on DB create User
use DBNAME
GO

if (Not Exists(select * from sys.sysusers where name = 'LOGINNAMEHERE'))
BEGIN;
    CREATE USER [LOGINNAMEHERE] FOR LOGIN [LOGINNAMEHERE] WITH DEFAULT_SCHEMA=[dbo]
END;
GO

--3 insert role membership
IF ((select ISNULL(is_rolemember('db_owner', 'LOGINNAMEHERE'),0)) <> 1)
BEGIN;
    --EXEC sp_addrolemember 'db_owner', 'LOGINNAMEHERE'
    ALTER ROLE [db_owner] ADD MEMBER [LOGINNAMEHERE];
END;
IF ((select ISNULL(is_rolemember('db_owner', 'LOGINNAMEHERE'),0)) <> 1)
BEGIN;
    PRINT 'user not added to role try sp_addrolemember';
END;

- OzBob