July 12, 2017 at 2:35 pm
I have given user permission to execute a Stored procedure but that stored procedure drops a specific table and recreates it again. Is there a way for me to grant user with rights to drop just that specific table? Because I certainly don't want him dropping objects in the DB.
July 12, 2017 at 2:45 pm
You can't give him access to just that table the permissions will be lost as soon as he drops it the first time, try looking at using EXECUTE AS in the stored procedure that runs as a user with permissions to drop tables.
July 12, 2017 at 2:54 pm
DROP is not a grantable permission.
Following roles allow you to drop a table:
dbo, schema owners, db_ddladmins, db_owner, sysadmin, people with CONTROL permission on the table, People with ALTER permission, table owners.
July 12, 2017 at 3:01 pm
what about setting up a proxy account so the user can execute the Stored procedure as if I am executing it. Does that work or do I really need to get some sleep?
July 12, 2017 at 3:14 pm
newdba2017 - Wednesday, July 12, 2017 3:01 PMwhat about setting up a proxy account so the user can execute the Stored procedure as if I am executing it. Does that work or do I really need to get some sleep?
That's kind of what ZZartin was suggesting, make the stored proc like:CREATE PROCEDURE dbo.MyProcName
WITH EXECUTE AS OWNER
AS
BEGIN
...
END
that way you just have to give the user permission to execute the stored proc, not any permissions for DDL or anything.
See this: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql
July 12, 2017 at 3:22 pm
Chris Harshman - Wednesday, July 12, 2017 3:14 PMnewdba2017 - Wednesday, July 12, 2017 3:01 PMwhat about setting up a proxy account so the user can execute the Stored procedure as if I am executing it. Does that work or do I really need to get some sleep?That's kind of what ZZartin was suggesting, make the stored proc like:
CREATE PROCEDURE dbo.MyProcName
WITH EXECUTE AS OWNER
AS
BEGIN
...
END
that way you just have to give the user permission to execute the stored proc, not any permissions for DDL or anything.
See this: https://docs.microsoft.com/en-us/sql/t-sql/statements/execute-as-clause-transact-sql
Excellent. Thanks everyone.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply