April 9, 2013 at 7:29 am
We have deployed on a dwh server the sp_who3 SP to look at user activity. WOuld like to give developers a way to run this without sys admin rights (below is the sp). Tried to give them read rights to the tables in the sp but that would not work. Thought of making a compiled exe with the command and a password encrypted in it to run from a command prompt, the issue is the window returned from the DOS prompt is almost impossible to read. ANy other ideas? THanks
USE [PBI_CMS]
GO
/****** Object: StoredProcedure [dbo].[sp_who3] Script Date: 04/02/2013 10:25:59 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_who3]
AS
BEGIN
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT
SPID = er.session_id
,BlkBy = er.blocking_session_id
,ElapsedMS = er.total_elapsed_time
,CPU = er.cpu_time
,IOReads = er.logical_reads + er.reads
,IOWrites = er.writes
,Executions = ec.execution_count
,CommandType = er.command
,ObjectName = OBJECT_SCHEMA_NAME(qt.objectid,dbid) + '.' + OBJECT_NAME(qt.objectid, qt.dbid)
,SQLStatement =
SUBSTRING
(
qt.text,
er.statement_start_offset/2,
(CASE WHEN er.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), qt.text)) * 2
ELSE er.statement_end_offset
END - er.statement_start_offset)/2
)
,STATUS = ses.STATUS
,[Login] = ses.login_name
,Host = ses.host_name
,DBName = DB_Name(er.database_id)
,LastWaitType = er.last_wait_type
,StartTime = er.start_time
,Protocol = con.net_transport
,transaction_isolation =
CASE ses.transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'Read Uncommitted'
WHEN 2 THEN 'Read Committed'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END
,ConnectionWrites = con.num_writes
,ConnectionReads = con.num_reads
,ClientAddress = con.client_net_address
,Authentication = con.auth_scheme
FROM
USE AdventureWorks2012;
GRANT SELECT ON OBJECT::Person.Address TO RosaQdM
grant select on object::sys.dm_exec_connections
to [CORIZONHEALTH\DOsdieck]
LEFT JOIN ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM cp
WHERE cp.plan_handle = er.plan_handle
) ec
ORDER BY
er.blocking_session_id DESC,
er.logical_reads + er.reads DESC,
er.session_id
END
FROM sys.dm_exec_requests er
LEFT JOIN sys.dm_exec_sessions ses
ON ses.session_id = er.session_id
LEFT JOIN sys.dm_exec_connections con
ON con.session_id = ses.session_id
CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS qt
OUTER APPLY
(
SELECT execution_count = MAX(cp.usecounts)
FROM sys.dm_exec_cached_plans
April 9, 2013 at 8:19 am
Signing stored procedures using a certificate is useful when you want to require permissions on the stored procedure but you do not want to explicitly grant a user those rights.
Tutorial: Signing Stored Procedures with a Certificate - SQL Server 2008 R2
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply