Need to grant execute permissions on all sprocs of a specific database to all server logins of a SQL instance without explicitly granting exec permissions on each individual object

Question

Post reply

Need to grant execute permissions on all sprocs of a specific database to all server logins of a SQL instance without explicitly granting exec permissions on each individual object

Viewing 5 posts - 16 through 19 (of 19 total)

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

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 1

Marios Philippopoulos (1/17/2012)
Even "best practices" should be looked at with a critical eye.

Precisely why I encapsulated "best practices" in quotes. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Jeff Moden (1/17/2012)
GSquared (1/17/2012)
Jeff Moden (1/16/2012)
rlevine (1/16/2012)
Perfect!!
I was able to install my CLR functions into a db called Library and then use these scripts to enable having all users call the functions without needing to create a new user on the Library dB for all of my users.
use Library;
go
grant connect to guest
grant execute to guest
grant select to guest
go
PUBLIC didn't work? I ask only because one of the usual "best practices" for security reasons is to disable the GUEST account.
Public is a role, not an account. You'd have to make sure everyone was in that role, and apparently that's not an option here, per a prior post.
This whole thing is going into realms, security-wise, that I'd NEVER allow on any server I'm responsible for, but apparently it's what's needed here.
Yep... I know Public is a role and, by default (IIRC), everyone is a member of that role at creation time.

Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

GSquared SSC Guru Points: 260824 More actions · Answer 3

Marios Philippopoulos (1/17/2012)
GSquared (1/17/2012)
Jeff Moden (1/16/2012)
rlevine (1/16/2012)
Perfect!!
I was able to install my CLR functions into a db called Library and then use these scripts to enable having all users call the functions without needing to create a new user on the Library dB for all of my users.
use Library;
go
grant connect to guest
grant execute to guest
grant select to guest
go
PUBLIC didn't work? I ask only because one of the usual "best practices" for security reasons is to disable the GUEST account.
Public is a role, not an account. You'd have to make sure everyone was in that role, and apparently that's not an option here, per a prior post.
This whole thing is going into realms, security-wise, that I'd NEVER allow on any server I'm responsible for, but apparently it's what's needed here.
Depends what you give access on. If, for example, the Library db contains only UDFs for regular expressions, then the risk of harm by allowing guest access on these objects is minimal. It's a balance between what is safe to allow and what will give the most savings in terms of future maintenance.
Even "best practices" should be looked at with a critical eye.

I agree that it's probably safe now. The problem is scope-creep on things like this. Are you familiar with the parable about how the camel got into the tent?

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Jeff Moden SSC Guru Points: 1004414 More actions · Answer 4

GSquared (1/17/2012)
Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.

Ah... sorry. I misunderstood the original problem.

Considering how easy it is to have the system generate a script to grant access to databases, I wouldn't simply use the "Guest" account. There's no tracability if everyone uses the "Guest" account. Yes, I also realize that's the way that most applications also work but you know what's next... I'm against that, as well. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 5

Jeff Moden (1/17/2012)
GSquared (1/17/2012)
Yep. But that doesn't grant access to a database their account/group hasn't been given access to, except as "guest". So guest has to be enabled.
Ah... sorry. I misunderstood the original problem.
Considering how easy it is to have the system generate a script to grant access to databases, I wouldn't simply use the "Guest" account. There's no tracability if everyone uses the "Guest" account. Yes, I also realize that's the way that most applications also work but you know what's next... I'm against that, as well. 😉

Yep. Which is why I consider this whole plan risky. If it's worth it for the reward in this case, that's not something I can judge, but it does have liabilities that have been pointed out.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon