October 22, 2008 at 7:28 am
Is there anyway to provide either the activity monitor or "read" access to what is happening on a sqlserver (2005) without being a sys admin?
October 22, 2008 at 8:05 am
This is what I see from BOL
http://msdn.microsoft.com/en-us/library/cc879320.aspx
To view the Activity Monitor in SQL Server 2005 and SQL Server 2008, a user must have VIEW SERVER STATE permission.
To view the Activity Monitor on a SQL Server 2000 server, a user must have SELECT permission to the sysprocesses and syslocks tables in the master database. Permission to view these tables is granted by default to the public database role.
October 22, 2008 at 8:09 am
If you create a user with the server role 'process_admin', this user will be able to use the activity monitor. HOWEVER...
It will also be able to kill of processe.
Hope this helps. 😉
Adam Zacks-------------------------------------------Be Nice, Or Leave
October 23, 2008 at 3:02 am
At my place we have a specific Windows group and a set of authorities that provides 'read-only' type access to all server objects. As Database Architect I use these rights to find out what is going on in some of our systems, without any risk that I may accidentally change anything.
The rights are:
1) Permissions (Open Server Properties then select Permissions)
ALTER TRACE
CONNECT SQL
VIEW ANY DATABASE
VIEW ANY DEFINITION
VIEW SERVER STATE
2) Server roles
Public
3) Database Roles
public (all databases)
db_datareader (all databases)
SQLAgentUserRole (msdb only)
The ALTER TRACE authority is only needed if you want to allow members of the Windows group to run traces.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
October 23, 2008 at 10:02 am
csales (10/22/2008)
Is there anyway to provide either the activity monitor or "read" access to what is happening on a sqlserver (2005) without being a sys admin?
The best way I found is the create a report through reporting services. This way anyone can view what is happening on your server without having to create new roles and security groups. Here's the script I used which I cobbled together from other scripts online. I then set my report to refresh every 60 seconds.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
SpID = s.session_id,
Status = UPPER(COALESCE
(
r.status,
ot.task_state,
s.status,
'')),
[Login] = s.nt_user_name,
DATEDIFF(mi, r.start_time, GETDATE()) AS [RunTime (min)],
BlockBy = COALESCE(RTRIM
(
NULLIF(r.blocking_session_id, 0)),
'.'
),
[CPU] = COALESCE
(
NULLIF(r.cpu_time, 0),
--NULLIF(s.cpu_time, 0),
--NULLIF(s.total_scheduled_time, 0),
--NULLIF(tt.CPU_Time, 0),
0
),
DiskIO = COALESCE
(
NULLIF(r.reads + r.writes, 0),
--NULLIF(s.reads + s.writes, 0),
--NULLIF(c.num_reads + c.num_writes, 0),
0
),
Command = COALESCE
(
r.Command,
r.wait_type,
wt.wait_type,
r.last_wait_type,
''
),
r.wait_type as WaitType,
BatchStart = convert(char(20),COALESCE
(
r.start_time,
''
)),
[Host] = COALESCE
(
s.[host_name],
'.'
),
CASE s.program_name WHEN 'Microsoft SQL Server Management Studio - Query' THEN 'SSMS'
WHEN 'Microsoft Business Solutions-Navision client' THEN 'Nav Client'
WHEN 'Microsoft Business Solutions-Navision Application Server' THEN 'NAS'
WHEN '.Net SqlClient Data Provider' THEN '.Net SQL Client'
WHEN 'Microsoft Data Access Components' THEN 'MDAC'
WHEN 'Microsoft Sql Server Managment Studio' THEN 'SSMS'
ELSE s.program_name END AS Client,
r.sql_handle,
r.percent_complete as [% Complete]
FROM
sys.dm_exec_sessions s
LEFT OUTER JOIN
sys.dm_exec_requests r
ON
s.session_id = r.session_id
LEFT OUTER JOIN
sys.dm_exec_connections c
ON
s.session_id = c.session_id
LEFT OUTER JOIN
(
SELECT
request_session_id,
database_id = MAX(resource_database_id)
FROM
sys.dm_tran_locks
GROUP BY
request_session_id
) t
ON
s.session_id = t.request_session_id
LEFT OUTER JOIN
sys.dm_os_waiting_tasks wt
ON
s.session_id = wt.session_id
LEFT OUTER JOIN
sys.dm_os_tasks ot
ON
s.session_id = ot.session_id
LEFT OUTER JOIN
(
SELECT
ot.session_id,
CPU_Time = MAX(usermode_time)
FROM
sys.dm_os_tasks ot
INNER JOIN
sys.dm_os_workers ow
ON
ot.worker_address = ow.worker_address
INNER JOIN
sys.dm_os_threads oth
ON
ow.thread_address = oth.thread_address
GROUP BY
ot.session_id
) tt
ON
s.session_id = tt.session_id
WHERE
COALESCE
(
r.command,
r.wait_type,
wt.wait_type,
r.last_wait_type,
'a'
) >= COALESCE
(
NULL,
'a'
)
AND
-- Forget those sleeping since they are not doing anything!
UPPER(COALESCE
(
r.status,
ot.task_state,
s.status,
'')) <> 'SLEEPING'
AND
-- Show just database activity
COALESCE
(
DB_NAME(COALESCE
(
r.database_id,
t.database_id
)),
''
) = 'YOUR DATABASE'
AND
UPPER(COALESCE
(
r.status,
ot.task_state,
s.status,
'')) <> 'BACKGROUND'
ORDER BY
DATEDIFF(mi, r.start_time, GETDATE()) DESC
Regards,
Ian
October 23, 2008 at 10:05 am
You should also look at downloading Microsoft's Performance Dashboard for SQL 2005 which will give more indepth data if needed. I usually keep my activity report running and when I see an issue (cell turning to red), I'll fire up the Performance Dashboard for a more detail look.
Ian
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply