db_datareader

Question

db_datareader

charipg

SSCrazy Eights

Points: 9888
More actions
June 12, 2015 at 12:15 pm

#318948

user have db_datareader role in in a database.
But user is not able to see the storedprocedures.
any suggestion pls.

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

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

Jack Corbett SSC Guru Points: 184393 More actions · Answer 1

The db_datareader role is for granting users select access to user objects that have a SELECT permission. If you want users to be able to execute stored procedures you need to grant EXECUTE permissions on the procedure(s). In order to see the definition of a stored procedure you need to grant VIEW definition on the procedure(s). Typically the best way to do this is to create specific user database roles that include all permissions needed for the role.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Eric M Russell SSC Guru Points: 125522 More actions · Answer 2

If what you need is to grant execute permission on ALL procedures and visibility on ALL objects, then you can use the following database level commands. However, as Jack suggested, there are advantages to granting permissions to roles, and then adding users to roles. One advantage is that it makes managing permissions easier, and easy means more consistent and less chance of mistakes.

GRANT EXEC TO [User | Role];

GRANT VIEW ANY DEFINITION TO [User | Role];

If they need permission on ALL but a few objects, then you can follow that up by using the DENY statement, which the reverse of GRANT.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 3

charipg (6/12/2015)
user have db_datareader role in in a database.
But user is not able to see the storedprocedures.
any suggestion pls.

I have a different suggestion than the others... why is it necessary for the user to see the stored procedures to begin with?

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

Eric M Russell SSC Guru Points: 125522 More actions · Answer 4

Jeff Moden (6/12/2015)
charipg (6/12/2015)
user have db_datareader role in in a database.
But user is not able to see the storedprocedures.
any suggestion pls.
I have a different suggestion than the others... why is it necessary for the user to see the stored procedures to begin with?

Maybe in production it's not needed, but if the users are doing BI reporting, then they need to have visibility into stored procedure to setup proc call and parameters. But yes, application with static procedure calls, no ad-hoc reporting, only needs exec access on specific procedures.

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Jeff Moden SSC Guru Points: 1003865 More actions · Answer 5

Eric M Russell (6/12/2015)
Jeff Moden (6/12/2015)
charipg (6/12/2015)
user have db_datareader role in in a database.
But user is not able to see the storedprocedures.
any suggestion pls.
I have a different suggestion than the others... why is it necessary for the user to see the stored procedures to begin with?
Maybe in production it's not needed, but if the users are doing BI reporting, then they need to have visibility into stored procedure to setup proc call and parameters. But yes, application with static procedure calls, no ad-hoc reporting, only needs exec access on specific procedures.

Jeez... why don't they just call the proc with the "/?" parameter or simply read the documentation from their WIKI (heh... wait for it, wait... wait.... BWAAA-HAAAA-HAAAA-HAAAA!!!! 😉 )

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