March 4, 2022 at 6:26 pm
Hi,
I have a Windows AD Group which I have created a SQL Login for and gave this group CONNECT and EXECUTE permission on a database.
I would like to allow members of this AD Group to be able to GRANT EXECUTE permission on all objects within this database to other users.
I have tried the following to no avail:
USE DB1
GO
GRANT EXECUTE TO [Domain\ADGroup] WITH GRANT OPTION
I can give the permission to an individual object within the database, just not all objects within the database, using the following:
USE DB1
GO
GRANT EXECUTE ON dbo.StoredProc1 TO [Domain\ADGroup] WITH GRANT OPTION
As that user I can GRANT EXECUTE to another user with the following:
EXECUTE AS LOGIN = [Domain\User1]
USE DB1
GO
GRANT EXECUTE ON dbo.StoredProc1 TO [AnotherUser] AS [Domain\ADGroup]
Is there a way to do this at the database level so User1 can GRANT EXECUTE to another user for all objects within DB1?
March 4, 2022 at 6:59 pm
If your code works for a specific object, can you grant the permission on a schema / all schemas, rather than specific objects?
GRANT EXECUTE ON SCHEMA::[dbo] TO [Domain\ADGroup] WITH GRANT OPTION
March 4, 2022 at 7:06 pm
You can do just
GRANT EXECUTE TO [Domain\ADGroup]
But you may not really want to do that.
----------------------------------------------------
March 4, 2022 at 7:58 pm
I did try the GRANT on SCHEMA but alas it did not work. When I then try to GRANT EXECUTE, on the StoredProc to another user, under the users context I get the error:
Cannot find the object 'StoredProc', because it does not exist or you do not have permission.
March 5, 2022 at 7:31 pm
And what was the result? What exact message do you get?
How do you absolutely know the users do not already have permission?
OR , Is there another permission setting denying this group access to any one resources used by any of the stored procedures?
See if this helps any :
https://www.mssqltips.com/sqlservertip/6394/understanding-sql-server-ownership-chaining
----------------------------------------------------
March 7, 2022 at 6:59 pm
If you grant execute rights on the schema to the group - then members of that group would all have the ability to execute the procedures in that schema. There wouldn't be a need to use WITH GRANT to allow them to grant execute rights on those procedures.
If the users all have there own schema - and they own the schema, then they already have the ability to grant permissions on anything in that schema.
It really isn't clear what you need to do - or why you need to use WITH GRANT option. Can you elaborate on this requirement?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply