2

I'm getting a bunch of errors in my MS SQL Server logs about a missing ##MS_PolicyEventProcessingLogin## principal. I went to check the master database and yes, it is indeed missing. I don't know how this happened.

How do I recreate this principal?

Improve this question

3 Answers 3

Reset to default
7

Any account enclosed in "##" are certificate based accounts and you will not be able to simply create them as you would other logins/users. If you have a backup of the master database prior to the accounts disappearing you could might try restoring the database to get them back. Other than that I suspect you will need to reinstall or try rebuilding the master database.

Read through these two articles (here, here) for rebuilding the system databases. I would suggest taking a backup of the current state of the system databases. Script out all the logins you have in master as you will loose these.

Improve this answer
4
  • I don't have a backup of master unfortunately, how can I rebuild it without reinstalling the entire product? Thank you...
    – md1337
    Commented Feb 10, 2012 at 16:37
  • Mmh, ok so I did what you said, I rebuilt the system databases, following the MS guides you posted. Unfortunately it screwed up the system big time, basically it lost all logins, databases, etc... Fortunately I had a backup of the master db from this morning so I could restore everything. Is there something special I can do to avoid losing everything when I rebuild the system dbs? I looked at the MS guides and nowhere do they mention that all data will be lost...
    – md1337
    Commented Feb 10, 2012 at 21:43
  • 1
    A rebuild of master is basically starting over with an empty (or "new") master database for that instance. All logins will have to be recreated and user databases reattached. There is not really any way around that, it is the reason having backups of your system databases is about as important (if not more) than user database backups.
    – user507
    Commented Feb 12, 2012 at 4:13
  • After rebuilding the master database you need to create the logins. You'll need to script them out using sp_help_revlogin before you rebuild the master database. After the master database is rebuilt you need to then attach all the databases back to the instance using sp_attach_db or management studio. There's no getting around this. Also start taking daily backups of your system databases (I'm assuming your user databases are already being backed up).
    – mrdenny
    Commented Apr 28, 2013 at 9:12
2

Did you recently re-install sql server and/or recreate server logins?

try

exec sp_change_users_login ‘report’ and if ##MS_PolicyEventProcessingLogin## comes up try sp_change_user_login with autofix.

If it exists in server login, but not in msdb and master databases -

USE [master]
GO

/****** Object:  User [##MS_PolicyEventProcessingLogin##]    
CREATE USER [##MS_PolicyEventProcessingLogin##] FOR LOGIN   [##MS_PolicyEventProcessingLogin##]  WITH DEFAULT_SCHEMA=[dbo]

GO

Improve this answer
4
  • This is the first thing I tried. Nothing comes up after running the report.
    – md1337
    Commented Feb 9, 2012 at 21:22
  • So the ##MS_PolicyEventProcessingLogin## exists in server logins, but you can't find the corresponding users in msdb and master databases? If yes, then create the user (updated answer with create script) -
    – RK Kuppala
    Commented Feb 10, 2012 at 14:44
  • That did not work either, it just created a new regular user in master, called ##MS_PolicyEventProcessingLogin##. When running the report it still returns nothing.
    – md1337
    Commented Feb 10, 2012 at 16:36
  • Then take @Shawn Melton's answer and rebuild the system databases.
    – RK Kuppala
    Commented Feb 10, 2012 at 17:20
0

Run this to get the Sid of the deleted logins,

use  msdb

go
sp_change_users_login 'Report'

Take the Sids from above and place them below in place of ,

CREATE LOGIN [##MS_PolicyEventProcessingLogin##] WITH PASSWORD = 0x0100EBB95886A3A65AD6C770157B4E767D146D83E079A3A33321 HASHED,

SID = <Sid>, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyEventProcessingLogin##] DISABLE

　

CREATE LOGIN [##MS_PolicyTsqlExecutionLogin##] WITH PASSWORD = 0x01008D22A249DF5EF3B79ED321563A1DCCDC9CFC5FF954DD2D0F HASHED,
    
SID = <Sid>, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF; ALTER LOGIN [##MS_PolicyTsqlExecutionLogin##] DISABLE

Source

Improve this answer
1
  • i tried this and I can see the logins created successfully. however we are only updating the SID, the passwords in the commands above are unchanged, so I'm not sure if it's recreating them perfectly. Any inputs appreciated.
    – tinker
    Commented Jan 19, 2022 at 2:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.