GRANT permission WITH GRANT at database level for an AD Group

Question

GRANT permission WITH GRANT at database level for an AD Group

SteveLund

SSC Enthusiast

Points: 184
More actions
March 4, 2022 at 6:26 pm

#3993310
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?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Ed B Hall of Fame Points: 3615 More actions · Answer 1

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

MMartin1 One Orange Chip Points: 27764 More actions · Answer 2

You can do just

GRANT EXECUTE TO [Domain\ADGroup]

But you may not really want to do that.

----------------------------------------------------

SteveLund SSC Enthusiast Points: 184 More actions · Answer 3

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.

This reply was modified 2 years, 1 month ago by SteveLund.

SteveLund SSC Enthusiast Points: 184 More actions · Answer 4

MMartin1 wrote:

You can do just
GRANT EXECUTE TO [Domain\ADGroup]
But you may not really want to do that.

This is what I was doing with this:

USE DB1
GO
GRANT EXECUTE TO [Domain\ADGroup] WITH GRANT OPTION

MMartin1 One Orange Chip Points: 27764 More actions · Answer 5

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

----------------------------------------------------

Jeffrey Williams SSC Guru Points: 90244 More actions · Answer 6

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