April 9, 2010 at 10:40 am
We have a dev who would like to query info_schema to find a list of tables in the app. However, the stored procedure he wrote just returns an empty result set.
From some research, it seems it will only return a list of tables that the user has some sort of access to.
The problem here is the app was developed such that the user has NO table access. All table manipulation is handled by stored procedures.
Several places, I read that "By default, info_schema will only return data user has access to". Does that mean there's a way to change that default behavior, short of giving the user read access to the tables?
April 10, 2010 at 1:52 pm
ask your DBA to run query as either "sa" or "dbo" on target database.
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.April 11, 2010 at 1:05 am
have a job created that populates a user table so the user can access the data.
April 11, 2010 at 10:02 am
llevity (4/9/2010)
Several places, I read that "By default, info_schema will only return data user has access to". Does that mean there's a way to change that default behavior, short of giving the user read access to the tables?
A full explanation, together with correct ways to solve your problem can be found here:
Metadata Visibility Configuration
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply