July 15, 2005 at 5:05 am
I'm trying to grant select permissions on information_schema.tables to a database user in a user database and it fails with the following error:
Msg 4629, Level 16, State 10, Line 1
Permissions on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.
Here's the command I executed but I get the same error if I use the GUI:
grant select on information_schema.tables to [a_db_user]
In fact, it seems I'm unable to grant access to any of the information_schema views. I don't understand why it's telling me I can only grant the permission in master. I don't want to give the user access to a view in master, I want to give them access to a view in a user database.
Does anyone know if this is by design, and if so, how do I go about giving a user access to an information_schema view? Or is this some kind of bug?
Thanks
July 15, 2005 at 5:32 am
Those views are only located in master, but they can be queried from any database :
grant select on master.information_schema.tables to [a_db_user]
July 15, 2005 at 5:41 am
Gram,
I think the error is also pointing to the fact that your "Current" database is NOT master which could be why the GRANT failed.
Good Hunting!
AJ Ahrens
webmaster@kritter.net
July 15, 2005 at 6:31 am
Correcting my post... You have to be in the db to grant permissions in it.
July 15, 2005 at 6:33 am
Ok, maybe I'm going about this the wrong way.
I want my user to be able to do a select * from information_schema.tables in a database so that the user can see which tables exist in that database.
Before 2005 I used to do:
select * from sysobjects where type = 'u'....
How can I get the list of tables for the current database?
July 15, 2005 at 6:40 am
Those views are special. They are located in master, but they can be queried from any database whitout specifying the dbname.
Ex :
use pubs
select * from information_schema.tables
use northwind
select * from information_schema.tables
July 15, 2005 at 6:59 am
Ok, so how do I give my user the rights to view all of the tables in the current database?
As it stands, my user can do:
use pubs
select * from information_schema.tables
The only row that is returned is for the table dt_properties.
If you can, try this as a test. Create a user, log in as that user and try and select * from information_schema.tables.
July 15, 2005 at 7:03 am
I think I've found what the problem is.....
if the user does not have any permissions on the tables within the database, then information_schema.tables will not return any results.
I gave my user select permissions on one of the tables and now that tables is being returned as a result when doing a select * from information_schema.tables.
Surely there must be a way around this, I don't want to give my user select permissions on the table. I just want the user to see the table names?
July 15, 2005 at 7:14 am
Select Name from [DbName.]dbo.SysObjects where XType = 'U' order by Name
July 15, 2005 at 7:20 am
I found the problem. There is a permission called [View Definition]. I had to give my user [View Definition] permissions on every table in the database.
Now, a select * from information_schema.tables or sysobjects (as Remi suggested) will work.
Bit of a pain because every time a new table gets added to the database I have to make sure that this user gets [View Definition] permissions to that table.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply