sql server登录名只能看到自己拥有权限的库

目录

• #背景
• 【1】SSMS操作
  • 1、先给不同的服务商创建不同的登录名（如下以一个服务商为例）
  • 2.将登录名加入到public服务器角色中
  • 3.将public服务器角色的属性——>安全对象中取消查看所有数据库的权限
  • 4.将待授权的数据库的dbowner指派给该用户
  •  【最终效果】
  • 【如何取消？】
• 【2】T-SQL实现
• 【3】为什么我一直不能成功？ 
• 【4】缺陷

 

---

Sqlserver中 登录用户只能看到自己拥有权限的库

问题背景：公司的一台数据库服务器上放在多个数据库,每个数据库都使用不同的登录名称，但在将项目文件发布到Ftp时,有些Ftp的信息是在客户那边的

一旦客户那边使用配置文件中的数据库信息连接到数据库他就能够看到服务器上所有的数据库，虽然它不能访问其他的数据库但还是有安全隐患的

现在我需要的是指定的登录用户只能看到并访问它自己的数据库而不能看到其他的数据库，于是在网络上找到解决方案

正文

回到顶部

#背景

　　SQL Server实例上有多个服务商的数据库，每个数据库要由各自的服务商进行维护，

　　为了限定不同服务商商的维护人员只能访问自己的数据库，且不能看到其他服务商的数据库，现需要给各个服务商商限定权限，让他们登录SQL Server只能看到授权的数据库而无法看到其他数据库。

回到顶部

【1】SSMS操作

1、先给不同的服务商创建不同的登录名（如下以一个服务商为例）

　　

2.将登录名加入到public服务器角色中

反正默认也会加入public的

　　

 

 

 

3.将public服务器角色的属性——>安全对象中取消查看所有数据库的权限

sql server 2005 及相关低版本：

 

 　　

 

把勾去掉；

sql server高版本：

　　

把勾去掉，上图已经把勾去掉了；

或者执行如下SQL：

REVOKE VIEW ANY DATABASE TO [public]

 

4.将待授权的数据库的dbowner指派给该用户

执行下面任意一种方法sql 即可

--方法（1）
use master;
ALTER AUTHORIZATION ON DATABASE::Test2 TO test22

--方法（2）
use test2
EXEC dbo.sp_changedbowner N'test22'

或使用SSMS操作：

　　

 

 

运行上面代码命令，或上面SSMS修改操作 之后的效果图

 

　　

 

 【最终效果】

　　

 

 

 

【如何取消？】

　　注意：此处的设置不能手动取消，会有错误提示：

　　　　

　　解决办法，执行sql修改该数据库的所有者，执行下面代码任意一种方法即可

--方法（1）
use master;
ALTER AUTHORIZATION ON DATABASE::Test2 TO sa

--方法（2）
use test2
EXEC dbo.sp_changedbowner N'sa'

 --方法（3）

　　

回到顶部

【2】T-SQL实现

USE [master]
GO
-- create login name
CREATE LOGIN [test22] WITH PASSWORD=N'123456',check_policy=off
GO

-- removke public see databases 
revoke view any database to public;

go

-- change login:test22  mapping database user:dbo 
ALTER AUTHORIZATION ON DATABASE::Test TO test22
go

　　

 

 

回到顶部

【3】为什么我一直不能成功？ 

（1）必须要先  revoke view any database to public 之后，才给登录名创建对应数据库的user权限;

如：

--方法（1）
use master;
ALTER AUTHORIZATION ON DATABASE::Test2 TO test22

--方法（2）
use test2
EXEC dbo.sp_changedbowner N'test22'

否则，就算授权后，取消了，再进行上面的代码操作，依然不会生效； 

 

回到顶部

【4】缺陷

这种方式，很明显的缺陷就是，不能做更详细、具体的权限控制，直接就给了db_owner 角色，这个角色那可是对该库 可以为所欲为的啊！

这种办法，用完之后
其他多库的非管理员账户看不到任何库，但可以操作有权限的库，但连接上去不会显示库名。可以用 select * from maste.sys.databases 来获取库名操作，有得有失，这个做的不太好。

不知道高版本是否有改正，这方面mysql倒是好很多

参考：

使SQL用户只能看到自己拥有权限的库

MSDN VIEW ANY DATABASE 权限 

MSDN ALTER AUTHORIZATION (Transact-SQL) 

MSDN sp_addlogin (Transact-SQL)

SSMS参考：https://www.cnblogs.com/willingtolove/p/9122738.html 

来自为知笔记(Wiz)

 

分类: sql server 管理

 

标签: Sql Server, SQL Server 权限