Stored Procedures Permissions

http://sqlserverimpressions.blogspot.co.uk/2008/10/grant-permissions-to-view-stored.html

A good information for the permissioning

Grant permissions to view stored procedure text




Sometimes in a production environment you may want to give developers read access to the text of the stored procedures but not be able to modify or execute them. In SQL Server 2005, you can grant VIEW DEFINITION permissions. Here are different ways to it











To grant view permissions to the entire schema











GRANT VIEW DEFINITION ON SCHEMA::dbo TO [UserName]









To grant permissions to specific stored procedure









GRANT VIEW DEFINITION ON YourStoredProcedureName TO [UserName]









To grant view definition on all the stored procedures in the database









--temporary table

DECLARE @tmpTable TABLE (

PK_ID INT IDENTITY (1, 1) NOT NULL PRIMARY KEY CLUSTERED,

[name] SYSNAME

)

--declare variables

DECLARE @name SYSNAME,

@RowCount INT,

@RecCount INT,

@strSQL VARCHAR(1000)



INSERT INTO @tmpTable ([name])

SELECT ROUTINE_SCHEMA+'.'+ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_TYPE = 'PROCEDURE' AND ROUTINE_NAME NOT LIKE 'dt_%'





-- counters for while

SET @RecCount = (SELECT count(*) FROM @tmpTable)

SET @RowCount = 1



WHILE (@RowCount < @RecCount + 1)

BEGIN

SELECT @name = [name]

FROM @tmpTable

WHERE PK_ID = @RowCount

SET @strSQL = N'Grant VIEW Definition on ' + rtrim(cast(@name AS VARCHAR(128))) + ' to [UserName]'

--Execute the Sql

EXEC(@strSQL)

--Decrement the counter

SET @RowCount = @RowCount + 1

--reset vars, just in case...

SET @name = null

END

SELECT * FROM @tmpTable

Orphaned MSDTC transactions (-2 spids)

Orphaned MSDTC transactions (-2 spids):


select distinct(request_owner_guid) as [UoW ID] from sys.dm_tran_locks where request_session_id = -2  --- SQL Server 2005 onwards


go

select distinct(req_transactionUoW) as [UoW ID] from syslockinfo where req_spid = -2  -- SQL Server 2000
  go   Then to kill the transaction in SQL Server, follow the command
kill {'UoW ID'}

where {UoW ID} is the result of the above select statement.

References:

http://www.eraofdata.com/orphaned-msdtc-transactions-2-spids/

How to get Scheduled Reports From SSRS

SELECT


[Locale],

[InactiveFlags],

'Next Run Date' = CASE next_run_date

WHEN 0 THEN null

ELSE

substring(convert(varchar(15),next_run_date),1,4) + '/' +

substring(convert(varchar(15),next_run_date),5,2) + '/' +

substring(convert(varchar(15),next_run_date),7,2)

END,

'Next Run Time' = isnull(CASE len(next_run_time)

WHEN 3 THEN cast('00:0'

+ Left(right(next_run_time,3),1)

+':' + right(next_run_time,2) as char (8))

WHEN 4 THEN cast('00:'

+ Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 5 THEN cast('0' + Left(right(next_run_time,5),1)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

WHEN 6 THEN cast(Left(right(next_run_time,6),2)

+':' + Left(right(next_run_time,4),2)

+':' + right(next_run_time,2) as char (8))

END,'NA'),

Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="TO"])[1]','nvarchar(50)') as [To]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="RenderFormat"])[1]','nvarchar(50)') as [Render Format]

,Convert(XML,[ExtensionSettings]).value('(//ParameterValue/Value[../Name="Subject"])[1]','nvarchar(50)') as [Subject]

---Example report parameters: StartDateMacro, EndDateMacro & Currency.

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="StartDateMacro"])[1]','nvarchar(50)') as [Start Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="EndDateMacro"])[1]','nvarchar(50)') as [End Date]

,Convert(XML,[Parameters]).value('(//ParameterValue/Value[../Name="Currency"])[1]','nvarchar(50)') as [Currency]

,[ModifiedDate]

,[LastStatus]

,[EventType]

,[LastRunTime]

,[DeliveryExtension]

,[Version]

FROM

ReportServer.dbo.[Subscriptions] S

INNER JOIN ReportServer.dbo.ReportSchedule R ON S.SubscriptionID = R.SubscriptionID

INNER JOIN msdb.dbo.sysjobs J ON Convert(nvarchar(128),R.ScheduleID) = J.name

INNER JOIN msdb.dbo.sysjobschedules JS ON J.job_id = JS.job_id

Rename Column and Data Type

USE DBNAME


GO
/* The below script change the column name of the SQL Server Table*/

EXEC sp_rename 'dbo.TABLENAME.EXISTING_COLUMN_NAME', 'NEW_COLUMN_NAME', 'COLUMN';

GO

/* The below script change the column data type of the SQL Server Table*/
ALTER TABLE dbo.TABLENAME
ALTER COLUMN NEW_COLUMN_NAME varchar(20) null;

/*To see the changes of the above */

Execute Sp_help  'dbo.TABLENAME'