locked
Synonyms Permissions? RRS feed

  • Question

  • Hello –

    I’m on a project where I created two Synonyms I called TimeDataFetchCurrent and the other TimeDataFetchOld.

    (Synonym) TimeDataFetchCurrent is referencing my table TimeDataFresh and (Synonym) TimeDataFetchOld is referencing table TDFB

    Users are connecting within Excel to the database server and want them to only use TimeDataFetchCurrent . This way they can pull in the data and report off of it.

    My question is there a way to lock down the tables and only have people see and choose the TimeDataFetchCurrent Synonym when setting this up? Didn’t know if I set the permissions Synonoym if they would carry over and give the table permission as well.

    Thoughts??

    Regards,
    D

     

    Monday, January 9, 2012 6:29 PM

Answers


  • It is sufficient that the user has permissions on the synonym. They don't need any permissions on the table.

    This example example demostrates. Note that Brian can select from Edward, despite he only has access to Jennifer, and when he select objects he only sees Jennifer.

    CREATE DATABASE David
    go
    USE David
    go
    CREATE USER Brian WITHOUT LOGIN
    go
    CREATE TABLE Edward (a int NOT NULL)
    CREATE SYNONYM Jennifer FOR Edward
    go
    GRANT SELECT ON Jennifer TO Brian
    go
    EXECUTE AS USER = 'Brian'
    go
    SELECT * FROM Jennifer
    SELECT name FROM sys.objects
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE David

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 1:03 AM
    Monday, January 9, 2012 10:52 PM

All replies

  • I am not sure what you are trying to achieve with the creation of the 2 synonyms. I am assuming that the 2 synonyms have been created in your database that also has the 2 tables. Your users are connecting to the database via Excel and you want them to only connect to the TimeDataFetchCurrent to report on.

    Have a read of this link - http://msdn.microsoft.com/en-us/library/ms187552.aspx

    A synonym is just a different name for an object. Normally used to make referencing the undertlying table easier. Ie if you have a linked server, instead of using the 4 part name in your query you can have a synonym for that linked table and in your select statement you are just accessing the synonym.

    The permissions for your users are required at the underlying table level. If the users only need access to that one table you could create a group called report_readonly and grant explicit select permissions to the TimeDataFresh table to this group. Then have the appropriate users added to the group.

    You probably do not need to use the synonyms if i have assumed correctly on your environment.

    I hope this helps.


    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Monday, January 9, 2012 8:17 PM
  • Hi - 

    Thank you for the response. Your right the users would need to only use and see the TimeDataFetchCurrent synonyms. I attached a pic showing what I'm talking about. In the screenshot I don't want them to see and be able to choose the others listed.

    If I understand right they will need to see all of them because the table needs the rights. Correct?

     

    Thanks  D

    Monday, January 9, 2012 8:47 PM

  • It is sufficient that the user has permissions on the synonym. They don't need any permissions on the table.

    This example example demostrates. Note that Brian can select from Edward, despite he only has access to Jennifer, and when he select objects he only sees Jennifer.

    CREATE DATABASE David
    go
    USE David
    go
    CREATE USER Brian WITHOUT LOGIN
    go
    CREATE TABLE Edward (a int NOT NULL)
    CREATE SYNONYM Jennifer FOR Edward
    go
    GRANT SELECT ON Jennifer TO Brian
    go
    EXECUTE AS USER = 'Brian'
    go
    SELECT * FROM Jennifer
    SELECT name FROM sys.objects
    go
    REVERT
    go
    USE tempdb
    go
    DROP DATABASE David

    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by Stephanie Lv Wednesday, January 11, 2012 1:03 AM
    Monday, January 9, 2012 10:52 PM
  • My mistake Erland around the select permissions to the Synonym.
    Warwick Rudd
    MCT MCITP SQL Server 2008 Admin
    My Blog
    -------------------------------------------------------
    Please mark as Answered if I have answered your question
    Please vote if this was useful
    -------------------------------------------------------
    Monday, January 9, 2012 11:02 PM
  • Thank you for both for the responses I was able to get the permissions set and the view is what I need the users to see.

    Thank you again for the help!!

     

    Tuesday, January 10, 2012 4:34 PM