Results 1 to 3 of 3

Thread: Public Synonym?

  1. #1
    Join Date
    Dec 2003
    Location
    Texas
    Posts
    1

    Question Unanswered: Public Synonym?

    Hi,

    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?

    Thanks,
    K

  2. #2
    Join Date
    Feb 2002
    Posts
    2,232
    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.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •