I am an Oracle Developer and just started working on a SQL Server database. I created a view and gave ALL rights to PUBLIC and my supervisor. However, when my supervisor logs on as herself she can see the query (in Query Analyzer) but she can't select data from it.
In Oracle I typically have to create a public synonym so users can use an object without specifying the owner of that object. Does the same apply in SQL Server and if so, how do I create a public synonym?
Does that login have access to the database - and within that database does public have "select" rights ? When you manage the permissions for the view do you see that login and if so click on "select" rights.
Did you create your view in your own schema? (as in user.viewname)
Basically, MS goes through a routine for each table on each query. Suppose you issue:
select * from table
SQL Server first looks for username.table where username is your username. If that fails, SQL Server than looks for dbo.table. If that fails, then an error is returned. Because of that first lookup, it is suggested that you supply the owner of the table for queries that are run many many times per hour. The first lookup is not too work intensie, but if you do it a million times an hour....
As for a public synonym, you have to create a view as dbo.view. Then anyone can query it as just select * from view (provided they have no view or table of trhe same name in their own schema).