Re: Support team security

Thanks Jasper! This is good stuff!!


I have actually had to re-visit the Agent scheduler thing. Apparently
it does not work as well as I had thought.

is there a way that you can setup a user so that they can see the job
scheduler, with all of the attributes. If they can launch jobs, that
would be a bonus.
-ms




Jasper Smith wrote:
It's not possible using Management Studio as it requires you be a sysadmin
to view Linked Server properties (this is a tool requirement not a SQL
requirement) adn securityadmin to see errorlogs. However, you can get the
properties of a linked server by querying the sys.servers catalog view which
by default all logins have access to. In order to view sys.linked_logins or
sys.remote_logins you'll need to wrap them in a procedure running in a
different context and give them execute permissions on it e.g

create procedure dbo.usp_ReadLogins
(
@type varchar(6) = 'linked'
)
with execute as 'dbo'
as
set nocount on
if lower(@type) = 'linked'
select * from sys.linked_logins
else if lower(@type) = 'remote'
select * from sys.remote_logins
else
raiserror('Parameter @type must be ''linked'' or ''remote''',16,1)
go

grant exec on dbo.usp_ReadLogins to Support
go

Same goes for reading the errorlog. Either create a file share for the Log
folder and give them read access or wrap a call to xp_readerrorlog in a
procedure (you don't want to give access to xp_readerrorlog directly as it
can be used to read any file the SQL Server service account has access to
and not just the error logs) e.g

create procedure dbo.usp_ReadLog
(
@number int = 0
)
with execute as 'dbo'
as
set nocount on
declare @logs table(ArchiveNo int,Date datetime,FileSize int)
insert @logs exec dbo.xp_enumerrorlogs

if not exists(select * from @logs where ArchiveNo = @number)
begin
raiserror('%d is not a valid errorlog number',16,1,@number)
return
end
else
exec dbo.xp_readerrorlog @number
go

grant exec on dbo.usp_ReadLog to Support



--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com


<mswinarski@xxxxxxxxx> wrote in message
news:1165514532.652351.63450@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

mswinarski@xxxxxxxxx wrote:
We have a group of folks that handles much of our applicaiton support.
We would like them to have security to view (but not alter or create)
the following:

Properties of the Linked Servers
SQL Server Agent Job Schedule and Job history
SQL Server Logs


Does anyone know what permissions to grant in order to do this?? Can
this even be done?


I figured out one of them! the user can view the "SQL Server Agent Job
Schedule and Job history" without being able to modify the jobs by
adding them to the SQLAgentReaderRole in the msdb db.

One down, two to go!!


.

Relevant Pages

  • Re: DTS Package/Job anomalies
    ... First line of defence is to see whether MS SQL Server and SQL Server Agent ... Not add anything to the SQL Server Logs ... DTS package and then schedule it, or even create a new Maintenance Package ...
    (microsoft.public.sqlserver.dts)
  • Re: Joins and dynamic filters with different users
    ... login of the sql server agent, which is the application that logs into the ... Paul Ibison SQL Server MVP, ...
    (microsoft.public.sqlserver.replication)
  • Re: Error re-installing SQL Server 2005
    ... i actually was able to export the reg entries from my ... SQL Server Setup could not connect to the database service for server ... Refer to server error logs and setup logs for ... For details on how to view setup logs, ...
    (microsoft.public.sqlserver.setup)
  • Re: Cluster will not fail over.
    ... Could not recover the master database! ... If you intend to stay on SP3 (for SQL Server), you should at least apply the ... >> From SQL server logs ...
    (microsoft.public.sqlserver.clustering)
  • Re: Error re-installing SQL Server 2005
    ... SQL Server Setup could not connect to the database service for server ... Refer to server error logs and setup logs for more ... For details on how to view setup logs, ... Kevin Struckhoff ...
    (microsoft.public.sqlserver.setup)
Loading