Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 2 down vote favorite

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?

share|improve this question

2 Answers

up vote 5 down vote

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.

share|improve this answer
I don't have a backup of master unfortunately, how can I rebuild it without reinstalling the entire product? Thank you... – md1337 Feb 10 '12 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 Feb 10 '12 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. – Shawn Melton Feb 12 '12 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♦ Apr 28 at 9:12
up vote 1 down vote

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

share|improve this answer
This is the first thing I tried. Nothing comes up after running the report. – md1337 Feb 9 '12 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) - – yogirk Feb 10 '12 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 Feb 10 '12 at 16:36
Then take @Shawn Melton's answer and rebuild the system databases. – yogirk Feb 10 '12 at 17:20

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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