Contracting DBA - Permissions for initial analysis

Question

Post reply

Contracting DBA - Permissions for initial analysis

StephenSQL

Valued Member

Points: 64
More actions
February 11, 2025 at 1:02 am

#4534641

Accidental DBA here. After looking at all the work that needs to be done, I need additional help.
Right now I'm in the process of hiring a DBA contractor. What I'd like for this person to do, at least initially, is view maintenance plans, jobs, DMVs, etc., so they can perform their analysis without me having to grant them sysadmin.
I'm sure I'm not the first person looking to do this, but I didn't happen to find anything when searching the forums.
Would anyone be able to point to me to a resource if something like this already exists?
Thank you

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Abhinav Grasshopper Points: 14 More actions · Answer 1

Hi,

You may need to give following permissions

Grant VIEW SERVER STATE for DMVs , Ex Query - GRANT VIEW SERVER STATE TO [YourContractorUser];
Grant Access in MSDB to view Jobs

USE msdb;

CREATE USER [YourContractorUser] FOR LOGIN [YourContractorUser];

-- Add the user to the SQLAgentReaderRole

EXEC sp_addrolemember 'SQLAgentReaderRole', 'YourContractorUser';

Thanks,
Abhinav

Abhinav Grasshopper Points: 14 More actions · Answer 2

Hi,

You may need to give following permissions

Grant VIEW SERVER STATE for DMVs , Ex Query - GRANT VIEW SERVER STATE TO [YourContractorUser];
Grant Access in MSDB to view Jobs

USE msdb;

CREATE USER [YourContractorUser] FOR LOGIN [YourContractorUser];

-- Add the user to the SQLAgentReaderRole

EXEC sp_addrolemember 'SQLAgentReaderRole', 'YourContractorUser';

Thanks,
Abhinav

StephenSQL Valued Member Points: 64 More actions · Answer 3

Thanks, Abhinav. I'll review and give that a shot.

Abhinav Grasshopper Points: 14 More actions · Answer 4

Sure, let me know if you need more detailed solution /or script. I can help. Thanks.

Thanks,
Abhinav

Jeff Moden SSC Guru Points: 1004546 More actions · Answer 5

One of the first things you should do is to download Brent Ozar's first responder kit and run it.

Here's where you can get the kit...

https://www.brentozar.com/first-aid/

Here's where you can learn to use the kit most effectively... each "start" button has a 'tube for it and it's all free.

https://training.brentozar.com/p/how-i-use-the-first-responder-kit

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

StephenSQL Valued Member Points: 64 More actions · Answer 6

Hi Jeff, good recommendation. That is actually one of the first things I did, and the results highlighted just how much work needs to be done. The blitz scripts are great.

Thanks!

Contracting DBA - Permissions for initial analysis

Cookies on SQLServerCentral