Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##"

Following error continuously in the agent log after restoring the msdb database

The activated proc '[dbo].[sp_syspolicy_events_reader]' running on queue 'msdb.dbo.syspolicy_event_queue' output the following: 'Cannot execute as the database principal because the principal "##MS_PolicyEventProcessingLogin##" does not exist, this type of principal cannot be impersonated, or you do not have permission.'

This is normally down to an orphaned login for the ‘##MS_PolicyEventProcessingLogin##’ login

To fix, you can first make sure if an orphaned login is in fact the cause:

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

This should show that the SID for the login in the master database is different from the SID in the msdb database

image

Then run the following to correct the orphaned login:

use [master]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go
use [msdb]
go
exec sp_change_users_login 'Auto_Fix', '##MS_PolicyEventProcessingLogin##';
go

By rerunning the check, it should show that the SID is now the same in both database_principals tables

USE master
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go
USE msdb
SELECT * FROM sys.database_principals 
WHERE [name] = '##MS_PolicyEventProcessingLogin##'
go

image

To clean up, cycle the agent error log

use msdb
go
sp_cycle_agent_errorlog

Working with Filegroups

Adding a file and filegroup to a database

USE [master]
GO
ALTER DATABASE [DataArchive] 
ADD FILEGROUP [Archive_2015]
GO
ALTER DATABASE [DataArchive] 
ADD FILE ( NAME = N'DataArchive_2015', 
FILENAME = N'G:\DataArchive_2015.ndf' , SIZE = 104448KB , FILEGROWTH = 1048576KB ) 
TO FILEGROUP [Archive_2015]
GO

Making a filegroup default for new data

--Make a filegroup default
ALTER DATABASE core_AdvertDataArchive
MODIFY FILEGROUP Archive_2011 DEFAULT

Checking which tables are located in which filegroups

--Return which filegroup a table is located in
SELECT object_name(i.[object_id]) as Name_of_Object,
i.name as Index_Name,
i.type_desc as Index_Type,
f.name as Name_of_Filegroup,
a.type as Object_Type,
f.type,
f.type_desc
FROM sys.filegroups as f 
INNER JOIN sys.indexes as i 
 ON f.data_space_id = i.data_space_id
INNER JOIN sys.all_objects as a 
 ON i.object_id = a.object_id
WHERE a.type ='U' -- User defined tables only
--AND object_name(i.[object_id]) ='employee' -- Specific object
GO

winhttp.winhttprequest.5.1 the server name or address could not be resolved

Problem: When using POST or GET with sp_OAMethod in SQL Server, you receive the following error:

Error Source Description
0x80072EE7 WinHttp.WinHttpRequest The server name or address could not be resolved 

An example of a HTTP Post function is provided below:

Create function GetHttp
(
    @url varchar(8000)      
)
returns varchar(8000)
as
BEGIN
    DECLARE @win int 
    DECLARE @hr  int 
    DECLARE @text varchar(8000)

    EXEC @hr=sp_OACreate 'WinHttp.WinHttpRequest.5.1',@win OUT 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAMethod @win, 'Open',NULL,'GET',@url,'false'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAMethod @win,'SEND'
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OAGetProperty @win,'ResponseText',@text OUTPUT
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

    EXEC @hr=sp_OADestroy @win 
    IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 
    
    RETURN @text
END

The function is then called with a URL Variable:

DECLARE @url VARCHAR(500)
SET @url = 'http://google.co.uk'
exec GetHttp @url

Although there are many reasons for the error, if the simpler solutions have not worked (eg: Making sure the URL is correct, that the DNS resolves, Firewall is correctly configured) then the issue may be a hidden proxy.

WinHTTP uses its own proxy and depending on how your servers are configured, it may need to be set or unset.

For me, the proxy was enabled and i needed direct access.

Open an Elavated Powershell window:

> netsh winhttp show proxy

Current WinHTTP proxy settings:

    Proxy Server(s) :  proxy.xxxxxxxxx.co.uk:8080

    Bypass List     :  (none)

> netsh winhttp reset proxy

Current WinHTTP proxy settings:

    Direct access (no proxy server).

> netsh winhttp show proxy

Current WinHTTP proxy settings:

    Direct access (no proxy server).

