Grant Missing Permissions to Functions

select *

from sys.schemas

select *

from sys.database_permissions

select *

from sys.objects

WHERE type_desc LIKE '%FUNCTION%'

select

       'GRANT ' +

       case when ObjectType in ('FT', 'FN') then 'EXECUTE' else 'SELECT' end

       + '  ON [' + SchemaName + ']' + '.' + '[' + ObjectName + '] TO [public]', *

from

(

select o.name as ObjectName, s.name as SchemaName, o.type as ObjectType

FROM sys.objects o

join sys.schemas s on o.schema_id = s.schema_id

--where xtype in (N'FN', N'IF', N'TF', N'FS', N'FT')

WHERE type_desc LIKE '%FUNCTION%'

and o.name not like '%twitter%'

) f

left join sys.database_permissions p on ObjectName = object_name(p.major_id)

where p.major_id is null

Security Audit Report

-- SERVER ROLES

select p.name, p.type_desc, pp.name, pp.type_desc

from  sys.server_role_members roles

join sys.server_principals p on roles.member_principal_id = p.principal_id

join sys.server_principals pp on roles.role_principal_id = pp.principal_id

-- DATABASE ROLES

SELECT

p.name, p.type_desc, pp.name, pp.type_desc, pp.is_fixed_role

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

order by p.name, pp.name

-- DATABASE PERMISSIONS SPECIAL

SELECT

      dp.class_desc, dp.permission_name, dp.state_desc,

ObjectName = OBJECT_NAME(major_id), GranteeName = grantee.name, GrantorName = grantor.name

FROM sys.database_permissions dp

JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

SQL SSIS 2012 - Metadata could not be determined

