Archive

Archive for October, 2010

How to find Last Execution Date of Stored Procedure

October 25, 2010 2 comments

I have seens this common question on few SQL Server Forum that how can i get the last execution date for Stored Procedur or when was the stored procedure exedcuted last.

Now How to achive this?

Every SQL Stament sent to SQL Server is PARSED,COMPILEd and EXECTED.SQL Server maintains QUERY PLAN for each and every statement and keeps it in the system tables.

Which we can query using DMV’s.Query Plans are flushed out if SQL Server Service gets started manulally or automatically(In cluster mode)/runs update stats command / reindexing / usinf DBCC CACHE Clean Commands.

To get last execution date of the stored procedure if SQL Server has the Query Plan for it.

SELECT qs.sql_handle,qs.statement_start_offset,qs.statement_end_offset,

qs.creation_time,qs.last_execution_time,qp.dbid,qp.objectid,st.text
FROMsys.dm_exec_query_statsAS qs
CROSSAPPLYsys.dm_exec_sql_text(qs.sql_handle)AS st
CROSSAPPLYsys.dm_exec_text_query_plan(qs.plan_handle,DEFAULT,DEFAULT)AS qp
WHERE st.textlike‘%USP_CDS_GetUserDetails%’

 

How to Generate Grant Permission Script to User on Database Objects

October 23, 2010 Leave a comment

Script :Grant Permisison on User Object to User (Read Only Permission)

The will help to generate Grant Select,Execute & View permission to user on dataabse objects.We use this script when we have to give read only permission to users on prodcution databases.

 —Select Permisison on User  Table— DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login

FROM sys.objects so INNER JOIN sys.schemas sc ON

so.schema_id= sc.schema_id

WHERE TYPE = ‘U’

AND so.NAME NOT LIKE ‘SYNC%’

SELECT * FROM @tables

  —Execute Permission on User Stored Procedure—-

GO

DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login

FROM sys.objects so INNER JOIN sys.schemas sc ON

so.schema_id= sc.schema_id

WHERE TYPE = ‘P’

AND so.NAME NOT LIKE ‘SYNC%’

SELECT * FROM @tables

  —Select Permission on User View—–

GO

DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login

FROM sys.objects so INNER JOIN sys.schemas sc ON

so.schema_id= sc.schema_id

WHERE TYPE = ‘V’

AND so.NAME NOT LIKE ‘SYNC%’

SELECT * FROM @tables

 —Select Permission on User Function (Table-Valued-Function)—-

GO

DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT SELECT ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login

FROM sys.objects so INNER JOIN sys.schemas sc ON

so.schema_id= sc.schema_id

WHERE TYPE IN (‘TF’)

AND so.NAME NOT LIKE ‘SYNC%’

SELECT * FROM @tables

—Select Permission on User Function (Scalar-Valued-Function)—-

GO

DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT EXECUTE ON OBJECT :: ‘ + sc.name +’.’ + so.NAME + ‘ TO ‘+@login

FROM sys.objects so INNER JOIN sys.schemas sc ON

so.schema_id= sc.schema_id

WHERE TYPE IN (‘FN’)

–AND so.NAME NOT LIKE ‘SYNC%’

SELECT * FROM @tables

—Grant View Defination on User Types—

GO

DECLARE @login varchar(50)

SET @login = ‘DBARoles’

DECLARE @tables TABLE(ROWID int IDENTITY(1,1), SQLSTR varchar(500))

INSERT INTO @tables

SELECT ‘GRANT VIEW DEFINITION ON TYPE :: ‘ + ‘dbo’ +’.’ + st.NAME + ‘ TO ‘+@login

FROM sys.types st where st.schema_id=1

SELECT * FROM @tables