February 27, 2011 at 1:09 pm
while running the ssis package with following querys
SELECT convert(varchar(120),serverproperty('servername'))'Servername',sqltext.TEXT,database_id,user_id,r.session_id,s.program_name,r.status,r.command,r.reads,r.writes,r.row_count,nest_level,((r.total_elapsed_time/1000)/60) 'ETM',open_transaction_count ,r.cpu_time,scheduler_id,granted_query_memory,executing_managed_code,blocking_session_id,wait_type,((wait_time/1000)/60)'WaitTime(Min)',wait_resource ,getdate() 'Date'
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext ,sys.dm_exec_sessions s
where r.session_id=s.session_id
and r.session_id <> @@spid
Its showing the error message "The user does not have permission to perform this action." but user has dbo owner permission on the master database.
Can anybody let me know what could be the problem and what permission it need to run ?
February 27, 2011 at 2:35 pm
You rproblem is in this section of the code
sudhakara (2/27/2011)CROSS APPLY sys.dm_exec_sql_text(sql_handle)
To use the dm_exec_sql_text function you need VIEW SERVER STATE permissions. See http://msdn.microsoft.com/en-us/library/ms181929.aspx?ppud=4
You can fix it by making the user a member of the "serveradmin" Server Role.
Cheers
Leo
Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.
February 27, 2011 at 3:01 pm
Thanks.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply