SQL Server 如何设置某用户只能访问特定的表、视图、存储过程

一、设置用户只能操作已授权的对象(表、视图、存储过程)

本文将介绍如何只想让某个用户访问数据库特定的表或视图,达到保密的效果,本文演示表的设置过程,存储过程和视图同理。但这个流程设置完用户可以看到所有的数据库名,隐私性不强(点击未授权的数据库提示未授权,无法访问)。

在设置之前我们要先检查public角色的“查看任意数据库”权限是否勾选(默认是勾选的),如果取消勾选下方创建用户看不到任何数据库。

1、登录SSMS之后选择安全性,右键点击登录名。在弹出的右键菜单中选择【新建登录名】选项。

 2、在新建登录名界面中的,常规页面输入新的登录名并设定密码。可以取消勾选用户下次登录时必须更改密码选项。最后设定默认数据库。

3、接下来在用户映射页面中,勾选映射需要配置的数据库STAR_EMES。下方的数据库角色成员身份只勾一个public即可。点击确定按钮完成新用户创建。

 4、展开STAR_EMES数据库需要授权的对象,这里演示Basal_Equipment表,右键》属性》权限》搜索》输入前面创建的用户test2》确定》勾选需要授权的操作类型》确定

 

5、使用前面创建的用户登录数据库,登录后点开STAR_EMES数据库,可以看到只显示Basal_Equipment表。

二、设置用户只能查看并访问特定数据库(不推荐)

ps:此方法,只适用于此数据库只设置一次当前行为,否则下次设置会取代授权的数据库的db_owner权限,设置时先查看数据库的所有者。

1.新建登录用户 

以管理员身份登陆数据库(权限最高的身份如sa),点击安全性->登录名,右键新建登录名,输入登录名和密码,取消强制实施密码策略。 

2.将服务器角色设置为public 

注意:很重要的一步骤,检查【用户映射】中该用户是否有对应的数据库权限,如果有请把勾去掉,否则会影响第4点的操作结果

3.将public服务器角色的属性->取消查看所有数据库的权限 

点击安全性->服务器角色->public,右键属性。点击服务器后如下图取消查看任意数据库的权限

或者执行如下SQL: 
REVOKE VIEW ANY DATABASE TO [public]

4.将待授权的数据库的db_owner指派给刚注册的新用户 

在数据库中新建查询,输入下列sql语句。完成对用户权限的设定,做到了新建用户只能查看和操作特定数据库(除系统数据库外)

Use [WH_R]//WH_R为数据库名称
  go

   EXEC dbo.sp_changedbowner N'user'//user为刚新建的用户

注意:此处的设置不能手动取消,也无法删除上述用户,会有错误提示:

 解决办法,执行sql修改该数据库的所有者

Use [WH_R]//WH_R为数据库名称
  go

   EXEC dbo.sp_changedbowner N'sa'

 

posted @ 2021-06-11 11:38  时光巷尾  阅读(5388)  评论(0编辑  收藏  举报