SDE Logins

[LeoDonahue]

During normal post install of ArcSDE, a sde login is created and that login is associated to a user schema in your spatial database.  Along comes a DBA and deletes all logins except sa.  I can still connect to my spatial database via Direct Connects.  Are the SQL login accounts needed for anything?

I can also connect via app server connections.  Both connections are using SQL Server user accounts.  I know there is an article about re-synching logins after a database restore, but is there any reason why ArcSDE would need a SQL Server login?

I think I may not be able to upgrade this geodatabase in the future without a login, correct?

[RobertHu]

During normal post install of ArcSDE, a sde login is created and that login is associated to a user schema in your spatial database.  Along comes a DBA and deletes all logins except sa.  I can still connect to my spatial database via Direct Connects.  Are the SQL login accounts needed for anything?

If a "sde" login was created in postinstallation, you must use the sde schema for your GDB. The "sde" DB user owns all objects in the SDE repository. If your DBA deleted the "sde" login, which login is mapped to the "sde" user in your GDB?

I'd say your DBA made a big mistake. I don't know if the "sde" login is absolutely necessary. But I DO believe with the sde schema, even you can map another login to the "sde" DB user and it works, you'll have lots of headaches to manage the SDE repository down the road. I would never want to try it.

[LeoDonahue]

If your DBA deleted the "sde" login, which login is mapped to the "sde" user in your GDB?

No login is currently mapped to the sde user.  I'm not sure what the reasoning behind this is/was.  I guess the DBA sees a SQL account (sde) with login access and removed it for security reasons?  I'm still pondering this as it leaves me a little puzzled and speechless.

[RobertHu]

You can try to re-create the sde login and map it to the sde user in all of your GDBs under the instance. This probably is the easiest way. But as I said, I never deleted the sde login on my instance, and never want to try, not even on my dev sever. So, don't know if it will work.

Thanks!

[VinceAngelo]

I wouldn't have thought it possible to remove the login of a user that owns data,
but the disparity between logins, users, and schemas is probably what will save
your data integrity.  However, you'll need to pay close attention to the mapping
of the "new sde" login's login-id to the user-id in the database (they will no longer
match).  The sp_change_users_login procedure is really designed to deal with
the complications of attaching a database from a foreign instance, but you'll need
to use it to reassociate the 'sde' login with the 'sde' user/schema.  Good luck.

- V

[LeoDonahue1]

I'm not sure if the deletion was accidental or intentional, I'm leaning towards the later.  Deleting one login is an accident, deleting both is intentional for whatever reason they had.  Is there any reason for doing so, from a DBA perspective?

Is it possible that through some manipulation via the master database that I'm just not seeing the sde login in Object Explorer?

When I set up this SDE instance, I used SDE schema, created a sde login and user (via the post install) and created one other SQL login/user which the end user would use to load gis data (for the sake of naming conventions).  Both the sde and gis data loader logins are not listed in Security/Logins - Object Explorer, yet they are still users in the spatial database.  And I can connect via ArcCatalog using either of those user accounts and edit data.  I can't imagine how I can be doing those types of things without a login.

I remember using the sp_change_users_login procedure when restoring a database from a backup.

Sorry, must have resurrected an old esri account on this post. (losing my mind I guess).

[LeoDonahue]

About dropping logins and creating orphaned users.

http://msdn.microsoft.com/en-us/library/ms188012.aspx

[ShannonShields · Accepted solution]

Both the sde and gis data loader logins are not listed in Security/Logins - Object Explorer, yet they are still users in the spatial database.  And I can connect via ArcCatalog using either of those user accounts and edit data.  I can't imagine how I can be doing those types of things without a login.

It is possible to drop a login while leaving the database user previously associated with that login intact. However, you wouldn't be able to connect as either of those users if their logins have been deleted or disabled. How are you connecting? What permissions does your account have? It could be that your dba modified what you can see at the server level rather than removing the logins you created.

If you look at the user properties via management studio (database -> security -> users) you'll see the name of the login associated with each user. If there is no login, it will say 'without login'. Otherwise it will list the login that is associated with the user.

-Shannon

[LeoDonahue]

I have an administrative account I use to perform installations.  I used that account to install SDE.  That account is still a login to the SQL Server, but that account is now just part of the public role, whereas before it was part of sysadmin.

I was thinking that the DBA might have changed what I can see with my login and perhaps the other sql logins I created during the SDE install: sde, gis, gisreader, are all still there.

When I connect from ArcCatalog to SDE, I have three direct connections, one for each user: sde, gis and gisreader.  So you must be correct in that those logins are still there, i just can't see them.

sde: connect, create function, create procedure, create table, create view
gis: connect, create procedure, create table
gisreader: connect, permissions granted via ArcCatalog on individual layers, View-Grant.

[ShannonShields]

I have an administrative account I use to perform installations.  I used that account to install SDE.  That account is still a login to the SQL Server, but that account is now just part of the public role, whereas before it was part of sysadmin.

If your login no longer has any elevated permissions such as sysadmin then you will not be able to see the existance of other logins on SQL Server. By default logins and users have no permissions to do or see anything unless explicitly granted. By downgrading your permissions from sysadmin to a regular login, viewing any system metadata like logins becomes very restricted.

-Shannon