Hi, We are using SQL Server 2016. There is a request by a leading Network Monitor Support staff for sysadmin privilege (The database log in is already dbo of a the database used) He says that according to his previous experience, dbo privilege will corrupt the database and has to run a repair of the database as applying new patches require updating schema. Is it correct ? May I also ask how do we repair a database ? Thanks

What they actually mean is more likely they cannot do upgrades, without full sysadmin rights. Many application vendors expect to have full sysadmin rights to the database server and do not work unless they do. Normally there is not a valid reason after installation to have sysadmin rights unless they also manage user access on the server. I would ask them exactly why they need sysadmin rights. What exactly is the need they cannot do as "dbo".

Hi @TonyJK , Full agree with others. What did they want to do when they asking for admin privilege? Upgrade SQL server instance? Or others? Sysadmin can perform any activity in the server, we suggest you do not give admin permission to others. For the permissions to alter the schema of SQL DB, please read the permission part of this MS document ALTER SCHEMA (Transact-SQL) . If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Access right for updating database schema ?

Accepted answer

Tom Phillips 17,716 Reputation points

2022-01-28T12:59:53.36+00:00

What they actually mean is more likely they cannot do upgrades, without full sysadmin rights. Many application vendors expect to have full sysadmin rights to the database server and do not work unless they do. Normally there is not a valid reason after installation to have sysadmin rights unless they also manage user access on the server.

I would ask them exactly why they need sysadmin rights. What exactly is the need they cannot do as "dbo".
Please sign in to rate this answer.
TonyJK 876 Reputation points

2022-01-28T22:55:18.577+00:00

Dear Tom,

Would it be a good idea to grant sysadmin right to him when he needs to perform an upgrade again in the future. Then revoke it after he finishes the upgrade ?

Thanks

Erland Sommarskog 101.8K Reputation points MVP

2022-01-28T22:57:57.123+00:00

Well, we don't know what this guy really wants to do. I would first investigate whether he really needs sysadmin.

Sure, you can make him sysadmin for the day, but who knows, maybe he creates an account for him to use a rainy day? Obviously, it matters here if you trust him(*) and how sensitive the system is.

(*) Personally, I would have low faith in persons make suggestions as in your original post.

TonyJK 876 Reputation points

2022-01-29T01:59:01.167+00:00

Neither do I. Especially the message is conveyed by my colleague.

Erland Sommarskog 101.8K Reputation points MVP

2022-01-29T11:01:37.39+00:00

Seems like this is morphing into to a discussion about professional advice. You definitely need to go and dig up more information and talk with this guy directly to find out what this is all about. You may find that your question will be completely different after that.

Tom Phillips 17,716 Reputation points

2022-01-29T15:00:09.59+00:00

As Erland said, you can. But without knowing that he really wants or is trying to do, I would not.

Normally we tell people like that "No, but I am a sysadmin and can run any command for you." Then you can review what the requested query.

This is why I make it a policy to install vendor software on its own instance on their own VM whenever possible. Then you can give them sysadmin to their own instance without "much" fear of them messing up something else.

Erland Sommarskog 101.8K Reputation points MVP

2022-01-29T16:49:56.183+00:00

Normally we tell people like that "No, but I am a sysadmin and can run any command for you." Then you can review what the requested query.

This is why I make it a policy to install vendor software on its own instance on their own VM whenever possible. Then you can give them sysadmin to their own instance without "much" fear of them messing up something else.

Excellent ideas both!

TonyJK 876 Reputation points

2022-01-29T23:39:44.093+00:00

Dear fellows,

I make the same suggestion.

However, vendor says that SQL Server should be installed on different machine other than the server running SW.

Besides, my supervisor says it will cost extra money for SQL Server License.

Thanks
Sign in to comment

Access right for updating database schema ?

1 additional answer