25112
asked on
what do you about mismatched 'dbo' user
when you restore a db on another server,
EXEC sp_change_users_login 'Report'
reports the mismatched sid values.....
but trying to fix 'dbo' value gives
"Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure."
how can this mismatched sid for 'dbo' affect the application in a negative way? would you be concerned about it?
EXEC sp_change_users_login 'Report'
reports the mismatched sid values.....
but trying to fix 'dbo' value gives
"Terminating this procedure. 'dbo' is a forbidden value for the login name parameter in this procedure."
how can this mismatched sid for 'dbo' affect the application in a negative way? would you be concerned about it?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I strongly disagree.
The dbo user can still have a corresponding login, and it's extremely important that the dbo user not be "orphaned".
If 'dbo' gets reported, I suggest changing the db owner to sa to prevent possible future failures due to an invalid/missing db owner:
ALTER AUTHORIZATION ON DATABASE::<db_name> TO sa
My preference is for as many native SQL users as possible, to sync up the sids on the different environments (dev, qa, prod, etc) so I don't have to constantly repair sids after restoring a db from one environment to another.
The dbo user can still have a corresponding login, and it's extremely important that the dbo user not be "orphaned".
If 'dbo' gets reported, I suggest changing the db owner to sa to prevent possible future failures due to an invalid/missing db owner:
ALTER AUTHORIZATION ON DATABASE::<db_name> TO sa
My preference is for as many native SQL users as possible, to sync up the sids on the different environments (dev, qa, prod, etc) so I don't have to constantly repair sids after restoring a db from one environment to another.
ASKER
>>it's extremely important that the dbo user not be "orphaned".
in my case (example), it is then orphaned? is it mainly orphaned because it does not have corresponding login? can I or should I create a dbo login and then try to repair with sp_change_users_login?
or is changing the owner the only solution?
in my case (example), it is then orphaned? is it mainly orphaned because it does not have corresponding login? can I or should I create a dbo login and then try to repair with sp_change_users_login?
or is changing the owner the only solution?
You could create the corresponding login and re-link the user and login.
I prefer that all my dbs be owned by sa, when sa is available.
But certain apps do require that their login owns dbs directly.
I prefer that all my dbs be owned by sa, when sa is available.
But certain apps do require that their login owns dbs directly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>The role only exist at the database level so they don't need to be repaired.
is that why
select * from sys.syslogins where name = 'dbo'
brings back nothing?
b)
when
EXEC sp_change_users_login 'Report'
reports
"dbo"
as the only record, where is it comparing and reporting a concern?