Was it a mistake to create enterprise geodatabases with my OS account?

[PaulHuffman]

I have five enterprise geodatabases in ArcServer 10.6.1 and SQL Server  that I created with the Create Enterprise Geodatabase tool from my domain login,  so the Database Admin account is my OS account on my local domain.  But when I retire in a couple months,  how can I pass this Databasee Admin authority to my successor admin?  Did I screw up? Because right now I can't figure out the best way to hand this off.  Will I need to leave my account on the domain so the new admin will have to add my domain login to her PC and have to switch to my user account  to use ArcCatalog to do the admin on these geodatabases, like add users, create roles, add users to roles?  Or can the new admin use SSMS to manage database users? But I can't remember  SSMS can use my admin credentials from a PC logged into the domain with someone else's credentials.  

But at least I have  headless DB accounts for the data owner and the geodatabase administrator account.

[ConnorFriese]

Paul Huffman‌

The most important thing noted is that you still have headless account for the data owner and geodatabase administrator.

This will allow you to to do many geoprocessing functions.

Now in regards to running a tool like Create Database User, that does require someone with System Administrator privileges.

Since you are using SQL Server, a user can be granted the server role "sysadmin".

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-s...

If a user is part of that "sysadmin" role then they have System Administrator privileges and will be able to execute tools that requires those privileges.

Once a user has the "sysadmin" role then they will be known as DBO.

http://desktop.arcgis.com/en/arcmap/latest/manage-data/gdbs-in-sql-server/comparison-geodatabase-own...

Now, if you right-click on the database (in SSMS), go to properties, and click the General tab then you will get a window like this.

Here you will see the actual owner of the database will still be your OS user.

This really shouldn't have any impact when it comes to doing ESRI workflows.

However, I found some Microsoft documentation that may address how to change this.

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-changedbowner-...

https://blog.arvixe.com/change-database-owner-with-sql-server-management-studio/

[PaulHuffman]

I took a look at my settings with SSMS.  I wanted to create a new DB account to take over as the database owner,  but I found there already is a user dbo with the login name of my OS login.  So it is potentially confusing to admins after me, because it looks like dbo is the expected practice.    Maybe I'll name the new DB user account "DBOwner"  to give System Administrator privileges.

My class textbook from "Deploying and Maintaining a Multiuser Geodatabase" used "sa" as the account name for the database owner.  Maybe I'll go with that. 

[ConnorFriese]

The "sa" account is recognized as DBO because that user has the "sysadmin" fixed server role.

Since it is a headless account it would probably be easier to set this user as the database owner.

DBO versus the database owner is a common confusion and something specific to SQL Server. This documents may provide some insight into the differences.

https://stackoverflow.com/questions/2731787/what-is-the-difference-between-db-owner-and-the-user-tha...

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/server-and-database-roles-in-sql-s...

[PaulHuffman]

I got an error trying to use the Create Database User tool in Desktop 10.6.1.  [Microsoft][ODBC Driver 13 for SQL Server] [SQL Server] Cannot use the special principal 'sa'.    Is it not recommended to create a user "sa" to be the database owner with sysadmin priviledges?  Or should I use SSMS to create this user "sa"?

[ConnorFriese]

Yes, you should be able to use the "sa" account as the database owner.

I believe anyone with the 'sysadmin' role can change other user's passwords.

https://www.top-password.com/knowledge/change-sql-server-password.html

[PaulHuffman]

The database administrator in this office just told me that he would prefer that I not create any more DB accounts but to do this with existing OS user accounts, that  we create a small group of OS user accounts to be the  Database Administrators.  At first, I was resistant to this because I was taught in my ESRI class that the standard practice was to create headless DB accounts to be  Database administrator,  geodatabase administrator, and data owner.  Then I had in my ArcCatalog three different database connections, one for each level.   But I think it might work OK to create a role for each to these three levels,  then add to these roles any current headless DB account I've been using as well as the Windows OS user accounts that is going to take this over from me.  We could add and remove Windows user accounts to these roles as people came and went.  Then, I guess,  the small group of users that would have these admin capabilities would just need one database connection in ArcCatalog to each of the five geodatabases, and would be able to do the sa, sde, and data owner stuff from that one connection.  Would this approach work?

I'm kind of worried that the data owner might look different depending on which OS user in the data owner role loaded the data.  Would another user in the data owner role be able to manage privileges to data they didn't load?  Might this be a case where it is simpler to use a headless DB user to load and own all the data?  Like this scenario:  I add myself and Adrianne to the data owner group with our perspective Windows OS domain logins.  I add a bunch of data.  I leave the organization, so the network admin  removes my domain account.  Now Bob joins the office.  Will Adrianne, as part of the data owner role, be able to give Bob privileges to data I loaded? 

[ConnorFriese]

Creating those roles would work, so that setup is valid.

However, there can't be a data owner role. Roles are really just for the permissions.

What makes a data owner isn't quite just permissions, but also being the owner of schema.

When it comes to an enterprise geodatabase, a user must have a schema that matches their username.

A data owner really should only be one user and not part of a role.

[PaulHuffman]

I went into SSMS, added  the windows login for Adrianne to the sysadmin role.  And it looks like my network and database administrator is already in that role.  I didn't find this role at the database level, I found it at the Server Level Security>Server Roles. 

Then for testing I made a Windows user account on a PC on the network for Adrianne,  then logged in as Adrianne,  opened ArcCatalog, made an OS connection to a couple databases as Adrianne, checked Administration> Add User and it wasn't grayed out, so I think it is working.  I didn't have another user handy to try to add but this test seems to show this will work.