Post reply

how to check for SP existence with only db_datawriter permissions

  • December 17, 2009 at 7:02 am

    #218748

    All --

    I need to know how to check for SP existence with only db_datawriter permissions.

    For example, I saw the excellent article...

    "

    How To Find SQL Server Objects

    By Ashish Kaushal, 2004/08/17

    http://www.sqlservercentral.com/articles/Advanced+Querying/howtofindsqlserverobjects/1446/

    "

    ...but I am still stuck because the Sql login that I use to check for SP existence is only a member of the following UserMappings...

    public, db_datawriter

    ...and, as such, that user does not have permissions to query sysobjects, (or so it seems because such a query always returns an empty result)...

    ...so, is there a workaround such that one CAN check for stored procedure existence with a db_datawriter?

    Please advise.

    Thank you.

    -- Mark Kamoski

  • December 17, 2009 at 7:13 am

    #1093471

    i'd say there was an error in assigning roles;

    how can you update something(db_datawriter) if you can never query it for the original values(db_datareader)

    i think you need to add db_datareader to the roles assigned to your usermapping.

    then you would be able to query the table with if exists()

    alternatively, maybe you can assign the ability to VIEW DEFINITION for the procs/functions;

    i think the syntax is Grant View Any Definition To MyUserGroup

    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!

  • December 17, 2009 at 8:03 am

    #1093514

    The roles that I am using are the built-in roles.

    To be precise, the login has the following mappings...

    public

    db_datareader

    db_datawriter

    ...and the login cannot get but an empty string from a query to sysobjects for SP existence.

    I am not sure about widening permissions and will have to check if our systems security team will allow that-- however, I would like to be able to do it without having to widen permissions and, it seems to me, that I should be able to check for SP existense with those UserMappings, should be able from a "logical, common sense point of view", IMHO.

    Ug.

    Not good.

    If you have more ideas, then please let me know.

    Thank you.

    -- Mark Kamoski

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

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