Rahman Agoro, the frontline SQL DBA

Just another WordPress.com weblog

Could not obtain information about Windows NT group/user ‘domain\xxxx’, error code 0x5. sp_dropdistributor

Posted by rahmanagoro on October 11, 2010


I have recently ran into an issue with dropping replication, it complains about the error below.

exec master..sp_dropdistributor @no_checks = 1
Msg 15404, Level 16, State 19, Procedure sp_replcmds, Line 1
Could not obtain information about Windows NT group/user ‘DOMAIN\xxxx.xxxxxx’, error code 0x5.

Exceptions like this are often caused when the login does not have access to a resource, or maybe when one is trying to detach/attach a database in SQL 2008 whilst using different user names. It sounded quite strange that I am having the same issues whilst trying to do something as simple as dropping replication on a test server. I setup a profiler trace for the session that I am using to try and track where the exception is occurring so as to know how to tackle the problem. I realized that the stored procedure was failing when it got the section trying to run the code.

sys.sp_replcmds 0

This stored procedure runs as part of exec master..sp_dropdistributor @no_checks = 1

Upon further investigation, I was able to run the stored procedure on the database which was throwing the exception, the first thing that came to my mind was, “which login owns the database ?”. I then ran the code below to check the owners of each and every database.

select name, SUSER_NAME(sid) as DBOwner from master..sysdatabases (nolock)
where name not like '%Snap'
order by name

Some of the databases came up as “sa” which is my preferred db owner and some came up as null, the ones that came up as null were databases that I had just restored recently using my windows login, once restored the SIDS did not match that which was on the master database in the syslogins table.

In order to fix the problem, I then ran the script below that will update all the database owners on the database to sa.

   EXEC master..sp_MSforeachdb '
USE [?]
IF DB_NAME() not in (''master'',''msdb'',''distribution'',''tempdb'',''model'')
BEGIN
	IF db_name() not like ''%Snap''
		BEGIN
		   SELECT ''?''
			exec sp_changedbowner ''sa''
		END	
END'

Now whilst trying to drop the distributor, it now works without any problem.

exec master..sp_dropdistributor @no_checks = 1

As a precaution, always change the owner of the databases to SA in order to avoid any such problems. Another issue that can occur is if the windows login which owns the database is removed from active directory ? thesame problem can occur.

   EXEC master..sp_MSforeachdb '
USE [?]
IF DB_NAME() not in (''master'',''msdb'',''distribution'',''tempdb'',''model'')
BEGIN
	IF db_name() not like ''%Snap''
		BEGIN
		   SELECT ''?''
			exec sp_changedbowner ''sa''
		END	
END'
Advertisements

2 Responses to “Could not obtain information about Windows NT group/user ‘domain\xxxx’, error code 0x5. sp_dropdistributor”

  1. tom said

    Thank you.

  2. TI said

    Thanks a lot. This solution worked for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: