November 10, 2011 at 10:09 am
Hi,
If I grant execute permissions to a procedure, do I also need to grant permissions to any underlying tables that procedure uses? Or does the user(s) automatically get permissions to the table?
Is there a way to only give a user permissions to a proc, but not the underlying table?
I do not believe there is, but maybe there is something I am missing.
-Al
November 10, 2011 at 10:56 am
Al Cook (11/10/2011)
Hi,If I grant execute permissions to a procedure, do I also need to grant permissions to any underlying tables that procedure uses? Or does the user(s) automatically get permissions to the table?
Is there a way to only give a user permissions to a proc, but not the underlying table?
I do not believe there is, but maybe there is something I am missing.
-Al
Al just grant EXECUTE permissions. no need to grant more permissions.
SQL assumes that if the calling user has execute permissions, the proca can do whatever it need to to the undlying objects used inside the proc(unless the proc uses dynamic SQL), and also as long as all the objects reside inside the database.
what you are asking is very typical: an end user can call procedures, maybe SELECT from a VIEW, but has no access to the underlying objects.
Lowell
November 10, 2011 at 11:49 am
Thanks Lowell.
That is exactly how I believed it to work. I have somebody wokring on this issue in SQL 2008. However, they are using "msado" via Excel to load budget data.
The proc deletes data from a table and inserts new data. However, she does not want to give the users delete rights to the table, because they can get into the table and delete ALL records by accident... (not going thru the proc). When the procedure runs via msado / Excel, they receive an error because they no not have insert/delete rights to the table.
-Al
November 10, 2011 at 11:55 am
in that case, for that one specific proc, you want that proc to execute as some advanced user ,that really would have access to ADO or the linked server.
then the end user still only has EXECUTE to the proc
--the EXECUTE AS must be a user in the database i believe...not a login
CREATE procedure pr_CallBoostedSecurityProcess
WITH EXECUTE AS 'superman'
AS
BEGIN
dbcc freeproccache
END
in my example here, you could create a login name dsuperman, disable it, then may it a sysadmin and a user, for exmaple.
--create our super user
CREATE LOGIN [superman] WITH PASSWORD=N'NotARealPassword',
DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
--make our special user a sysadmin
EXEC master..sp_addsrvrolemember @loginame = N'superman', @rolename = N'sysadmin'
GO
--noone will ever login with this, it's used for EXECUTE AS, so disable the login.
ALTER LOGIN [superman] DISABLE
GO
USE [SandBox]
GO
CREATE USER [superman] FOR LOGIN [superman]
GO
USE [SandBox]
GO
EXEC sp_addrolemember N'db_owner', N'superman'
GO
--now create our procedure that runs under special priviledges instead of as the caller.
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply