Post reply

Restrict Linked server user to see system tables and views

  • July 1, 2011 at 4:30 am

    #243098

    Hi All

    I gave a sql user to one of my client and they have set up a linked server from one of their existing SQL Server but now the problem is i gave access to exclusively few user tables by using GRANT SELECT ON, but now the problem is they could able to see the system databases Master, MSDB and Temp and the systemviews from the database where the user table existed.

    I want to restrict the client from seeing my system views and system database from their SSMS while expanding the linked server.

    any help appreciated 🙂

  • July 1, 2011 at 6:12 am

    #1346973

    if you have passed the T-sql GRANT SELECT ON

    TO then he cant access the systems databases and systems objects even other user objects

    Regards,
    Syed Jahanzaib Bin Hassan
    BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA

    My Blog
    www.aureus-salah.com

  • July 1, 2011 at 6:20 am

    #1346982

    Syed Jahanzaib Bin hassan (7/1/2011)

    if you have passed the T-sql GRANT SELECT ON

    TO then he cant access the systems databases and systems objects even other user objects

    while he cannot ACCESS the system objects, he's talking about seeing the names of those objects, which is inherited from the PUBLIC role.

    the issue is very similar to a DOD security requirement;

    see this thread for an example which generates teh statemetns to remove access to PUBLIC and GUEST for sys views :

    http://www.sqlservercentral.com/Forums/Topic845604-392-1.aspx#bm845742

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • July 1, 2011 at 6:22 am

    #1346983

    I have denied access to public role but still they are visible from SSMS 🙁

  • July 4, 2011 at 5:34 am

    #1347776

    Help please

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply