REVOKE/GRANT Permisison on INFORMATION_SCHEMA Views on User Databases

Question

Hi,

An user database have SELECT permission on INFORMATION_SCHEMA views and 
I’m not able to REVOKE it. Upon investigation found that the database 
was migrated from SQL 7.0 to 2005 by means of backup and restore.
It’s known that the INFORMATION_SCHEMA permission can be handled on master database a
lone but here very peculiar we can see this permission on user databases 
and not able to REVOKE the permission. Please advice how we can resolve this case.

---

Thanks & Regards, Karthikeyan

Answer 1

what is the error meesage you are getting when you are trying it..

have you verified you have the right accesses and also the dependt owner as well what other permission like dbo on other schemas or objects?

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 2

The error message is,

Permission on server scoped catalog views or system stored procedures or extended stored procedures can be granted only when the current database is master.

the command used is,

USE ADI

GO

REVOKE SELECT on INFORMATION_SCHEMA.TABLE_PRIVILEGES FROM PUBLIC

GO

---

Thanks & Regards, Karthikeyan

Answer 3

what is the comptablity of the Database showing for the database after migration and it is bit intersting..

---

Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

Answer 4

It;s still on SQL 7.0 , we tried changed to 90 and still not able to REVOKE.

---

Thanks & Regards, Karthikeyan