KBA-01204: The server principal “Master60SP” is not able to access the database “sfSol6DocSys”

Purpose & Scope:

When saving in Microsoft Dynamics SL, the following error message appears:

System Message 916: The server principal ‘Master60SP‘ is not able to access the database ‘sfSol6DocSys‘ under the current security context.

Procedure:

If you‘ve restored a Microsoft Dynamics SL database from one SQL Server to another, the Spitfire database on the new server may still be referring to the old Master60SP.

To correct this problem, run the following script in SQL Query Analyzer on the Spitfire database:

if (select sid from sysusers where name = ‘master60sp‘) <> (select sid from master.dbo.syslogins where name = ‘master60sp‘) begin

      print ‘bad sid‘

      exec sp_dropuser ‘master60sp‘

      exec sp_adduser ‘master60sp‘

end

 

Alternate:

exec sp_dropuser 'master60sp'
ALTER AUTHORIZATION ON DATABASE::sfSalesDocSys TO 'master60sp'

Additional Comments:

A similar problem is that the user in the database is ‘orphaned‘: there is no login ID or password associated with the user. SQL has a stored procedure to fix the issue.

First, make sure that this is the problem. This will list the orphaned users:

Exec sp_change_users_login ‘Report‘

Temporarily change the database DBO to the sa user:

ALTER AUTHORIZATION ON DATABASE::sfSalesDocSys TO 'sa'

Run the stored procedure to auto-fix the user:

Exec sp_change_users_login ‘auto_fix‘,‘spitfire‘

Reset the user as the DBO of the database:

ALTER AUTHORIZATION ON DATABASE::sfSalesDocSys TO 'spitfire'

KBA-01204; Last updated: July 23, 2018 at 10:56 am;
Keywords:  SQL Logins