SQL Server 2017 permissions.

Question

Hi everyone!

Is it possible to perform the commands below on SQL Server 2017 not belonging to the sysadmin or db_owner roles?

1. DBCC CHECKDB ([master]) WITH ALL_ERRORMSGS;
2. DBCC SHRINKDATABASE ();
3. DBCC UPDATEUSAGE ();
4. EXEC sp_cycle_errorlog;
5. EXEC sp_updatestats;
6. FROM sys.database_files, sys.dm_exec_requests and sys.dm_exec_sessions.

Hope I was clear enough.

Answer 1

Hi @Doria ,

According to MS document, i listed the permissions of performing below commands.

1. DBCC CHECKDB ([master]) WITH ALL_ERRORMSGS; ------- Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
2. DBCC SHRINKDATABASE ();------Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
3. DBCC UPDATEUSAGE ();------Requires membership in the sysadmin fixed server role or the db_owner fixed database role.
4. EXEC sp_cycle_errorlog;------Execute permissions for sp_cycle_errorlog are restricted to members of the sysadmin fixed server role.
5. EXEC sp_updatestats;------ To run sp_updatestats, the user must be the owner of the database (the dbo, not just member of the role db_owner) or to be member of the sysadmin fixed server role.
6. sys.database_files------ Requires membership in the public role. sys.dm_exec_requests ------ If the user has VIEW SERVER STATE permission on the server, the user will see all executing sessions on the instance of SQL Server; otherwise, the user will see only the current session. sys.dm_exec_sessions.------ SQL Server: Requires VIEW SERVER STATE permission on SQL Server to see all sessions on the server.
   SQL Database: Requires VIEW DATABASE STATE to see all connections to the current database.

> Is it possible to perform the commands below on SQL Server 2017 not belonging to the sysadmin or db_owner roles?

No, it is not possible. We can check this from above information.

Best regards,
Cathy

---

If the response is helpful, please click "Accept Answer" and upvote it.
Hot issues October--Users always get connection timeout problem when using multi subnet AG via listener. Especially after failover to another subnet

Answer 2

I'm not sure what applies to all of these commands. I guess I could look them up in Books Online. But, wait! So could you!

What I know on the top of my head is the sys.dm_exec_sessions/requests requires VIEW SERVER STATE.

Answer 3

Thank you guys!

Thank you Cathy for your patience, effort and for your response. I'll try to use the documentation next time, I'm sorry.

Regards.

Answer 4

If you are asking about permissions because you're going to grant someone sysadmin and/or VIEW SERVER STATE, then no special permissions are needed at all, as long as the user doesn't need ad hoc access to any of those commands or DMVs. You can use Module Signing (i.e. signing code with certificates) to hide the actual command / resource in a stored procedure or function that the user can execute, and then grant that stored procedure or function the required permission. This is done by creating a certificate, signing the relevant code with that certificate, and then associating that certificate with a user and/or login (that can't log in or be impersonated) that has the required permission. This way, the actual user can only access the protected resource(s) via the code that you provided and control. For examples on how to accomplish this, please see the following posts of mine (the Server-level post even uses VIEW SERVER STATE as the example):

• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Server-level
• Safely and Easily Use High-Level Permissions Without Granting Them to Anyone: Database-level