Could not obtain information about Windows NT group/user .......error code 0x534...

Question

== I asked this question directly to Remus and wanted to share the response to all of those people using this forum ==

We recently moved our database server from SQL Server 2000 to SQL Server 2005. All applications on our intranet development server stay the same [VS.NET 2003], but recently resources in our Dev DB server ran out of space. While doing a thorough investigation, I noticed ERRORLOG file was occupying about 35 Gig of HDD space. I immediately checked SQL Server error log and noticed an entry which says –

===========================================================================================

Date                 7/7/2006 4:45:37 PM

Log                   SQL Server (Current - 7/7/2006 4:45:00 PM)

Source              spid77s

Message

The activated proc [dbo].[SqlQueryNotificationStoredProcedure-5eaf8465-d0cb-4be7-93b6-44bb979dd41c] running on queue BW_Content.dbo.SqlQueryNotificationService-5eaf8465-d0cb-4be7-93b6-44bb979dd41c output the following:  'Could not obtain information about Windows NT group/user 'BWCINC\HoffK', error code 0x534.'

===========================================================================================

What is this SqlQueryNotificationService in my database? Is it a SQL Server 2005 thing? Why the same kind of stored procedure does not exist in other databases, but BW_Content? This error is getting repeated most probably every second and is filling up our server.

 I believe our corporate IT people removed our domain accounts from BWCINC domain to BWCORP domain and probably some application which is using BWCINC\HoffK credential is getting errored out. I tried to locate this application and was not successful.

 Is there anyway that I can stop this ERRORLOG from growing? How can I delete these log entries so that I can make space on our Hard Drive? Is there an easy way in SQL Server 2005 to locate which application is creating this error?

Response from Remus:

The 'SqlQueryNotificationService-...' is the service created by SqlDependency when you call SqlDependency.Start (). The problem you describe appears because the 'dbo' user of the database is mapped to the login that originally created this database. The SqlDependency created queue has an EXECUTE AS OWNER clause, owner is 'dbo' and therefore this is equivalent to an EXECUTE AS USER = 'dbo'. The error you see is reported by the domain controller when asked to give information about the original account 'dbo' mapps to (that is, BWCINC\HoffK'): Error code: (Win32) 0x534 (1332) - No mapping between account names and security IDs was done.

 

To solve the issue, change 'dbo' to match a correct login, using either sp_changedbowner or ALTER AUTHORIZATION ON DATABASE::[dbname] TO [somavalidlogin]

To find the databases that have this problem, run this query:

select name, suser_sname(owner_sid) from sys.databases

The databses that have the problem will show NULL on the second column.

A similar problem is described here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=65711&SiteID=1

 

To remove the entries, use sp_cycle_errorlog to force a new errorlog file, then delete the huge log file.

---------------------------------------

 

I executed ALTER AUTHORIZATION ON DATABASE::[BW_Content] TO [sa];

 

I got this error in SQL Error Log once and the growth of ERRORLOG was stopped.

===============================================================

Date                         7/10/2006 1:16:55 PM
Log                          SQL Server (Current - 7/10/2006 1:17:00 PM)

Source                    spid20s

 

Message

The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a&apos; because it does not exist.</Description></Error>'.

===============================================================

 

Should I be concerned about this error?

 

Thanks

-Binoy

Answer 1

Thanks for sharing this!

Answer 2

Binoy Sankar wrote:

I got this error in SQL Error Log once and the growth of ERRORLOG was stopped. =============================================================== Date                         7/10/2006 1:16:55 PM Log                          SQL Server (Current - 7/10/2006 1:17:00 PM) Source                    spid20s   Message The query notification dialog on conversation handle '{6BDE95F7-0EFB-DA11-9064-000C2921B41B}.' closed due to the following error: '<?xml version="1.0"?><Error xmlns="http://schemas.microsoft.com/SQL/ServiceBroker/Error"><Code>-8490</Code><Description>Cannot find the remote service &apos;SqlQueryNotificationService-c15bb868-ed56-47d2-bf91-ce18b320989a&apos; because it does not exist.</Description></Error>'. ===============================================================   Should I be concerned about this error?