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

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

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
January 13, 2012 at 11:24 am

#262817

I have a certain database in a SQL instance that serves as just a library of code (sprocs and UDFs) and needs to be freely accessed from code on other databases in the same instance.
This is a generic library that needs to be made accessible to all logins on the server.
Say userA is executing sprocA on databaseA and within sprocA there is a reference to a sproc in the library db.
userA should be able to execute that portion of the code - the portion referencing the library-db object.
How can a set up this access without having to explicitly grant EXEC permissions on each sproc/UDF in the library db to specific logins/groups?
I tried to add the public role to db_owner in library db but it gave me an error.
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 15 posts - 1 through 15 (of 19 total)

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

GSquared SSC Guru Points: 260824 More actions · Answer 1

Create a group called something like "LibraryExecutors" (sounds bad, you might think of a better name), put everyone in it, grant that group execute rights in your library database. Not db_owner, use Grant Execute.

Something like:

CREATE ROLE [db_executor];

grant execute to db_executor;

EXEC sp_addrolemember N'db_executor', N'MyNewGroup';

- 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

rlevine Say Hey Kid Points: 672 More actions · Answer 2

Althout that solution will work, it still would require one to add every user created (even in the future) to that group. Is there any way to allow all users to access the "library" db without specifically adding priviliges for each and every user?

GSquared SSC Guru Points: 260824 More actions · Answer 3

You could simplify to:

GRANT EXEC TO PUBLIC;

I'm not sure I'd go that far in any database I'm responsible for, but it should work.

- 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

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 4

One of the way to do it is to enable the guest user :

Use Your_db

go

grant connect to guest

grant execute to guest

go

azdzn SSCarpal Tunnel Points: 4128 More actions · Answer 5

GSquared (1/13/2012)
You could simplify to:
GRANT EXEC TO PUBLIC;

I think you still have to create a user in the database with that.

GSquared SSC Guru Points: 260824 More actions · Answer 6

azdzn (1/13/2012)
GSquared (1/13/2012)
You could simplify to:
GRANT EXEC TO PUBLIC;
I think you still have to create a user in the database with that.

Use the guest account.

Edit: Just noticed you suggested about the same thing. Either method works.

- 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

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 7

azdzn (1/13/2012)
One of the way to do it is to enable the guest user :
Use Your_db
go
grant connect to guest
grant execute to guest
go

Thank you all for the input, it is appreciated.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

rlevine Say Hey Kid Points: 672 More actions · Answer 8

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

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 9

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.

--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 10

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.

- 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: 1004318 More actions · Answer 11

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.

--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)

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 12

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.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

rlevine Say Hey Kid Points: 672 More actions · Answer 13

No, public did not work without creating a user in the target db first, which is what I am trying to avoid.

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

rlevine (1/17/2012)
No, public did not work without creating a user in the target db first, which is what I am trying to avoid.

Ok... thanks for the feedback.

--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)