SQL 创建只能看到一个数据库的用户，只能从中选择？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10218897/
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
Create user that can only SEE one database, and only select from it?
提问by Kyle
We have several databases on SQL server. We would like to create 1 new user that can see database 'c' but can not see the rest of the databases.
我们在 SQL 服务器上有几个数据库。我们想创建 1 个可以看到数据库“c”但看不到其余数据库的新用户。
This user should only be able to select from this database, and nothing else.
I have been googleing and searching for a while now, and the closest I found was to deny view any database, and then make them the database owner.
该用户应该只能从该数据库中进行选择，而不能进行其他选择。
我一直在谷歌搜索和搜索一段时间，我发现最接近的是拒绝查看任何数据库，然后让他们成为数据库所有者。
But I don't think that will work for limiting them to select, unless is there a way I can deny everything except for select on a database owner?
但我认为这不会限制他们选择，除非有什么办法可以拒绝除了数据库所有者的选择之外的所有内容？
Thanks in advance for any help!
在此先感谢您的帮助！
Edit: SQL Server 2008 R2, by the way.
编辑：顺便说一下，SQL Server 2008 R2。
Edit2: Sorry, I was not clear in my original post. I am looking to make it so when they log in they won't even see the names of other databases, not just that they can't access them.
Edit2：对不起，我在原帖中没有说清楚。我希望这样做，当他们登录时，他们甚至不会看到其他数据库的名称，而不仅仅是他们无法访问它们。
Thanks.
谢谢。
采纳答案by Russell Fox
1) Create the user on the server
1）在服务器上创建用户
2) Add the user to the given database
2）将用户添加到给定的数据库
3) Grant read-only access to the database
3) 授予对数据库的只读访问权限
USE [master]
CREATE LOGIN [SomeUserName] WITH PASSWORD=N'someStr0ngP@ssword', DEFAULT_DATABASE=[c], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
USE [c]
CREATE USER [SomeUserName] FOR LOGIN [SomeUserName] WITH DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember N'db_datareader', N'SomeUserName'
回答by Diego
deny is the default behavior when it comes to permission so if you create a user and add it to the db_datareader role on the necessary db, that's the only permission it will have. It wont be able to access the other databases
拒绝是权限方面的默认行为，因此如果您创建一个用户并将其添加到必要数据库上的 db_datareader 角色，那将是它拥有的唯一权限。将无法访问其他数据库
EDIT:
编辑：
use [master]
GO
DENY VIEW ANY DATABASE TO [login]
GO
use [master]
GO
DENY VIEW SERVER STATE TO [login]
GO
that will remove the login's abbility to view the databases. Then go to the one you WANT him to see and make him owner of that DB
这将删除登录名查看数据库的能力。然后去你想让他看到的那个，让他成为那个数据库的所有者
回答by Moumit
Step-1: Create the LogIn
步骤 1：创建登录
Step-2: Remove all DB view permission from the login
步骤 2：从登录中删除所有数据库查看权限
deny view any database to LogInName
Step-3: Give the login authorization of database
Step-3：赋予数据库登录权限
alter authorization on database:: DataBaseName to LogInName
Note: No need to specify username or database name with in single quotation marks
注意：不需要用单引号指定用户名或数据库名称
回答by Vagif
Maybe this will work.
也许这会奏效。
Add user to server and map to the correct data base. Delete user from Server.
将用户添加到服务器并映射到正确的数据库。从服务器中删除用户。
Server will inform you Deleting server users does not delete the data base users associated with this login.
服务器会通知您删除服务器用户不会删除与此登录关联的数据库用户。
Click OK and you will have user with access only to the one data base.
单击“确定”，您将拥有只能访问一个数据库的用户。
