2

I am using a GUI tool to upload data in my SQL Server 2016 database. The user being used for above task has been granted sysadmin permission I created a dedicated schema to store above data. This schema is owned and default to above said user. The issue is, when i upload data it stores in dbo schema instead of dedicated schema and i couldn't revoke sysadmin priv from this user.

Kindly let me know how to upload data to this dedicated schema without revoking sysadmin priv ?

Thanks

  • What do you mean by i couldn't revoke sysadmin priv from this user? It's a requirement that the user must be sysadmin or you had a problem revoking the sysadmin priv? What tool are you using? Maybe there's a config on that tool to specify the schema instead of using the default schema of the user. – Ronaldo Dec 17 '19 at 14:32
  • @Ronaldo, I am using ArcGIS tool to create a spatial table. I couldn't revoke the privilege because my synchronize job to linked server fails if i do so. – Faisal Alinn Dec 18 '19 at 5:14
  • 2
    This question had four votes to close when I encountered it. I believe your best option is to replace sysadmin with CONTROL SERVER as in the linked 'duplicate' Q & A. If not, please edit your question (don't just add comments) and we will consider reopening. – Paul White 9 Dec 18 '19 at 6:17
  • Maybe you should address the errors you get when the job fails after revoking the sysadmin priv. It's possible that there's no need to be sysadmin to run it (few jobs should be executed as sysadmin and it might not be one of them). – Ronaldo Dec 18 '19 at 13:31
  • Thanks @PaulWhitesaysGoFundMonica. Control Server worked in my case. The job is executing without any errors. – Faisal Alinn Dec 19 '19 at 5:13
8

The only way for a member of the sysadmin fixed server role to use a non-dbo schema is to explicitly qualify their object names with the schema name:

CREATE TABLE MySchema.TableName (...

instead of:

CREATE TABLE TableName (...

There is no way around this. The documentation for ALTER USER (in the "Remarks" section) even has a note for:

🛈 Important

The value of DEFAULT_SCHEMA is ignored if the user is a member of the sysadmin fixed server role. All members of the sysadmin fixed server role have a default schema of dbo.

This is most likely due to the fact that Logins (instance-level) that are members of the sysadmin fixed server role become the dbo User (database-level) in all databases. To verify this, log in as any non-sa Login that is a sysadmin and execute the following:

SELECT SYSTEM_USER AS [Login], CURRENT_USER AS [User];
  • Or to impersonate another user, but that probably wouldn't help here. – David Browne - Microsoft Dec 17 '19 at 16:14
  • @Solomon Rutzky, as i said initially, i am using a GUI tool to create a table. Your suggestion works if i have an option to use command. And yes you are correct, in the background a sysadmin user is counted as dbo. – Faisal Alinn Dec 18 '19 at 5:19

Not the answer you're looking for? Browse other questions tagged or ask your own question.