User unable to view stored procedure from Management studio

  • Hi,

    I have granted db_datareader database role to a user in production.

    With this, user is able to view the tables but NOT stored procedures.

    What privileges need to be granted to view stored procedures also?

  • for a single stored procedure, you would grant view definition on the User in the database(or the role/windows group)

    GRANT VIEW DEFINITION ON dbo.DelimitedSplit8K to ClarkKent;

    GRANT VIEW DEFINITION ON dbo.DelimitedSplit8K to MyReadGroup;

    GRANT VIEW DEFINITION ON dbo.DelimitedSplit8K to [myDomain\SQLUserGroup];

    to let them see the definition of anything(all functions /views/procedures, it's very similar

    GRANT VIEW DEFINITION to ClarkKent;

    GRANT VIEW DEFINITION to MyReadGroup;

    GRANT VIEW DEFINITION to [myDomain\SQLUserGroup];

    you can grant a LOGIN access to view definitions in any database, regardless, like this:

    GRANT VIEW ANY DEFINITION to [mydomain\lowell]

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • thank you so much

Viewing 3 posts - 1 through 2 (of 2 total)

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