I want a user to log-in to SQL server and only see one database. This user can not be the database owner. I have tried doing DENY VIEW ANY DATABASE TO [username] and i would now like to GRANT to view a single database to [username] is this possible? how do i do this? Thank you.
我希望用户登录到SQL服务器,只看到一个数据库。此用户不能是数据库所有者。我已经尝试过DENY VIEW ANY DATABASE到[用户名],我现在想GRANT查看单个数据库到[用户名]这可能吗?我该怎么做呢?谢谢。
edit: to clarify what i mean, i dont want the user to even see that other databases exist, not just that they cant go into the database.
编辑:澄清我的意思,我不希望用户甚至看到其他数据库存在,而不仅仅是他们不能进入数据库。
thanks!
2 个解决方案
#1
10
I assume you are talking about SQL Server Management Studio here. In short, if you are unable to grant DBO to the user (which is perfectly understandable) then there is NOT currently a way to accomplish what you want.
我假设您在这里谈论SQL Server Management Studio。简而言之,如果您无法向用户授予DBO(这是完全可以理解的),那么目前无法实现您想要的任何方式。
You've gotten as close as you'll get by issuing
你已经尽可能接近发行了
DENY VIEW ANY DATABASE TO <customerlogin>
effectively hiding all databases from the user. Without making the user a DBO there is no way to open view ability back up to only one. This is not a feature that has ever been added per Mike Hotek well known SQL Expert.
有效地隐藏用户的所有数据库。在不使用户成为DBO的情况下,无法将视图功能打开至仅一个。这不是Mike Hotek众所周知的SQL Expert所添加的功能。
You can see a much longer and more detailed discussion regarding this desire on this MSDN thread.
您可以在此MSDN线程上看到有关此愿望的更长更详细的讨论。
http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/a989ca87-660d-41c4-9dac-70b29a83ddfb
#2
1
After you deny view to any database to a specific user:
拒绝查看特定用户的任何数据库后:
DENY VIEW ANY DATABASE TO <customerlogin>
If you make this user a db_owner for the specific database:
如果您将此用户设为特定数据库的db_owner:
USE <customdatabase>
exec SP_changedbowner <customerlogin>
It will only be able to see and use the chosen database.
它只能查看和使用所选的数据库。
More details: https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/
更多细节:https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/
#1
10
I assume you are talking about SQL Server Management Studio here. In short, if you are unable to grant DBO to the user (which is perfectly understandable) then there is NOT currently a way to accomplish what you want.
我假设您在这里谈论SQL Server Management Studio。简而言之,如果您无法向用户授予DBO(这是完全可以理解的),那么目前无法实现您想要的任何方式。
You've gotten as close as you'll get by issuing
你已经尽可能接近发行了
DENY VIEW ANY DATABASE TO <customerlogin>
effectively hiding all databases from the user. Without making the user a DBO there is no way to open view ability back up to only one. This is not a feature that has ever been added per Mike Hotek well known SQL Expert.
有效地隐藏用户的所有数据库。在不使用户成为DBO的情况下,无法将视图功能打开至仅一个。这不是Mike Hotek众所周知的SQL Expert所添加的功能。
You can see a much longer and more detailed discussion regarding this desire on this MSDN thread.
您可以在此MSDN线程上看到有关此愿望的更长更详细的讨论。
http://social.msdn.microsoft.com/Forums/en/sqlsecurity/thread/a989ca87-660d-41c4-9dac-70b29a83ddfb
#2
1
After you deny view to any database to a specific user:
拒绝查看特定用户的任何数据库后:
DENY VIEW ANY DATABASE TO <customerlogin>
If you make this user a db_owner for the specific database:
如果您将此用户设为特定数据库的db_owner:
USE <customdatabase>
exec SP_changedbowner <customerlogin>
It will only be able to see and use the chosen database.
它只能查看和使用所选的数据库。
More details: https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/
更多细节:https://www.mssqltips.com/sqlservertip/2995/how-to-hide-sql-server-user-databases-in-sql-server-management-studio/