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

    Question

  • 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.
    
    
    
    LineNumber:1
    
    
    
    


    I tried to do find all objects owned by NT AUTHORITY\SYSTEM with this stored procedure (http://www.sqlservercentral.com/scripts/Administration/63631/)
    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)
    
    Test:
    
     EXEC spLogin_OwnedObjects 'sa'
    
    */
    
        declare @sql varchar(MAX), @DB_Objects varchar(MAX)
    
        Select @DB_Objects = ' L.name COLLATE DATABASE_DEFAULT as Login, U.Name  COLLATE DATABASE_DEFAULT as [User]
    
     , o.name COLLATE DATABASE_DEFAULT as [name]
    
     , 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

     


     

    Sami Marzouki

    Monday, April 06, 2009 11:27 AM