Should I be using COM Objects post SQL 2000??!?

We did this for an old system, but my recommendation would be to use SQL Server CLR’s to access REST Services.

Great post here on MSDN of how to do this – http://blogs.msdn.com/b/sqllive/archive/2008/06/18/accessing-rest-based-web-services-using-sql-clr.aspx

Delete a job from multiple servers using SSMS Server Groups

For more detail on Server groups see http://blog.hoegaerden.be/2012/11/24/ssms-connect-to-several-servers-in-one-click-okay-two/ as that explains it in more detail.

The issue is that as Job IDs are unique across servers, a standard sp_delete_job query wouldnt work as we first need to know the Job ID.

The below sample is to delete the same named job across multiple servers:

USE [msdb]
GO

declare @jobid varchar(38)

select @jobid = job_id from sysjobs where name = 'Job Name'

/****** Object:  Job [Monitoring - Backup Stats]    Script Date: 31/12/2014 09:28:37 ******/
EXEC msdb.dbo.sp_delete_job @job_id=@jobid, @delete_unused_schedule=1
GO

Free space in all databases..or just one

Useful query which returns a lot of information around physical and logical disk usage for all database. A where clause can be commented out to just return for one database or a selection

CREATE TABLE #tmp_fileinfo(
    [Server] [varchar](30) NOT NULL,
    [Instance] [varchar](30) NULL,
    [DBName] [sysname] NULL, 
    [File_Name] [sysname] NOT NULL,
    [Size] [numeric](10, 2) NULL,
    [Used] [numeric](10, 2) NULL,
    [Unused] [numeric](10, 2) NULL,
    [Drive] [nvarchar](3) NULL,
    [Physical_Location] [varchar](260) NULL,
    [State] [varchar](15) NULL,
    [AutoGrow_MB] [int] NULL,
    [Percent_growth_set] [Varchar](5) NULL,
    [Percent_growth] [int] NULL,
    [Max_Size_MB] [varchar](9) NULL,
    [Timestamp] [datetime] NULL
) ON [PRIMARY]
 
declare @SQL nvarchar(max)
set @SQL = ''
select @SQL = @SQL + 'USE'  + QUOTENAME(name) + '
insert into #tmp_fileinfo
select Convert(varchar(30),SERVERPROPERTY(''MachineName'')), Convert(varchar(30), 
SERVERPROPERTY(''InstanceName'')), ' + QUOTENAME(name,'''') + ' AS [DBName],
[name] As [File_Name]
,CONVERT(NUMERIC(10,2),ROUND([size]/128.,2)) AS [Size]
,CONVERT(NUMERIC(10,2),ROUND(FILEPROPERTY([name],''SpaceUsed'')/128.,2)) AS [Used]
,CONVERT(NUMERIC(10,2),ROUND(([size]-FILEPROPERTY([name],''SpaceUsed''))/128.,2)) AS [Unused]
,UPPER(SUBSTRING(physical_name, 1, 3)) AS [Drive]
,UPPER([physical_name]) AS [Physical Location]
,UPPER([state_desc]) AS [State]
,[AutoGrow_MB] = CASE WHEN [is_percent_growth] = 0 THEN [Growth]*8/1024 ELSE 0 END
,[Percent_growth_set] = CASE WHEN is_percent_growth = 1 THEN ''TRUE'' ELSE ''FALSE'' END
,[Percent_growth] = CASE WHEN [is_percent_growth] = 1 THEN [Growth] ELSE 0 END
,[MAX_SIZE_MB] = 
    CASE 
        WHEN CAST(max_size as VARCHAR(15)) = -1 THEN ''Unlimited''
        WHEN max_size = 268435456 THEN CAST(2097152 AS VARCHAR(15))
        WHEN max_size >=0 THEN CAST(([max_size]*8)/1024 AS VARCHAR(15))
    END
,getdate()
FROM [sys].[database_files] '
from sys.databases
  
execute (@SQL)
select * from #tmp_fileinfo 
where DBName = 'master'
order by DBName, File_Name
  
drop table #tmp_fileinfo

Collations available on a SQL Server

Nice little query to find all collations and definitions from SQL Server:

select name, COLLATIONPROPERTY(name, 'CodePage') as Code_Page, description
from sys.fn_HelpCollations()