ArcSDE 10 PR, SQLServer, register layer fails: invalid object name

[Bende_Vries]

Registering a layer does not work.

Environment: Windows7, ArcSDE10 prerelease (32bit), SQLServer 2008 SP1 (64bit)

Steps:
- create a sde user and database via the postinstall
In SQLServer Management Studio
- logged in as sa: create a login ben, create database bendb1, create schema ben in bendb1, add user ben to bendb1
- logged in as ben: create table table1, add column geometry (type=geography)
- logged in as sa: add user ben to database sde, assign role db_datareader
- logged in as sa: add user sde to database bendb1, assign role db_datareader

Register layer via:
"C:\Program Files (x86)\ArcGIS\ArcSDE\sqlexe\bin\sdelayer" -o register -l ben.table1,geometry -C OBJECTID,SDE -t GEOGRAPHY -e a+ -G 4326 -E -180,-90,180,90 -i 5151 -D bendb1 -u ben -p ben

Result in sde_esri_sde.log:
-------------------------------------------------------
ArcSDE 10.0  for SQL Server Build 624 Fri Feb 12 14:47:24  2010
-------------------------------------------------------
Connected to instance . . .
DBMS Connection established...
RDBMS:     "SQL Server"
Instance Name:    "esri_sde"
IOMGR Process ID (PID):           5668

SDE DBMS Error: 8180  Microsoft SQL Server Native Client 10.0: Invalid object name 'bendb1..SDE_layers'.
Instance initialized . . .[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166] SDE DBMS Error: 208  Microsoft SQL Server Native Client 10.0: Invalid object name 'sde.BEN.SDE_logfiles'.
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166] SDE DBMS Error: 208  Microsoft SQL Server Native Client 10.0: Invalid object name 'sde.BEN.SDE_logfiles'.
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166] In DB_close_instance...
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
Describe Cache...
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
Cache Size............................. 200
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
Number of Table Objects Cached          (0)
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
Number of Selects Cached                (0)
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
Number of Joins Cached (DBMS-described) (0)
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
    Cached     Cached
     Hits    Statements
          ---------------------
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]
[Tue Jun 01 13:34:13 2010] [3] [ESRIBX0166]     SELECT's DBMS Described: [0][0]


I have been struggling with this issue now for more then a day trying different methods but the error message stays the same. Following this exact same scenario works fine on:
- XPMode virtual pc: ArcSDE9.3.1 with SQLSErver 2008 SP1
- W3K Server with ArcSDE10 prerelease and SQLServer 2008 SP1

Can somebody help ?

[ShannonShields]

Ben,

I'm trying to reproduce your issue as described - Windows 7-64 bit, SQL Server 2008 SP1-64, ArcSDE 10 Final (not pre-release) and I'm able to successfully register the layer using the identical command & what I think are identical database & user configuration.

What permissions is 'ben' granted in the bendb1 database?
What error message is returned by the sdelayer command?

The table-not-found errors in the log are common, and not necessarily the cause of your problem. We often branch code, or make assumptions about logfile types, multiple versus single database model etc based on whether or not certain objects exist in a database. We don't persist the errors all the way up to the client, but they do show up in logfiles.

-Shannon

[ShannonShields]

wait - just realized you are using 32-bit ArcSDE against 64-bit SQL Server. I can't see that would make a difference, but I'll try to repo with that configuration as well.

-Shannon

[Bende_Vries]

Shannon,

Thanks for your reaction. Initially I tried using ArcSDE 64bit. After many unsuccesful attempts registering the table I tried ArcSDE 32bit.
The permissions in bendb1 are (probably too many, kept experimenting with this):
db_accessadmin
db_datareader
db_datawriter
db_ddladmin
db_owner
db_securityadmin

A collegue of mine followed the same scenario (same platforms, versions, etc.) and with him it worked. So, it must be something with my environment.

[ShannonShields]

I don't think that any of those permissions would cause some sort of conflict, but if you place someone in 'db_owners', the rest of them are redundant. The only permissions that a user who creates a Geodatabase feature class needs are
CREATE TABLE
CREATE PROCEDURE
and optionally CREATE VIEW (if they are going to create views).

When a table gets registered with ArcSDE and/or the Geodatabase a couple of stored procedures get created for ObjectID generation, which is why you'd need create procedure permission. However, being in the db_owners role gives you permission to do just about anything in that database.

-Shannon

[Bende_Vries]

Shannon, thanks again for your reaction. In the meantime I have managed to get an sde gdb going. However not on the sqlserver instance that I was using before. Next to the sqlserver instance I added an sqlexpress instance. In both instances I cannot register layer with sde. Same error as before. In the sqlexpress instance I can add new featureclass. Does not work in the sqlserver instance. Compared the logins and db users details in both instances. They are exactly identical. At least I can continue with my project (need feature service for web editing) using the sqlexpress.

Ben