Data ownership problems under SQL auth process

665
1
Jump to solution
01-28-2022 07:06 AM
MKF62
by
Occasional Contributor III

My organization recently moved locations and we are working under all new leadership, getting a new server, etc etc. In the process, we've had to move all our data to a new SQL Server. Originally, I was able to make database connections using the operating system auth, but that is no longer the case and they want us to only use database auth. We use the dbo schema for geodatabase ownership. I originally was a sysadmin, but they have downgraded me to db owner of all the databases since their policy on who can be sysadmins is different than our last location. My login name is different than it was at the prior place since we're doing database auth instead of OS auth and this is a new company altogether. My concern is that we lost the data owner due to the changing of both the login names and the type of authentication being used. I am still getting messages that I am not the data owner despite being in the db owner role when I do things like try to manage the geodatabase functionality:

geodatabase functionality.png

 

As you can see, the name of the feature class is [database].DBO(is this the schema or the user name?).[featureclass] which suggests to me that the owner of the data is not any one user. I think this means that only a sysadmin role person can administer it, right? If I was made into a sysadmin, would this problem of not being an owner go away?

This is how my current user is mapped in one of the databases. I do not have any securables.

 

Screenshot 2022-01-28 100244.png

 

Screenshot 2022-01-28 100225.png

Screenshot 2022-01-28 100151.png

 

My server login mapping looks like so. Under the old system, my login was mapped to the dbo user (User column was all dbo, not my username). No idea whether this will be an issue or not. Obviously the dbo user exists and cannot be deleted so changing the User column over to dbo instead of my username might be doable, through altering the login (T-SQL), I'm not sure.

Screenshot 2022-01-28 112814.png

 

 

Any insights or are we screwed?

0 Kudos
1 Solution

Accepted Solutions
MKF62
by
Occasional Contributor III

Turns out we had dbo owning all the data at our last MSSQL setup, so the issue was how my login was mapped. Initially my login "User" was set to my username, but we changed it to dbo and everything worked. Because we had dbo as the owner schema on all the data (formatted like <database name>.DBO.<feature class name>, this made sense. I do not have to be in a sysadmin role for this solution to work.

View solution in original post

0 Kudos
1 Reply
MKF62
by
Occasional Contributor III

Turns out we had dbo owning all the data at our last MSSQL setup, so the issue was how my login was mapped. Initially my login "User" was set to my username, but we changed it to dbo and everything worked. Because we had dbo as the owner schema on all the data (formatted like <database name>.DBO.<feature class name>, this made sense. I do not have to be in a sysadmin role for this solution to work.

0 Kudos