Using SDE schmema but getting DBO.default

6485
13
04-26-2012 03:49 AM
JoeBorgione
MVP Esteemed Contributor
Here is my situation;  brand new install of SDE/Server 10.0 on Windows 2008r2 64 bit os.  Using SQL-Server 2008 on a different box than sde installation.

During my post install, initially I chose DBO schema.  After struggling with that, I deleted the database and then ran post install using the SDE schema choice.  Now I can connect via ArcCatalog with the SDE user login, but it still shows dbo.default.  What the ....?  What happened to good old sde.default? 

I would rather use the sde.default/schema as I have in the past, but can't seem to convince the post install of my intentions.
can't wait to retire....
0 Kudos
13 Replies
ChrisMcLean2
New Contributor III

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. 

  1. Remove the sysadmin permissions from the SDE user, must login as another sysadmin to do this
  2. Run the script for each enterprise geodatabase database if necessary

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

0 Kudos
JoeBorgione
MVP Esteemed Contributor

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....

can't wait to retire....
0 Kudos
ChetDobbins
Esri Contributor

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.

  1. The login is a member of the sysadmin server-level role.
  2. The login is the owner of the 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.

0 Kudos
JoeBorgione
MVP Esteemed Contributor

As I recall it was #  1. The login is a member of the sysadmin server-level role.

can't wait to retire....
0 Kudos