User cannot not see stored procedures

Question

Post reply

User cannot not see stored procedures

DBA-640728

SSChampion

Points: 12898
More actions
March 5, 2010 at 3:50 pm

#139127

hi guys, i was wondering if you can help me, I have two identical databases in different servers. I have a user with Read access permissions to these databases. In Database A the user can see 450 procedures, in DatabaseB the user can see only 4, i made sure the user has same permissions for both environments and he does, what else should i check for?

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

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

Steve Cullen SSCertifiable Points: 5598 More actions · Answer 1

Have you granted execute permissions to all the procedures to the user or a role he/she is a member of?

Converting oxygen into carbon dioxide, since 1955.

DBA-640728 SSChampion Points: 12898 More actions · Answer 2

in both environments , he is only a member of the role db_datareader for the DB.

DBA-640728 SSChampion Points: 12898 More actions · Answer 3

to be more specific, when the user runs this in one enviroment (A)

select count(1) from sys.procedures (nolock)

in Database Server A he gets around 400

in Server B same Database only 4

also if he tries to run sp_helptext 'proc name'

on Server B he gets the error

Msg 15009, Level 16, State 1, Procedure sp_helptext, Line 54

The object 'proc name' does not exist in database 'Expense_Ecu' or is invalid for this operation.

the proc is there in both Databases.

only db_datareader role assigned to user.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Login with that id and execute SELECT * FROM fn_my_permissions (NULL, 'DATABASE');

to see what permissions user has in db and compare them.

http://msdn.microsoft.com/en-us/library/ms176097.aspx

DBA-640728 SSChampion Points: 12898 More actions · Answer 5

thanks for your reply, i executed this in both environment.

in one db(A) he has

databaseCONNECT

databaseSELECT

databaseVIEW DEFINITION

in the other (B) only

databaseCONNECT

databaseSELECT

for the first one i went to the DB, db properties, permissions

his login , but i don't see the view definition checked.... what am i missing?

LutzM SSC Guru Points: 107049 More actions · Answer 6

Did you try

exec sp_helpuser 'user'

to see if they belong to different roles on the two db?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

DBA-640728 SSChampion Points: 12898 More actions · Answer 7

that is what it was, the user's role had a view definition, thank you all of you!!!! 😀

LutzM SSC Guru Points: 107049 More actions · Answer 8

Glad I could help. 🙂

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

homebrew01 SSC Guru Points: 55539 More actions · Answer 9

homebrew01

SSC Guru

Points: 55539

March 10, 2010 at 3:09 pm

#1131671

Thanks here too. I just had a need for this.