June 17, 2002 at 7:01 am
We have 2 stored procedures A and B and a user is assigned a role which has permission to execute A and not B.
If the code within A executes B and A and B are within the same DB this works fine but if they are not in the same DB
then a lack of permissions on B error is generated. Is there a work around for this situation without granting the role permissions
to execute B explicitly? An example is xp_CmdShell, if you didn't want to grant a user permission to execute it but you do want them to be
able to execute an SP which calls it internally.
Regards,
Andy Jones
.
June 17, 2002 at 8:46 am
I tried this and the only thing I had to do was set the user account up in public on the database, nothing more. Works fine for me, and when they access the DB directly they get permission denied on SP B in the other DB.
My Ex.
user testMe no password
DB1
TestDB
CREATE PROC ip_A
AS
EXEC TestDB2.dbo.ip_B
GO
Grant execute permission to testMe
----
DB2
TestDB2
CREATE TABLE tblX (
[myint] [int] NOT NULL
)
CREATE PROC ip_B
AS
SELECT * FROM tblX
GO
Set testMe up in default public role
Run testDB.dbo.ip_A
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 17, 2002 at 9:51 am
Thanks for the response. I thought this was how it should work but in the case you outlined below I am still getting permission denied on sp ip_B??? It works if I grant public the permission to execute ip_B but not otherwise, any ideas?
Regards,
Andy Jones
.
June 17, 2002 at 10:25 am
What version of SQL and ServicePack level on it? I tested on SQL 2000 SP 2. Can you post A and B code, it may be something to do with what you are doing.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 21, 2002 at 5:09 am
Sorry for the delay...It's SQL2K Standard with SP1.
I want a user to be able to execute certain procedures which execute master..xp_CmdShell but not master..xp_CmdShell itself, so I ran
the following.
--Add the login
sp_addlogin 'user1','user1','a','British English'
GO
use a
GO
--Create an sp
create procedure sp_1 as
exec master..xp_cmdshell 'dir c:\'
GO
--Grant user access to DB
sp_grantdbaccess 'user1'
GO
--Add the role
sp_addrole 'role1'
GO
--Add user to the role
sp_addrolemember 'role1', 'user1'
GO
--Grant permissions to the role
grant exec on sp_1 to role1
use master
GO
--Grant user access to DB
sp_grantdbaccess 'user1'
GO
When I run exec sp_1 in database a I get:-
Server: Msg 229, Level 14, State 5, Procedure xp_cmdshell, Line 4
EXECUTE permission denied on object 'xp_cmdshell', database 'master', owner 'dbo'.
Stored Procedure: a.dbo.sp_1
Return Code = 0
Is this issue becuase it's an extended sp? Is there a way to accomplish what I'm trying to do?
Regards,
Andy Jones
.
June 21, 2002 at 5:44 am
From BOL
quote:
Execute permissions for xp_cmdshell default to members of the sysadmin fixed server role, but can be granted to other users.
You cannot bypass this, you must either grant rights to xp_cmdshell or work around it. One work around is to insert the return from xp_cmdshell into a table and allow the user to select the table. Then you set up a job or process to periodically update that table.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 21, 2002 at 10:13 am
Antares is correct. When you execute certain stored procedures (system ones) permissions are rechecked for security purposes. I think the solution proposed above is a great one.
Steve Jones
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply