Remap DBO Username to a Different Login in SQL Server

7208
4
03-29-2018 07:35 AM
LoganCaraway
New Contributor II

Hello,

It is important to note that we need to use windows authentication to manage security for our sql servers. I created the enterprise geodatabase and thus it mapped me to the database user dbo. However, I have a GIS Admin group that i need to be mapped as dbo rather than me. I have made them db_owners however this does not function as it should when creating, editing, and versioning data from inside ArcMap. Has something to do with users names must be the same as the scheme for certain things. 

I have tried everything i can think of but the system will not let me alter the user dbo nor delete my user and resign because of dependencies. Without starting over, is their a way I can change the mapping to the group server login. 

0 Kudos
4 Replies
Asrujit_SenGupta
MVP Regular Contributor

To create data as the DBO schema, you need to assign that Windows Login the SYSADMIn server role. Any number of Windows login can be granted that server role.

Also check this: The geodatabase administrator in SQL Server—Help | ArcGIS Desktop 

LoganCaraway
New Contributor II

This is an enterprise. Our GIS Admins are not and should not be the same as our SQL Admins. 

0 Kudos
Asrujit_SenGupta
MVP Regular Contributor

To create data as the DBO schema, you need to assign that Windows Login the SYSADMIn server role. Any number of Windows login can be granted that server role.

This part still stands correct. If you want multiple users to create data as DBO schema and then manage the data (grant privileges, Register as Versioned, etc). Only the data owner can perform those operations. This is only IF you need the owner to be DBO. Any user granted the SYSADMIN, automatically gets assigned the DBO Schema. You cannot map logins to DBO schema, w/o granting them SYSADMIN server role.

If you cannot grant SYSADMIN, you need to think of another way and a different SCHEMA for owning the data.

You cannot assign a single schema to a group. In case of Windows logins mapped to a group, they will automatically use the Windows Login name during data creation.

Also DB_Owner is not the same as DBO.

SQL Server: dbo vs db_owner role member? - Server Fault 

  • What is the version of SQL Server being used? (Example SQL Server 2012 SP3)
  • What is the version of ArcGIS Desktop & Enterprise geodatabase?
0 Kudos
RebeccaStrauch__GISP
MVP Emeritus
 I have a GIS Admin group that i need to be mapped as dbo 

(emphasis added)

This may not be the case, but it seems years ago that using groups to assign privileges for SDE didn't work.  This may be different know (many SQL and ArcGIS versions later) and may have been different circumstances.  Just throwing that out there for something to investigate if nothing else works.

0 Kudos