qt中怎么从ftp服务器中读取文件,并存入到sq
发布网友
发布时间：2022-04-09 04:54
共2个回答
懂视网
时间：2022-04-09 09:15
在后面两种情况下，我们添加了SQLAgentReaderRole角色，却看不了作业信息。真的看不了吗？
不用急，我们先看下在GUI界面点击展开作业所调用的语句：
跟踪脚本发现它从msdb.dbo.sysjobs_view视图获取作业信息。
msdb.dbo.sysjobs_view视图定义如下：
CREATE VIEW sysjobs_view AS SELECT jobs.job_id, svr.originating_server, jobs.name, jobs.enabled, jobs.description, jobs.start_step_id, jobs.category_id, jobs.owner_sid, jobs.notify_level_eventlog, jobs.notify_level_email, jobs.notify_level_netsend, jobs.notify_level_page, jobs.notify_email_operator_id, jobs.notify_netsend_operator_id, jobs.notify_page_operator_id, jobs.delete_level, jobs.date_created, jobs.date_modified, jobs.version_number, jobs.originating_server_id, svr.master_server FROM msdb.dbo.sysjobs as jobs JOIN msdb.dbo.sysoriginatingservers_view as svr ON jobs.originating_server_id = svr.originating_server_id --LEFT JOIN msdb.dbo.sysjobservers js ON jobs.job_id = js.job_id WHERE (owner_sid = SUSER_SID()) OR (ISNULL(IS_SRVROLEMEMBER(N‘sysadmin‘), 0) = 1) OR (ISNULL(IS_MEMBER(N‘SQLAgentReaderRole‘), 0) = 1) OR ( (ISNULL(IS_MEMBER(N‘TargetServersRole‘), 0) = 1) AND (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js WHERE js.server_id <> 0 AND js.job_id = jobs.job_id))) -- filter out local jobsView Code
注意WHERE条件：当用户是sysadmin或SQLAgentReaderRole或TargetServersRole角色成员时条件永远成立，返回所有作业；否则仅返回所有者是当前用户的作业。
检查角色默认授予了哪些权限：
granteename objectname columnname class_desc permission_name state_desc SQLAgentOperatorRole sp_enum_login_for_proxy NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentOperatorRole sp_help_alert NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentOperatorRole sp_help_notification NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentOperatorRole sp_help_targetserver NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentOperatorRole sp_purge_jobhistory NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentOperatorRole sysalerts NULL OBJECT_OR_COLUMN SELECT GRANT SQLAgentOperatorRole sysnotifications NULL OBJECT_OR_COLUMN SELECT GRANT SQLAgentOperatorRole sysoperators NULL OBJECT_OR_COLUMN SELECT GRANT SQLAgentUserRole sp_add_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_add_jobschedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_add_jobserver NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_add_jobstep NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_add_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_addtask NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_attach_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_check_for_owned_jobs NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_check_for_owned_jobsteps NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_jobschedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_jobserver NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_jobstep NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_jobsteplog NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_delete_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_detach_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_droptask NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_enum_sqlagent_subsystems NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_get_job_alerts NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_get_jobstep_db_username NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_get_sqlagent_properties NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_category NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobactivity NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobcount NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobhistory NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobhistory_full NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobhistory_sem NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobhistory_summary NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobs_in_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobschedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobserver NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobstep NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_jobsteplog NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_operator NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_proxy NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_help_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_maintplan_subplans_by_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_notify_operator NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_start_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_stop_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_uniquetaskname NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_update_job NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_update_jobschedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_update_jobstep NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole sp_update_schedule NULL OBJECT_OR_COLUMN EXECUTE GRANT SQLAgentUserRole syscategories NULL OBJECT_OR_COLUMN SELECT GRANT SQLAgentUserRole sysjobs_view NULL OBJECT_OR_COLUMN SELECT GRANT SQLAgentUserRole sysschedules_localserver_view NULL OBJECT_OR_COLUMN SELECT GRANTView Code
权限分配来看，SQLAgentUserRole分配了基础权限(视图、存储过程中有对角色的判定限制操作)；SQLAgentReaderRole没有额外权限；SQLAgentOperatorRole额外有警报、通告、操作员权限。
SQLAgentReaderRole、SQLAgentOperatorRole是SQLAgentUserRole的角色成员，SQLAgentOperatorRole是SQLAgentReaderRole的角色成员。
SQLAgentReaderRole对视图msdb.dbo.sysjobs_view有SELECT权限(继承SQLAgentUserRole的权限)，GUI界面操作时从视图获取数据。所以添加到此角色后，展开作业就能返回所有作业。
我们用语句查询作业时，习惯直接从msdb.dbo.sysjobs这类表入手。但SQLAgentUserRole角色并没有对此类表有SELECT权限，因此常规语句会报拒绝对对象的SELECT权限。
出现问题的根源是，自己没有深入了解本质，自己以为SQLAgentUserRole角色直接在表上有权限，未曾深入查看它的权限集中在对存储过程的执行和视图的查询。
很多时候我们使用习以为常且单方面是正确的方式操作，最终却没得到预期的结果，这时我们就要检查操作的是否是同一对象。
添加到SQLAgentReaderRole角色后报拒绝SELECT权限
标签：
热心网友
时间：2022-04-09 06:23
