April 29, 2009 at 4:05 am
Hi,
I'm tring to make a stored procedure that goes to the master ans makes a select.
I want that this procedure is exectuted in the context of the "SA", because i do not want that this user have select permissions on the master database.
when i create the stored procedure, as below, i receive and error.
Can you please help? tks.
create proc MaquinasLigadas(@host varchar(100))
WITH EXECUTE AS 'sa'
as
select distinct(hostname) from master.dbo.sysprocesses
where db_name(dbid) ='SGCT' and dbid <>0
and spid<>@@spid and hostname <> @host
order by hostname
go
error:
Server: Msg 156, Level 15, State 1, Procedure MaquinasLigadas1, Line 2
Incorrect syntax near the keyword 'EXECUTE'.
April 29, 2009 at 5:47 am
You prosted this into the SQL 2000 section, so I assume you're using this version.
But EXECUTE AS was implemented with SQL 2005.
[font="Verdana"]Markus Bohse[/font]
April 29, 2009 at 6:18 am
hummm.... tks...
The problem is this:
I have some employees that i do not want that they have permissions on the Master database.
But i need that they run a procedure. Do you have any idea of how can i do this in SQL Server 2000?
tks,
Pedro
April 29, 2009 at 6:38 am
Every user has automatically access to the master database via the guest account. And yes, that is necessary, don't remove the guest account.
So in your case, I would simply give EXECUTE permission on your procedure to the public role.
[font="Verdana"]Markus Bohse[/font]
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply