Passionately curious about Data, Databases and Systems Complexity. Data is ubiquitous, the database universe is dichotomous (structured and unstructured), expanding and complex. Find my Database Research at SQLToolkit.co.uk . Microsoft Data Platform MVP

"The important thing is not to stop questioning. Curiosity has its own reason for existing" Einstein

Friday 1 May 2009

Fix: sa login is not mapped to dbo

The sa login is the login of the system administrator.
Check whether the login is matched.

USE databasename
SELECT u.name AS "Name", ISNULL(l.name, 'dbo is unmatched') AS "Matched Login"
FROM sysusers u
LEFT JOIN master.dbo.syslogins l ON u.sid = l.sid
WHERE u.name = 'dbo'

If unmatched change the database owner.

USE databasename
EXEC sp_changedbowner 'sa'

Then fix the sa dbo mapping.

USE databasename
EXEC sp_change_users_login 'auto_fix', sa

To run a report to check username logins have matching userSID

USE databasename
EXEC sp_change_users_login 'report'

No comments:

Post a Comment

Note: only a member of this blog may post a comment.