Error "The server principal owns one or more event notification(s) and cannot be dropped." when repairing SQL Server 2008


  • Hi everyone,

    I have this error when trying to repair SQL Server 2008. First I was trying to apply SQL Cumulative Update Package 4, which failed.
    I'm trying to do a "Repair" on this SQL installation bu t receiving this message.

    Here is a part of the log file.

    2009-04-04 19:47:52 SQLEngine: --SqlDatabaseServiceConfig: Running queries SQL....
    2009-04-04 19:47:52 SQLEngine: : Repair script parameters:
    2009-04-04 19:47:52 SQLEngine: :  Instance Name: MSSQLSERVER
    2009-04-04 19:47:52 SQLEngine: :  Network Name: 
    2009-04-04 19:47:52 SQLEngine: :  Account SIDs to add to SA / Logins: S-1-5-18; S-1-5-80-3880718306-3832830129-1677859214-2598158968-1052248003
    2009-04-04 19:47:52 SQLEngine: :  Account SIDs to add to Logins: 
    2009-04-04 19:47:52 SQLEngine: : Checking Engine checkpoint 'AddSysAdmins'
    2009-04-04 19:47:52 SQLEngine: --SqlEngineScriptExecution: Asking SQL Server to convert SID S-1-5-18 to account name.
    2009-04-04 19:47:52 Slp: --SafeSqlCommand: SELECT suser_sname(@sid)
    2009-04-04 19:47:52 Slp: Sco:SqlScriptStatementCompleted: Rows affected 1
    2009-04-04 19:47:52 SQLEngine: --SqlEngineScriptExecution: SID S-1-5-18 maps to NT AUTHORITY\SYSTEM
    2009-04-04 19:47:52 Slp: --SafeSqlCommand: if exists(select * from sys.server_principals where name = N'NT AUTHORITY\SYSTEM') DROP LOGIN [NT AUTHORITY\SYSTEM];
    2009-04-04 19:47:52 Slp: Sco: SqlException: Message:The server principal owns one or more event notification(s) and cannot be dropped.

    I tried to do find all objects owned by NT AUTHORITY\SYSTEM with this stored procedure (
    In case you're not registered, here is the code of this stored procedure:

    CREATE Proc spLogin_OwnedObjects ( @Login as SYSNAME ) As
            Display all objects in all DBs owned by the Login.
    2008-07-06 RBarryYoung  Created.
    2008-08-28 RBarryYoung Added corrections for DBs with different Collations
       (note that ReportingDBs have different Collations)
     EXEC spLogin_OwnedObjects 'sa'
        declare @sql varchar(MAX), @DB_Objects varchar(MAX)
        Select @DB_Objects = ' COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User]
     , o.object_id
     , o.principal_id
     , o.schema_id
     , o.parent_object_id
     , o.type COLLATE DATABASE_DEFAULT as [type]
     , o.type_desc COLLATE DATABASE_DEFAULT as [type_desc]
     , o.create_date
     , o.modify_date
     , o.is_ms_shipped
     , o.is_published
     , o.is_schema_published
         From %D%.sys.objects o
          Join %D%.sys.database_principals u 
            ON Coalesce(o.principal_id
        , (Select S.Principal_ID from %D%.sys.schemas S Where S.Schema_ID = O.schema_id))
                = U.principal_id
          Left Join %D%.sys.server_principals L on L.sid = u.sid
        Select @sql = 'SELECT * FROM
        (Select '+Cast(database_id as varchar(9))+' as DBID, ''master'' as DBName, '
                         + Replace(@DB_objects, '%D%', [name])
         From master.sys.databases
         Where [name] = 'master'
        Select @sql = @sql + 'UNION ALL Select '+Cast(database_id as varchar(9))+', '''+[name]+''', '
                         + Replace(@DB_objects, '%D%', [name])
         From master.sys.databases
         Where [name] != 'master'
        Select @sql = @sql + ') oo  Where Login = ''' + @Login + ''''
        print @sql
        EXEC (@sql)

    but it retuns nothing.

    So I'm quiet confused.

    Kind regards,




    Sami Marzouki

    Monday, April 06, 2009 11:27 AM