I know this is an old post, but thought that I would add the solution to revert the promoted SDE user from dbo back to the sde schema - which happens when you've granted the SDE user sysadmin rights for whatever reason.
USE [your_database_name]
GO
ALTER AUTHORIZATION ON SCHEMA::[sde] TO [dbo]
GO
DROP USER [sde]
GO
CREATE USER [sde] FOR LOGIN [sde]
GO
ALTER USER [sde] WITH DEFAULT_SCHEMA=[sde]
GO
-- You don't have to give db_owner permissions to the SDE user, you can determine the permissions to give.
ALTER ROLE [db_owner] ADD MEMBER [sde]
GO
ALTER AUTHORIZATION ON SCHEMA::[sde] TO [sde]
GO
During the process the account might become disabled... you'll just have to enable it again.
This worked for me, and hope it works for anyone running into the same problem. This might not be the case, and for an issue of this magnitude you should first test the solution in a DEV and QA environment, and backup your data before making such changes.
Cheers,
Chris
Thanks Chris- you're right this is an old post! So old in fact I changed jobs! But, maybe it'll help someone else who runs into the same problem....
I would like to try to simplify this discussion to clarify the root cause of the issue:
There are only two ways that a SQL Server login can be mapped to the DBO user in a database.
If you create or enable a geodatabase using the SDE login and the SDE login meets either or both of those criteria, then the geodatabase will be stored in the DBO schema instead of the SDE schema. If you want to have an SDE schema geodatabase, you need to start over. Ensure that the SDE login is not configured with either of the items above, and then create a new database and geodatabase.
If you created a geodatabase using the SDE login, and the geodatabase was correctly created in the SDE schema, but later the SDE login was configured to meet either or both of those criteria, the geodatabase will still be stored in the SDE schema. However, you will encounter issues when managing the geodatabase because SDE is no longer SDE, it is DBO. Ensure that the SDE login is not configured with either of the items above. Remove SDE from the sysadmin role, and/or change the ownership of the database to a user who can act as DBO in the database.
As I recall it was # 1. The login is a member of the sysadmin server-level role.