July 1, 2005 at 4:38 am
Hi,
We are in the process of migrating the database from v7 to v2000. The public role is granted select access on the following objects in v7 but when the database is migrated to v2000, these grants are missing.
[INFORMATION_SCHEMA].[CHECK_CONSTRAINTS]
[INFORMATION_SCHEMA].[COLUMN_DOMAIN_USAGE]
[INFORMATION_SCHEMA].[COLUMN_PRIVILEGES]
[INFORMATION_SCHEMA].[COLUMNS]
[INFORMATION_SCHEMA].[CONSTRAINT_COLUMN_USAGE]
[INFORMATION_SCHEMA].[CONSTRAINT_TABLE_USAGE]
[INFORMATION_SCHEMA].[DOMAIN_CONSTRAINTS]
[INFORMATION_SCHEMA].[DOMAINS]
[INFORMATION_SCHEMA].[KEY_COLUMN_USAGE]
[INFORMATION_SCHEMA].[REFERENTIAL_CONSTRAINTS]
[INFORMATION_SCHEMA].[SCHEMATA]
[INFORMATION_SCHEMA].[TABLE_CONSTRAINTS]
[INFORMATION_SCHEMA].[TABLE_PRIVILEGES]
[INFORMATION_SCHEMA].[TABLES]
[INFORMATION_SCHEMA].[VIEW_COLUMN_USAGE]
[INFORMATION_SCHEMA].[VIEW_TABLE_USAGE]
[INFORMATION_SCHEMA].[VIEWS]
I tried to manually grant access using the following sqls
Grant Select ON [INFORMATION_SCHEMA].[COLUMN_DOMAIN_USAGE] to [public]
Grant Select ON [INFORMATION_SCHEMA].[COLUMN_PRIVILEGES] to [public]
...and so on but I receive the error "Invalid object name".
Can somebody help me on this?
Regards,
Harish
July 3, 2005 at 8:43 am
Explicitly specify the database name:
Grant Select ON DATABASENAME.[INFORMATION_SCHEMA].[COLUMN_DOMAIN_USAGE] to [public]
**ASCII stupid question, get a stupid ANSI !!!**
July 4, 2005 at 12:59 am
The user databases do not contain the INFORMATION_SCHEMA views in SQL Server 2000, though one can access the information in the tables. Refer the following article for more info http://support.microsoft.com/default.aspx?scid=kb;en-us;294350#kb3
So I am not sure if there is any other way of assigning the grant on the tables.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply