Post reply

Allow login to view information_schema views?

  • December 11, 2008 at 9:45 am

    #196912

    I would like to be able to let our developers query the information_schema views (like information_schema.routines) in some of our production databases. Right now, it only let's them see objects that they have permissions to...

    i.e. if they have been granted execute on a procedure, they can see it in the view, and they can;t see any other user created procedures, even those in the dbo schema.

    This is interesting becauase they can do a sp_helptext on any procedure, but they can't retrieve a listing of the procedures from the information_schema.routines view.

    I've googled this, but I can't find anything that tells me how to allow them to query the views. Any ideas?

    More info: I created a SQL login and it is in the public server role. I added itas a user to a database, and put it in the public and db_datareader database role. Still can't see anything in information_schema.routines except for about 30 dt_ routines.

    Chris

  • December 11, 2008 at 4:52 pm

    #911396

    It has been 7 hours since you posted your question with no answers.

    Let me ask:

    Have you looked at:

    Impersonation

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/scsql9/html/dc049e56-b210-47f9-815a-a03b9ef7b6ba.htm

    and / or the "Execute As"

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/2c7004bf-9385-429e-a616-9bd7a7b19978.htm

    subjects in Books on Line?.. It not they may give you some sort of a hint as to how to do what you want to do.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • December 12, 2008 at 7:33 am

    #911635

    Neither of those choices will allow a user without execute rights to a stored procedure to find it in a listing by querying information_schema.routines or sysobjects.

    This is stemming from having developers that need to be able to do an sp_helptext on stored procedures in our production system, but they should not have execute rights. I've just been tasked with finding a solution, not determining the 'why' behind the need for it.

    I still think it is odd that these logins can do a sp_helptext on a specific stored procedure, but they can't run a select * from information_schema.routines and get a listing including that procedure without having execute permissions on the procedure. I know it's more secure that way, but that doesn;t help when people are saying - "I could do it on SQL Server 2000"

  • December 12, 2008 at 7:42 am

    #911647

    i thought if you create a group, and add this command, then your developers, if they belong to that group, can view the definition of any of the objects int he database, but since they are not datareaders or datawriters, they can't fiddle with the data:

    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 12, 2008 at 7:50 am

    #911653

    You can create a stored procedure and use the execute as clause. In then you can grant permissions to use this procedure to anyone that needs to be able to see all the procedures. Here is the example:

    create procedure ShowAllPorcedures

    with execute as self

    as

    select * from information_schema.routines

    go

    grant execute on ShowAllPorcedures to public

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • December 12, 2008 at 7:54 am

    #911656

    Lowell's suggestion worked beautifully. Thanks.

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

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