Using SDE schmema but getting DBO.default

[JoeBorgione]

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.

[VinceAngelo]

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

- V

[JoeBorgione]

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

[VinceAngelo]

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

- V

[JoeBorgione]

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-

[DeminHu]

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.

[JoeBorgione]

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

[KimPeter]

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

[JoeBorgione]

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.

[JoeBorgione]

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!