locked
Give access to specific tables within 1 database to a user RRS feed

  • Question

  • I would like to give readonly (run select query and view definition) access to only a specific set of tables within a database to an employee. 

    When the employee logs into SSMS, I don't want him/her to see any of the other databases that are in the Server and obviously not be able to do any changes whatsoever to the DB. 

    There are a lot of posts around this but nothing that seemed to take me step by step. I've put together the bits from different posts below but running these didn't really help. When I logged in using the login created below, I could not see any Database. 

    -- STEP 1
    create LOGIN newlogin WITH PASSWORD='abcdef', CHECK_POLICY = OFF;
    GO
    
    -- STEP 2
    USE master;
    GO
    DENY VIEW ANY DATABASE TO newlogin; 
    
    -- STEP 3
    USE master;
    GO
    ALTER AUTHORIZATION ON DATABASE::[TestDB] TO newlogin;
    GO
    
    -- STEP 4
    USE [TestDB]
    GO
    CREATE USER newuser FOR LOGIN newlogin
    GO
    
    -- STEP 5
    USE [TestDB];
    GO
    GRANT SELECT ON SchemaName.TableName TO newuser;
    
    GO



    • Edited by Vivek Dev Wednesday, October 18, 2017 10:28 PM
    Wednesday, October 18, 2017 10:26 PM

All replies

  • Hi Vivek Dev,

    >> When the employee logs into SSMS, I don't want him/her to see any of the other databases that are in the Server and obviously not be able to do any changes whatsoever to the DB. 

    To achieve your requirement, you may refer to the code example below:

    --create new login

    create LOGIN newlogin WITH PASSWORD='abcdef', CHECK_POLICY = OFF;

    GO

    --create user for the login

    USE [TestDB]

    GO

    CREATE USER newuser FOR LOGIN newlogin

    GO

    --grant permission

    USE [TestDB];

    GO

    GRANT SELECT ON SchemaName.TableName TO newuser;

    GO

    After running the query above, you might see other database name as well, but that does not mean you have permissions on them.

    Besides, you can use contained database, which is isolated from other databases and from the instance of SQL Server that hosts the database. But it may raise potential issue, so be cautious to use it.

    If you have any other questions, please let me know.

    Regards,

    Hannah


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Hannah Yu Friday, October 20, 2017 9:56 AM
    Thursday, October 19, 2017 8:03 AM