EXECUTE permission was denied on the object 'sp_enable_sql_debug'

The SQL code was failing for one DB User with DBO permissions on the databases it was accessing. 

Problem: There was one stored procedure under database DB1 at some point it used to call other stored procedures from DB2. This caused failure when tried to analyse the issue using DEBUG method.

SOLUTION: Its fixed for us by doing following changes at code and DB level.

Code Level:

11)      Impersonate method

·         With execute as [login]

·         Execute as login=[login name]

22)      Fully qualified database object names

Administration Side:

11)     Grant user granted public role on master to read objects.

22)      Granting View Server State at instance level.

SQL Server 2017 - Parallel redo is started for database

There is a change from Microsoft in the servicing model, going ahead from SQL 2017 there will No Service Packs.

Any vulnerabilities hotfixes will be roll out on need basis and issues will be addressed only through CU and GDR.

Going ahead we need to apply only CU to SQL Server to bring the system into servicing support cycle. J

More On:  https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-the-modern-servicing-model-for-sql-server/

https://blogs.msdn.microsoft.com/sqlreleaseservices/announcing-updates-to-the-sql-server-incremental-servicing-model-ism/

 Discussion Thread:

https://social.technet.microsoft.com/Forums/en-US/71eae9b7-41f4-48fd-a0e9-4c1c67bfdf65/parallel-redo-is-started-for-database-reportservertempdb-with-worker-pool-size-2?forum=sqldatabaseengine

Data and Log File Move to New Location (Move Script)

Below is the script we can use to move the data and log files for the databases except master.


DECLARE
    @counter    INT = 1,
    @max        INT = 0,
    @NEW_DATA_PATH VARCHAR(500)='E:\XXXX\Data',
    @NEW_LOG_PATH VARCHAR(500)='F:\XXXX\Log'
  
DECLARE @FILE_LIST TABLE (
    [ID]    int IDENTITY,
    [DBNAME] VARCHAR(100),
    [LNAME]   VARCHAR(100),
    [PNAME]   VARCHAR(500),
    [TYPE_DESC]  VARCHAR(50)
    )  
INSERT INTO @FILE_LIST
SELECT n.NAME,f.NAME,f.PHYSICAL_NAME,f.TYPE_DESC
FROM MASTER.SYS.MASTER_FILES f,MASTER.SYS.DATABASES n WHERE n.NAME not in('MASTER') 
and f.DATABASE_ID=n.DATABASE_ID

SELECT @max = COUNT([ID]) FROM @FILE_LIST

WHILE @counter <= @max
BEGIN

 DECLARE @FILE_TYPE VARCHAR(100),
         @DBNAME VARCHAR(100),
         @LOGICAL_NAME VARCHAR(100),
         @PHYSICAL_NAME VARCHAR(500) ;
          
 SELECT @FILE_TYPE=TYPE_DESC,@DBNAME=[DBNAME],@LOGICAL_NAME=[LNAME],
 @PHYSICAL_NAME=SUBSTRING([PNAME],LEN([PNAME])-CHARINDEX('\',REVERSE([PNAME]))+2,LEN([PNAME])-1) 
 FROM @FILE_LIST WHERE [ID] = @counter
    
    IF(@FILE_TYPE='ROWS')
    BEGIN
    --ALTER DATABASE database_name MODIFY FILE ( NAME = logical_name, FILENAME = 'new_path\os_file_name' );
        --PRINT @FILE_TYPE+','+@DBNAME+','+@LOGICAL_NAME+','+@PHYSICAL_NAME
        PRINT 'USE MASTER 
               GO 
               ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_DATA_PATH+'\'+@PHYSICAL_NAME+''')
               GO'
  END
  ELSE
   PRINT 'USE MASTER 
          GO 
          ALTER DATABASE '+@DBNAME+' MODIFY FILE ( NAME = '+@LOGICAL_NAME+', FILENAME = '''+@NEW_LOG_PATH+'\'+@PHYSICAL_NAME+''')
          GO'
     SET @counter = @counter + 1
      
 END

Invalid object name 'master.dbo.spt_values'. (Microsoft SQL Server, Error: 208)

Solution : https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/


We need to import  spt_values from other server using import and export method.

Other Sources:

https://blogs.msdn.microsoft.com/suhde/2009/04/05/invalid-object-name-master-dbo-spt_values-microsoft-sql-server-error-208/

https://blog.sqlauthority.com/2017/07/21/sql-server-invalid-object-name-master-dbo-spt_values-management-studio/

Delete Maintenance Plans

We need to perform the below steps to delete the jobs or maintenance plans in such cases from sysmaintplan_log table or fromsysmaintplan_subplans table .

Step 1: 
Execute the below query to obtain the Maintenance plan name and Id SELECT NAME, ID FROM MSDB..SYSMAINTPLAN_PLANS 
Step 2: 
Replace the Id obtained from Step 1 into the below query and delete the entry from log tableDELETE FROM SYSMAINTPLAN_LOG WHERE PLAN_ID=' ' 
Step 3: 
Replace the Id obtained from Step 1 into the below query and delete the entry from subplans table as shown below,DELETE FROM SYSMAINTPLAN_SUBPLANS WHERE PLAN_ID = ' '
Step 4: 
Finally delete the maintenance plan using the below query where ID is obtained from Step1DELETE FROM SYSMAINTPLAN_PLANS WHERE ID = ' '
Step 5: 
Check and delete the jobs from SSMS if it exists.

Many thanks to kapil

https://social.msdn.microsoft.com/forums/sqlserver/en-US/ae6f809f-ef74-4534-8327-b7b120a2d700/unable-to-delete-a-sql-2005-maintenance-plan