topic Re: Using SDE schmema but getting DBO.default in Data Management Questions

Using SDE schmema but getting DBO.default

JoeBorgione — Thu, 26 Apr 2012 10:49:12 GMT

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.

Re: Using SDE schmema but getting DBO.default

VinceAngelo — Thu, 26 Apr 2012 11:46:58 GMT

Did you make the SDE user the owner of the database? Or grant administrative access to SDE?

- V

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Thu, 26 Apr 2012 13:35:00 GMT

V- nope. I figured post install would somehow auto-magically cover that for me. Guess I need to set up SDE user a-priori in sql as a dbo user?

Thanks-
jb

Re: Using SDE schmema but getting DBO.default

VinceAngelo — Thu, 26 Apr 2012 14:24:58 GMT

No, no! You *don't* want 'sde' in the Administrator or DBO groups, else SQL-Server
will promote your 'sde' to DBO.

- V

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Thu, 26 Apr 2012 14:42:57 GMT

No, no! You *don't* want 'sde' in the Administrator or DBO groups, else SQL-Server
will promote your 'sde' to DBO.

- V

Fair enough.... But back to the orginal post, how do I get sde.default as opposed to dbo.default when I run the post install? I haven't done a complete new install since 8.3; just upgrades to exisiting databases...

Thanks-

Re: Using SDE schmema but getting DBO.default

DeminHu — Thu, 26 Apr 2012 19:37:01 GMT

By default, if you selected SDE schema in the post installation, it would be sde.Default.

In your case, I suggest you check the database: Security->users, Security->Schemas.
There should be a schema "sde", if not, create a sde schema, by default, set permission: connect, create function, procedure, table view and view database states.
In this database, sde user only owns sde schema.

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Thu, 26 Apr 2012 20:38:46 GMT

By default, if you selected SDE schema in the post installation, it would be sde.Default.

Yep, that's what I thought, but it didn't work that way....

Re: Using SDE schmema but getting DBO.default

KimPeter — Thu, 26 Apr 2012 23:51:56 GMT

Hi Joe,

Do you mean that when you connect to the geodatabase in Desktop, you only see dbo.DEFAULT as the transactional version option? You restarted Desktop after re-creating your geodatabase, right? If you create a new spatial database connection, does it still show dbo.DEFAULT only?

And you did say you deleted the database from the SQL Server instance before you re-ran the Post Installation wizard. If you didn't completely get rid of the database and start from scratch, re-running the Post Installation wizard on the existing database wouldn't switch schemas. I seem to recall that when you delete a database in SQL Server, there's an option that asks if you want to restore from backup. Is it possible that was checked and, therefore, it restored a backup that already contained the ArcSDE geodatabase system tables?

When you open the database in Management Studio, if the ArcSDE geodatabase system tables are shown to be in the dbo schema (i.e., they're named things like dbo.sde_layers), either try running the Post Installation wizard and provide a different name for the database, or drop the existing database, be sure it is gone from the SQL Server instance, and run the Post Installation wizard to re-create the database and geodatabase, specifying an sde-schema.

-Kim

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Fri, 27 Apr 2012 03:38:28 GMT

My responses in bold....

Hi Joe,

Do you mean that when you connect to the geodatabase in Desktop, you only see dbo.DEFAULT as the transactional version option? You restarted Desktop after re-creating your geodatabase, right? If you create a new spatial database connection, does it still show dbo.DEFAULT only? Hmmm; can't recall exactly; seems like I did though; that was yesterday. Another long day at a different client site today!

And you did say you deleted the database from the SQL Server instance before you re-ran the Post Installation wizard. If you didn't completely get rid of the database and start from scratch, re-running the Post Installation wizard on the existing database wouldn't switch schemas. I seem to recall that when you delete a database in SQL Server, there's an option that asks if you want to restore from backup. Is it possible that was checked and, therefore, it restored a backup that already contained the ArcSDE geodatabase system tables? The databases are gone for sure; there is some perverse satisfaction in deleting those that cause pain. However, I just let the post install delete and recreate the 5151 instance.

When you open the database in Management Studio, if the ArcSDE geodatabase system tables are shown to be in the dbo schema (i.e., they're named things like dbo.sde_layers), either try running the Post Installation wizard and provide a different name for the database, or drop the existing database, be sure it is gone from the SQL Server instance, and run the Post Installation wizard to re-create the database and geodatabase, specifying an sde-schema. I'll be back at this on Monday (today is Thursday). I'm not done with the project nor this thread by a long shot!

-Kim

Thanks Kim, et al. I'll post back up in a couple of days what the solution works out to be.

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Mon, 30 Apr 2012 21:58:23 GMT

Got to the bottom of it: for whatever reason, the SDE user was granted sysadmin rights in SQL. Once I cleared that hurdle, I got the post install to work as expected. Here's another tip: Melanie W in tech support is awesome when it comes to SDE, Server and SQL Server. She's helped me in the past and nailed this one in record time!

Re: Using SDE schmema but getting DBO.default

ChrisMcLean2 — Mon, 27 Aug 2018 15:58:01 GMT

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.

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

USE [your_database_name]

ALTER AUTHORIZATION ON SCHEMA::[sde] TO [dbo]

DROP USER [sde]

CREATE USER [sde] FOR LOGIN [sde]

ALTER USER [sde] WITH DEFAULT_SCHEMA=[sde]

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

ALTER AUTHORIZATION ON SCHEMA::[sde] TO [sde]

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

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Mon, 27 Aug 2018 16:16:01 GMT

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

Re: Using SDE schmema but getting DBO.default

ChetDobbins — Mon, 27 Aug 2018 18:54:44 GMT

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.

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

Re: Using SDE schmema but getting DBO.default

JoeBorgione — Mon, 27 Aug 2018 18:58:06 GMT

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