Dropping replication Can be a issue

I have recently run 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 0×5.
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? The same 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'

     

There is no more

     

use [mydb]

exec sp_droppublication @publication = N'mysql_tst'

GO