Read Access user cannot see Stored Procs

Question

Read Access user cannot see Stored Procs

schwizla

SSCrazy

Points: 2668
More actions
July 16, 2007 at 4:43 am

#179694

Hi I have created a READ ONLY user account with db_datareader and db_denydatawriter priveleges to all user databases. However when logged in as this user, I cannot list or see any of the user Stored Procedures in a database. Interestingly though I can see the system SP's.
Can someone please advise if I have missed something out? In SQL 2000 this works fine and I can see all objects but I am sure that 2005 has got added security thats preventing me from viewing SP's
Thanks in advance,
Siz

Viewing 11 posts - 1 through 10 (of 10 total)

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

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 1

With db_datareader they will be able to read the data in the tables only if you need to give them permission to see the procedure you have to give view definition on that procedure so that use wil be able to see the content of the procedure.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

schwizla SSCrazy Points: 2668 More actions · Answer 2

Sugesh,

thanks for the response. Excuse my lack of knowledge on this as I am failry new to SQL2005. How exactly do I do this and is it possible to apply this to all SP's in one go or does it have to be done on an indivdual SP one at a time?

Thanks, Siz

bledu SSChampion Points: 12335 More actions · Answer 3

i am assuming that you created the datareader user for use in an application, if that is the case then in sql why do you want to view the stored proc definition. just logon as your regular dev user.

unless if you are saying that u cannot execute the stored procs from within your application.

then you need to grant execution rights to the user:

GRANT EXECUTE ON cp_SomeName TO myDataReader

Everything you can imagine is real.

schwizla SSCrazy Points: 2668 More actions · Answer 4

I created the read only reader to allow DEV team to query the database. But they also require to see the list of SP's or even view the SP contents for all SP's in the database. In SQL 2000 this works as long as the user is given db_datareader role.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 5

you need to grant the permission to stored procedures so that they can view the content of procedure

if there anre n number of procedures

select

'grant view definition on '+name+' to username' from sysobjects where xtype='P'

you can use this simple select as a starting point

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com

schwizla SSCrazy Points: 2668 More actions · Answer 6

thank you very much for this. I have been invetsigating and I was inteding to run the follwing command

use

[dbname]

GO

GRANT

VIEW DEFINITION TO [devusers]

GO

This way as new SP's\functions and other objects are created, the DEV guys should be able to view them in a READ ONLY state. Do you see any security issues with approach?

bledu SSChampion Points: 12335 More actions · Answer 7

on which platform are we talking abt

i.e. development, test, production

my matrix for dev security would be

dev - full access

test - partial access

production - no access

Everything you can imagine is real.

schwizla SSCrazy Points: 2668 More actions · Answer 8

belive it or not, this is LIVE

before you say anything I know I know! but this request has come from high up above and all risks are highlighted and my back is covered. I am just trying to minimise the impact as much as possible and grant READ access only.

bledu SSChampion Points: 12335 More actions · Answer 9

the request came from high up, then tell them that this is a NO NO.

replicate the live on to Test and have the developers doing their thing there.

Everything you can imagine is real.

Sugesh Kumar One Orange Chip Points: 27311 More actions · Answer 10

Yes no access for developers/testers in the production box what if they tamper your data.

Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com