The metadata could not be determined because the statement 'SELECT   TOP 0 *

                           FROM   (SELECT 1,1,1,1,1,1,'none') as a (BatchID, LoadCount, FailedCount, SampleCo' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth' is not compatible with the statement 'SELECT  a.*, b.FilePath as FilePath

                                  FROM   @OUT a

                                  LEFT

                                  JOIN   dbo.BULKLOAD_Basic b (no' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth'.

Msg 11512, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1

The metadata could not be determined because the statement 'SELECT   TOP 0 *

                           FROM   (SELECT 1,1,1,1,1,1,'none') as a (BatchID, LoadCount, FailedCount, SampleCo' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth' is not compatible with the statement 'SELECT a.BatchID, a.LoadCount, a.FailedCount, a.SampleCount, a.BadCustomerRows, a.BadMediaRows, b.Fi' in procedure 'ETL_DATLOAD_SAVEBatch_Bandwidth'.

declare @p1 int

set @p1=0

exec sp_prepare @p1 output,NULL,N'EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth',1

select @p1

exec [sys].sp_describe_first_result_set N'EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth',NULL,1

EXEC sys.sp_describe_first_result_set N'EXEC sp_who2'

SSIS 2012 is unable to generate the first result set when the stored procedure uses a temporary table. We need to provide the output result set as below to prevent the above error.

EXEC dbo.ETL_DATLOAD_SAVEBatch_Bandwidth

WITH RESULT SETS

(

       (

              BatchID int,

              LoadCount int,

              FailedCount int,

              SampleCount int,

              BadCustomerRows int,

              BadMediaRows int,

              FilePath varchar(500)

       )

)

Datetime Stamped Table Name

-- Variable that will contain the name of the table

   declare @mytable varchar(100)

   -- Creates a temp table name

   select @mytable = 'Table_' + CAST(DATEPART(yy, GETDATE()) as nvarchar(10)) + CAST(DATEPART(mm, GETDATE()) as nvarchar(10)) + CAST(DATEPART(dd, GETDATE()) as nvarchar(10))

   + CAST(DATEPART(hh, GETDATE()) as nvarchar(10)) + CAST(DATEPART(mi, GETDATE()) as nvarchar(10)) + CAST(DATEPART(ss, GETDATE()) as nvarchar(10))

   print @mytable

   -- Create the temporary table

   execute ('create table DatabaseName.dbo.'+ @mytable +

             '(

                           [Date] [datetime] NULL,

                           [CustomerId] [int] NULL

                           [Status] [tinyint] NULL,

                           [LastUpdate] [datetime] NULL

                     )' )

   -- Insert two rows in the table

   execute ('insert into  DatabaseName .dbo.' + @mytable +

                 ' ([Date]

           ,[CustomerId]

           ,[Status]

           ,[LastUpdate])

SELECT [Date]

      ,[FeatureId]

      ,[Status]

      ,[LastUpdate]

  FROM [dbo].[ Table ]')

Check if File/Folder exists

drop table #XpFileExists

CREATE TABLE #XpFileExists         

(         

 IsFileExists bit,         

 IsFileDirectory bit,            

 IsParentDirectoryExists bit         

)         

drop table #Path

CREATE TABLE #Path

(

       [FilePath] [varchar](100) NOT NULL,

       [IsFileExists] [bit] NULL,

       [IsFileDirectory] [bit] NULL,

       [IsParentDirectoryExists] [bit] NULL,

       [IsActive] [bit] NOT NULL,

       [UpdatedOn] [datetime] NULL,

       [CreatedOn] [datetime] NOT NULL,

)

        

declare @FilePath [varchar](500)         

         

declare @IsFileExists bit         

declare @IsFileDirectory bit         

declare @IsParentDirectoryExists bit         

     

declare @FileCounter table (FilePath varchar(500))     

   

insert into @FileCounter(FilePath)

values

('C:\Share\'),

('D:\Share')

while exists(select top 1 * from @FileCounter)         

begin         

         

 select top 1 @FilePath = FilePath from @FileCounter         

          

         

 delete from #XpFileExists         

          

 insert into #XpFileExists         

 Exec master.dbo.xp_fileexist @FilePath         

         

          

 select         

  @IsFileExists = IsFileExists,         

  @IsFileDirectory = IsFileDirectory,            

  @IsParentDirectoryExists = IsParentDirectoryExists         

 from #XpFileExists 

 select * from #XpFileExists       

         

 update b set          

  IsFileExists = @IsFileExists,         

  IsFileDirectory = @IsFileDirectory,            

  IsParentDirectoryExists = @IsParentDirectoryExists,     

  UpdatedOn = GETDATE()     

 from #Path b         

 where b.FilePath = @FilePath         

      

 delete from @FileCounter where FilePath = @FilePath     

      

end         

     

select *     

from #Path     

where IsFileDirectory = 0     

and IsActive = 1    

Table Column DataTypes

select

       o.name,

       OBJECT_NAME(c.OBJECT_ID) TableName

       ,c.name AS ColumnName

       ,SCHEMA_NAME(t.schema_id) AS SchemaName

       ,t.name AS TypeName

       ,t.is_user_defined

       ,t.is_assembly_type

       ,c.max_length

       ,c.PRECISION

       ,c.scale

from sys.objects o

join sys.columns c on o.object_id = c.object_id

join sys.types t on c.user_type_id=t.user_type_id

where c.name = ''

order by o.name

Compile Locks, Owner Qualification, Cache Miss

Hit and miss Or “Monitoring plan cache usage”

http://sqlinthewild.co.za/index.php/2010/07/27/hit-and-miss/

Description of SQL Server blocking caused by compile locks

http://support.microsoft.com/kb/263889

Troubleshooting stored procedure recompilation

http://support.microsoft.com/kb/243586

sys.dm_tran_locks (Transact-SQL)

http://technet.microsoft.com/en-us/library/ms190345.aspx

INF: Understanding and resolving SQL Server blocking problems

http://support.microsoft.com/kb/224453

Performance Impact of Procedure Calls without Owner Qualification

http://sqlblog.com/blogs/linchi_shea/archive/2007/06/30/performance-impact-of-procedure-calls-without-owner-qualification.aspx

SP:CacheMiss Event Class

http://technet.microsoft.com/en-us/library/ms189103.aspx

Plan Caching in SQL Server 2008

http://technet.microsoft.com/en-us/library/ee343986(v=sql.100).aspx

How to identify the cause of recompilation in an SP:Recompile event

http://support.microsoft.com/kb/308737/EN-US

SQL Programmability & API Development Team Blog

http://blogs.msdn.com/b/sqlprogrammability/archive/2007/01/16/9-0-memory-pressure-limits.aspx

Stored Procedures and Caching

http://www.sqlservercentral.com/articles/Administration/procedurecache/591/

Execution Plan Caching and Reuse

http://msdn.microsoft.com/en-us/library/ms181055.aspx