Post reply

Deny Permissions to Schema Owner (assign read only) and deny to Linked Servers

  • August 1, 2019 at 9:10 pm

    #3668057

    We are running SQL Server 2012 SP4 EE on Windows Server 2008 R2 Standard. We had a developer who moved to another department. We would like to give the developer 'read only' access to the database before eventually disabling his account. We added the developer to the db_datareader database role. I want to make sure the developer is 'only' allowed to read data from the database.

    1. Does the db_datareader database role permissions apply to the owner of a schema?
    2. Is there a way to deny DDL and DML statements to the owner of a schema? (Deny permissions to the schema's owner?)
    3. Is there a way to deny someone from using Linked Servers (select * from linkedserver) to access data in a remote server?

    Regarding Linked Servers, I read that I should try to add the Local Login, click Impersonate (not sure of this one), and add a fake Remote user and fake Remote Password on the Linked Server Properties Dialog, Security Page, under 'Local server login to remote server login mappings:' section. I need to research this one more and test this out. I am leaving for the day and wanted to get some thoughts and suggestions.

    Thanks in advance.

     

  • August 2, 2019 at 1:26 pm

    #3668254

    If this user is to have only reader access going forward, then I would suggest changing the owner of the schema to someone else, like DBO.

    ALTER AUTHORIZATION ON SCHEMA::schema_name TO user_name;

     

    "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

  • August 30, 2019 at 7:49 pm

    #3675845

    Thanks Eric that would work also. I ended up placing the user in the db_datareader database role and the db_denydatawriter database role. It appeared that when I put the user in the db_datareader database role only, he was still able to insert, update, and delete data from objects in his (own) schema. So, I put him in the db_denydatawriter database role which stopped him from being able to run DML statements against his (own) schema. (I still need to test the DDL access in this scenario.)

    As far as the linked server access, I added the Local Login, (did not click Impersonate), and added a fake Remote user and fake Remote Password on the Linked Server Properties Dialog (Security Page), under 'Local server login to remote server login mappings:' section. When the user attempted to execute the linked server, the following was returned: Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'John_Doe'. So, this appears to be away to deny access to the linked server.

    Thanks again.

Viewing 3 posts - 1 through 2 (of 2 total)

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