Monday, April 23, 2012

View / Grant / Revoke Security permissions


--view security definitions
select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type,
     sys.database_permissions.permission_name,
     sys.database_permissions.state permission_state,
     sys.database_permissions.state_desc,
     state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE LATIN1_General_CI_AS
 from sys.database_permissions
 join sys.objects on sys.database_permissions.major_id =
     sys.objects.object_id
 join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
 join sys.database_principals on sys.database_permissions.grantee_principal_id =
     sys.database_principals.principal_id
 order by 1, 2, 3, 5

--revoke permission
select
'USE ['+DB_NAME()+']' + ' REVOKE VIEW DEFINITION ON [dbo].['+sys.objects.name+'] FROM ['+sys.database_principals.name+']'
,*
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = sys.database_principals.principal_id
where permission_name = 'VIEW DEFINITION'
and sys.database_principals.name = 'CSODMGMT\SQLAllDBAccess'

Thursday, March 29, 2012

SQL Jobs with Multiple schedules


use msdb
go
/*
select * from sysjobs
select * from sysjobschedules
*/

select a.job_id , a.name,  COUNT(*)
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1



select c.name, b.*, a.*
from sysschedules a
inner join sysjobschedules b on a.schedule_id = b.schedule_id
inner join sysjobs c on c.job_id = b.job_id
where a.schedule_id in
(
select schedule_id from sysjobschedules
where job_id in
(
select a.job_id
from sysjobs a
inner join sysjobschedules b on a.job_id = b.job_id
group by a.job_id , a.name
having COUNT(*) > 1
)
)

Monday, March 19, 2012

Long Running Queries Alert


use master
go

/*
exec sp_long_running_queries @longduration = 15 , @sendalertemail = 1
*/
ALTER procedure sp_long_running_queries
(
@longduration int = 15 -- minutes
, @sendalertemail int = 0
)
as
BEGIN

CREATE TABLE #_tmp_whoisactive
( [dd hh:mm:ss.mss] varchar(8000) NULL,[session_id] smallint NOT NULL,[sql_text] xml NULL,[login_name] nvarchar(128) NOT NULL,[wait_info] nvarchar(4000) NULL,[CPU] varchar(30) NULL,[tempdb_allocations] varchar(30) NULL,[tempdb_current] varchar(30) NULL,[blocking_session_id] smallint NULL,[reads] varchar(30) NULL,[writes] varchar(30) NULL,[physical_reads] varchar(30) NULL,[used_memory] varchar(30) NULL,[status] varchar(30) NOT NULL,[open_tran_count] varchar(30) NULL,[percent_complete] varchar(30) NULL,[host_name] nvarchar(128) NULL,[database_name] nvarchar(128) NULL,[program_name] nvarchar(128) NULL,[start_time] datetime NOT NULL,[login_time] datetime NULL,[request_id] int NULL,[collection_time] datetime NOT NULL)

exec sp_WhoIsActive @destination_table = '#_tmp_whoisactive'

--select datediff(mi,start_time,collection_time) , * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration

if @sendalertemail = 1 and exists ( select * from #_tmp_whoisactive where datediff(mi,start_time,collection_time) > @longduration)
begin

DECLARE @tableHTML  NVARCHAR(MAX) , @subjectstring nvarchar(200);

SET @tableHTML =
N'

Long Runing Queries on '+@@SERVERNAME+' executing over '+convert(nvarchar(10),@longduration)+' minutes

' +
N'
' +
N'
' +
N'
' +
N'
' +
CAST ( ( SELECT td = [dd hh:mm:ss.mss],       '',
td = session_id, '',
td = login_name, '',
td = database_name, '',
td = host_name, '',
td = Status
 FROM #_tmp_whoisactive
 where datediff(mi,start_time,collection_time) > @longduration
 FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N'
Time ElapsedSessionLogin NameDatabaseExecuting HostStatus
' ;

--print @tableHTML
 
set @subjectstring = 'Long Running Queries over '+convert(nvarchar(10),@longduration)+' min on SQL Server '+@@SERVERNAME
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'default',
@recipients = 'DLProductionDBA@cornerstoneondemand.com;jferris@csod.com',
--@recipients ='palexander@csod.com',
@body = @tableHTML,
@body_format = 'HTML' ,
@subject = @subjectstring;
 
end

drop table #_tmp_whoisactive

END
GO

Friday, March 16, 2012

If Primary Key Exists


SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo'


if not exists(SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE  CONSTRAINT_TYPE = 'PRIMARY KEY' AND TABLE_NAME = 'user_career_pref_date' AND TABLE_SCHEMA ='dbo' )
begin
ALTER TABLE [dbo].[user_career_pref_date]
    ADD CONSTRAINT [PK_user_career_pref_date] PRIMARY KEY CLUSTERED ([user_id] ASC, [question_id] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);
end

Wednesday, March 14, 2012

Proc to show expensive queries : sp_expensive_queries



USE [master]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER procedure [dbo].[sp_expensive_queries]
as
BEGIN


SELECT TOP 10
[Average IO] = (total_logical_reads + total_logical_writes) / qs.execution_count
,[Total IO] = (total_logical_reads + total_logical_writes)
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average IO] DESC;

SELECT TOP 10
[Average CPU used] = total_worker_time / qs.execution_count
,[Total CPU used] = total_worker_time
,[Execution count] = qs.execution_count
,[Individual Query] = SUBSTRING (qt.text,qs.statement_start_offset/2,
(CASE WHEN qs.statement_end_offset = -1
THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2
 ELSE qs.statement_end_offset END -
qs.statement_start_offset)/2)
,[Parent Query] = qt.text
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
ORDER BY [Average CPU used] DESC;

SELECT TOP 10
[Total Logical Reads] = qs.total_logical_reads,
[Last Logical Reads] = qs.last_logical_reads,
[Total Logical Writes] = qs.total_logical_writes,
[Last Logical Writes] = qs.last_logical_writes,
[Execution count] = qs.execution_count,
[Individual Query] = SUBSTRING(qt.TEXT, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(qt.TEXT)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2)+1),
[Parent Query] = qt.text,
DatabaseName = DB_NAME(qt.dbid),
[Total Worker Time] = qs.total_worker_time,
[Last Worker Time] = qs.last_worker_time,
[Total Elapsed Time Seconds] = qs.total_elapsed_time/1000000 ,
[Last Elapsed Time Seconds] = qs.last_elapsed_time/1000000 ,
[Last Execution Time] = qs.last_execution_time,
[Query Plan] = qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
ORDER BY qs.total_logical_reads DESC

SELECT TOP 10
[Total Reads] = SUM(total_logical_reads)
,[Execution count] = SUM(qs.execution_count)
,DatabaseName = DB_NAME(qt.dbid)
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt
GROUP BY DB_NAME(qt.dbid)
ORDER BY [Total Reads] DESC;

END