Re: Support team security
- From: "Jasper Smith" <jasper_smith9@xxxxxxxxxxx>
- Date: Sat, 9 Dec 2006 00:45:14 -0000
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!!
.
- Follow-Ups:
- Re: Support team security
- From: mswinarski@xxxxxxxxx
- Re: Support team security
- References:
- Support team security
- From: mswinarski@xxxxxxxxx
- Re: Support team security
- From: mswinarski@xxxxxxxxx
- Support team security
- Prev by Date: Re: SQL2000 UserPermissions for Database
- Next by Date: Re: View Permissions by User
- Previous by thread: Re: Support team security
- Next by thread: Re: Support team security
- Index(es):
Relevant Pages
|
