Using INFORMATION_SCHEMA Views

[woogoo]

Hi, I've created a (SQL Auth) Login and User details for an application I am writing, this application needs access to the INFORMATION_SCHEMA views. But whenever I try to grant SELECT permissions I get told:

Cannot find the user 'DSSelector', because it does not exist or you do not have permission.

But I can see it under Security/Logins and under MyDatabase/Security/Users when browsed using Management Studio.

However, when execute the VIEW under my DSSelector account, using a SELECT statement I get no rows returned, not a permissions error! But logging in with Windows Authentication and using the same SQL produces results!

Any ideas would be appreciated thanks.


woogoo

 

[RiverGuy]

I don't get this error. Are you sure you have the user attached to your database, and not just the login created?

When I try to explicitly grant permission, I do get a different error, basically stating that the scope is beyond the database itself--which makes sense.

Anyways, for a plain user, I'm able to select from INFORMATION_SCHEMA.TABLES for example. It behaves as expected--I see the tables and views I have permission to.

Which version of SQL Server are you using?

 

[woogoo]

Hi RiverGuy,

Well yes I must do as no exceptions are thrown in the code, I use the SQLDataReader.HasRows() to determine if I've got what I expect returned. All other errors are handled with Exceptions.

In addition to this the Connection String used to connect sets the database, and the CREATE LOGIN has the DEFAULT_DATABASE set, along with the CREATE USER having a default Schema.

Which explains the question marks above my head!


woogoo