数据库权限分配问题

anbin0814 2011-10-14 11:36:24
我现在需要查询系统数据库msdb;

SELECT * FROM sysjobs_view 我要查询这个试图;

现在sa账号登录进去查询没问题,可是我不想给用户sa的权限,我新建了一个账号;给public角色,然后在用户映射那里给msdb数据db_owner 和public 当时我去执行上面的sql语句查询是空的,我用sa查询就有记录,


各位大牛这要这么分配权限了,注:sql2008
...全文
407 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
我现在的权限已经有了,可以查询。

就是查询没有结果。
--小F-- 2011-10-14
  • 打赏
  • 举报
回复
sql server2005安全管理之用户、角色、架构 与 权限 
2008-12-04 16:47
--打开数据库
Use databaseName

--创建角色
create role ProgramerRole

--用于创建表 存储过程 视图
grant create table,create procedure,create view to ProgramerRole

--execute用以执行存储过程,alter用以创建、修改存储过程和视图,
--并可以新建立表,但不能修改表,但也可以删除表和对表改名了
grant select,insert,execute,alter on schema::dbo to ProgramerRole

--用于允许用户查看 显示估计的执行计划(081205)
grant showplan to ProgramerRole

--创建登录账号
--create login username with password='password'
--创建数据库用户
create user username for login username

--将用户TestUser添加到TestRole角色中
exec sp_addrolemember 'ProgramerRole','username '

--执行Sql Server Profiler是服务器级权限,所以在master库中授权
USE master;grant alter trace to auto;


注:据库引擎优化顾问必须由具有系统管理员权限的用户进行初始化。在具有系统管理员权限的用户对数据库引擎优化顾问进行初始化之后,任何是 db_owner 固定数据库角色成员的用户,都可以使用数据库引擎优化顾问来优化他们拥有的数据库上的表。
详优化物理数据库设计http://msdn.microsoft.com/zh-cn/library/ms191531(SQL.90).aspx

参考资料:
用户架构分离:http://msdn.microsoft.com/zh-cn/library/ms190387(SQL.90).aspx
数据库架构是一个独立于数据库用户的非重复命名空间。您可以将架构视为对象的容器
主体:http://msdn.microsoft.com/zh-cn/library/ms181127(SQL.90).aspx
是可以请求 SQL Server 资源的实体

Windows 级别的主体

Windows 域登录名
Windows 本地登录名
SQL Server 级的主体

SQL Server 登录名
数据库级的主体

数据库用户
数据库角色
应用程序角色
安全对象:http://msdn.microsoft.com/zh-cn/library/ms190401(SQL.90).aspx
安全对象是 SQL Server 数据库引擎 授权系统控制对其进行访问的资源
安全对象范围有服务器、数据库和架构
安全对象范围:服务器包含以下安全对象:
端点
登录帐户
数据库

安全对象范围:数据库包含以下安全对象:
用户
角色
应用程序角色
程序集
消息类型
路由
服务
远程服务绑定
全文目录
证书
非对称密钥
对称密钥
约定
架构

安全对象范围:架构包含以下安全对象:
类型
XML 架构集合
对象

对象 下面是对象类的成员:
聚合
约束
函数
过程
队列
统计信息
同义词

视图

架构:http://msdn.microsoft.com/zh-cn/library/ms365789(SQL.90).aspx
架构是指包含表、视图、过程等的容器。它位于数据库内部,而数据库位于服务器内部
特定架构中的每个安全对象都必须有唯一的名称。架构中安全对象的完全指定名称包括此安全对象所在的架构的名称。因此,架构也是命名空间

权限:http://msdn.microsoft.com/zh-cn/library/ms190387(SQL.90).aspx
每个 SQL Server 2005 安全对象都有可以授予主体的关联权限

数据库级别的角色:http://msdn.microsoft.com/zh-cn/library/ms189121(SQL.90).aspx
固定数据库角色是在数据库级别定义的,并且存在于每个数据库中。db_owner 和 db_securityadmin 数据库角色的成员可以管理固定数据库角色成员身份;但是,只有 db_owner 数据库的成员可以向 db_owner 固定数据库角色中添加成员。
每个数据库用户都属于 public 数据库角色。当尚未对某个用户授予或拒绝对安全对象的特定权限时,则该用户将继承授予该安全对象的 public 角色的权限
服务器级别角色:http://msdn.microsoft.com/zh-cn/library/ms188659(SQL.90).aspx
固定服务器角色在其作用域内属于服务器范围。固定服务器角色的每个成员都可以向其所属角色添加其他登录名。

GRANT 架构权限:http://msdn.microsoft.com/zh-cn/library/ms187940(SQL.90).aspx

如何配置用户以创建和管理 SQL Server 代理作业 http://msdn.microsoft.com/zh-cn/library/ms187901.aspx

若要配置用户以创建或执行 Microsoft SQL Server 代理作业,必须先将某个现有 SQL Server 登录名或 msdb 角色添加到 msdb 数据库中的下列 SQL Server 代理固定数据库角色之一:SQLAgentUserRole、SQLAgentReaderRole 或 SQLAgentOperatorRole。

默认情况下,这些数据库角色的成员可以创建各自的作业步骤,这些作业步骤不执行其他作业步骤。如果这些非管理用户要运行那些执行其他作业步骤类型(例如,SSIS 包)的作业,它们需要对代理帐户具有访问权限。sysadmin 固定服务器角色的所有成员都有创建、修改和删除代理帐户的权限






为具体的用户设置具体的访问权限 收藏
use 你的库名
go

--新增用户
exec sp_addlogin 'test' --添加登录
exec sp_grantdbaccess N'test' --使其成为当前数据库的合法用户
exec sp_addrolemember N'db_owner', N'test' --授予对自己数据库的所有权限

--这样创建的用户就只能访问自己的数据库,及数据库中包含了guest用户的公共表
go

--删除测试用户
exec sp_revokedbaccess N'test' --移除对数据库的访问权限
exec sp_droplogin N'test' --删除登录


如果在企业管理器中创建的话,就用:

企业管理器--安全性--右键登录--新建登录

常规项
--名称中输入用户名
--身份验证方式根据你的需要选择(如果是使用windows身份验证,则要先在操作系统的用户中新建用户)
--默认设置中,选择你新建的用户要访问的数据库名

服务器角色项
这个里面不要选择任何东西

数据库访问项
勾选你创建的用户需要访问的数据库名
数据库角色中允许,勾选"public","db_ownew"

确定,这样建好的用户与上面语句建立的用户一样
---------------------------------------------------------------------------

最后一步,为具体的用户设置具体的访问权限,这个可以参考下面的最简示例:

--添加只允许访问指定表的用户:
exec sp_addlogin '用户名','密码','默认数据库名'

--添加到数据库
exec sp_grantdbaccess '用户名'

--分配整表权限
GRANT SELECT , INSERT , UPDATE , DELETE ON table1 TO [用户名]

--分配权限到具体的列
GRANT SELECT , UPDATE ON table1(id,AA) TO [用户名]
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
好的,结贴
mingpei0703 2011-10-14
  • 打赏
  • 举报
回复
[Quote=引用 13 楼 ap0405140 的回复:]
参考:
http://book.51cto.com/art/201006/207767.htm
[/Quote]
学习,我先也没理解透!
唐诗三百首 2011-10-14
  • 打赏
  • 举报
回复
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 ap0405140 的回复:]

要加msdb的SQLAgentReaderRole角色才行.
SQL code

-- 用sa帐号登入执行

use msdb

exec sp_addrolemember 'SQLAgentReaderRole', 'test'
[/Quote]

谢谢了,可以了,问下,这个角色有什么用了。开启给用户有什么影响没有!
唐诗三百首 2011-10-14
  • 打赏
  • 举报
回复
要加msdb的SQLAgentReaderRole角色才行.

-- 用sa帐号登入执行

use msdb

exec sp_addrolemember 'SQLAgentReaderRole', 'test'

anbin0814 2011-10-14
  • 打赏
  • 举报
回复




这就是查询的结果,权限是上面那张图,
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
[Quote=引用 8 楼 qianjin036a 的回复:]

重新登录试试.
[/Quote]
也不行,
-晴天 2011-10-14
  • 打赏
  • 举报
回复
重新登录试试.
anbin0814 2011-10-14
  • 打赏
  • 举报
回复

你看,权限给了。

可以查询,就是没结果,用sa查询就有结果
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
[Quote=引用 5 楼 ssp2009 的回复:]

没有权限吧
[/Quote]

有权限。。
快溜 2011-10-14
  • 打赏
  • 举报
回复
没有权限吧
anbin0814 2011-10-14
  • 打赏
  • 举报
回复
[Quote=引用 3 楼 mingpei0703 的回复:]

没结果是正确的,sysjobs_view 这个视图记录的是作业相关信息,没结果说明该数据库没有相关作业记录

用户映射那里给其他任何非系统数据库(系统数据库是默认可以访问的)相应权限,这样应该有该数据库对应的记录了!
[/Quote]

我现在用test的账号建了一个作业,我用sa 查询这个试图有结果,test去查询就没有结果,不知道怎么回事,
mingpei0703 2011-10-14
  • 打赏
  • 举报
回复
没结果是正确的,sysjobs_view 这个视图记录的是作业相关信息,没结果说明该数据库没有相关作业记录

用户映射那里给其他任何非系统数据库(系统数据库是默认可以访问的)相应权限,这样应该有该数据库对应的记录了!

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