当您在 SQL Server 中创建新登录时，它默认选择 db_owner [英] When you create a new login in SQL Server it selects db_owner by default

问题描述

我在 SQL Server 2008 R2 中创建新 SQL 用户时遇到了一些问题.当我使用 SQL Server Management Studio 时，它默认检查 db_owner 角色成员资格.我只想创建一个具有只读访问权限的新 sql 用户.即使使用以下原始 SQL，它仍会创建具有 db_owner 级别权限的用户.

I am having some trouble creating a new SQL user in SQL Server 2008 R2. When I use SQL Server Management Studio it checks db_owner role membership by default. I just want to create a new sql user with read only access. Even with the following raw SQL it still creates the user with db_owner level permission.

<!-- language: lang-sql -->
CREATE LOGIN readonlyuser
WITH PASSWORD = '12345',CHECK_POLICY = OFF, DEFAULT_DATABASE=mydatabase
GO
USE mydatabase
GO
CREATE USER readonlyuser FOR LOGIN readonlyuser
GO
EXEC sp_addrolemember 'db_datareader', 'readonlyuser'

现在，如果我使用新创建的用户登录 SQL Server Management Studio，我基本上可以访问任何表并以任何我想要的方式修改任何数据.这正是我不想做的.我只想被读取数据，不想修改任何数据

Now if I log on to SQL Server Management Studio with newly created user I can basically access any table and modify any data any way that I want. This is exactly what I not want to do. I want only to be read data and not to modify any data

奇怪的是，如果我查看数据库的角色，readonlyuser 位于 db_datareader 内，而不是 db_owner.

Strange thing is if I look at the roles for database the readonlyuser is inside db_datareader and not in db_owner.

那么为什么 SQL 会创建这个具有 db_owner 级别权限的用户，从而允许用户修改数据?

So why is SQL creating this user with db_owner level permissions, thus allowing the user to modify data?

2013/08/07 更新

这似乎只发生在一个特定的数据库中.我创建了全新的数据库并创建了一堆表，然后运行上面相同的脚本，它工作得很好.但是，如果我尝试使用需要进行此更改的实际数据库，它就不会那样工作.它创建了用户并授予了太多权限.

This seems to be happening with just one specific database. I created brand new database and created bunch of tables and then ran the same script above and it is working perfectly fine. But if i try with the actual database where i need this change, it doesn't work like that. It created the user and gave way too much permission.

有什么我可以检查的数据库吗?请注意，特定的数据库不是我设计的.它来自第 3 方供应商.所以不确定他们可能做了哪些修改.

Is there anything that i can check on that database? Please note that specific database was not designed by me. It is from a 3rd party vendor. So not sure exactly what modifications they might have done.

非常感谢任何帮助.

推荐答案

我遇到了同样的问题.

解决办法:sp_changedbowner.那修复了它.(不知何故，所有者已损坏)

The solution: sp_changedbowner. That fixed it. (Somehow the owner was corrupted)

这篇关于当您在 SQL Server 中创建新登录时，它默认选择 db_owner的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！