June 8, 2022 at 3:35 pm
Is it possible to grant a regular user (domain account) read/see user accounts and SQL agent jobs?
We are doing an upgrade and the application admin wants to see user accounts, linked servers and sql agent jobs (job properties also?) and compare from old SQL server to the new SQL server.
Short of granting sysadmin role, I am not aware of any other way. ¯\_(ツ)_/¯
Any help is greatly appreciated.
Thanks
June 8, 2022 at 4:20 pm
Create a stored procedure WITH EXECUTE AS OWNER.
(being sure to secure the procedure so only the intended people can execute it)
June 8, 2022 at 5:29 pm
Or run the stored procedure yourself and email them the results in a spreadsheet.
--Jeff Moden
June 8, 2022 at 5:38 pm
For each database do ( including msdb - will allow view jobs )
GRANT VIEW DEFINITION TO [<USERNAME>]
And
USE [msdb]
GO
ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [<username>]
GO
USE [msdb]
GO
ALTER ROLE [SQLAgentUserRole] ADD MEMBER [<u8sername>]
GO
June 16, 2022 at 8:54 pm
Thanks, I will give this a try this week.
June 16, 2022 at 11:49 pm
For each database do ( including msdb - will allow view jobs )
GRANT VIEW DEFINITION TO [<USERNAME>]
And
USE [msdb] GO ALTER ROLE [SQLAgentReaderRole] ADD MEMBER [<username>] GO USE [msdb] GO ALTER ROLE [SQLAgentUserRole] ADD MEMBER [<u8sername>] GO
if decide to do the above then read this https://qa.sqlservercentral.com/forums/topic/sqlagentreaderrole-can-create-its-own-jobs-what-is-ms-thinking - you don't really need any user creating jobs on your server (even if they won't be able to do much with those jobs)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply