none
How to secure db login for a specific user

    Question

  • suppose there is a user who login with user123 and pwd is test123. now i want this user should not be able to access my db any object. what to do? thanks
    • Moved by Tom Phillips Monday, June 4, 2018 4:36 PM Security question
    Monday, June 4, 2018 1:09 PM

Answers

  • suppose there is a user who login with user123 and pwd is test123. now i want this user should not be able to access my db any object. what to do? thanks

    The below will disable the DB user. The user would still exist in the DB but without 'connect' access i.e. it would be disabled (with a red X sign by its name). It also won't show up in DB user mappings in corresponding login properties. 

    USE <DatabaseName>
    GO
    REVOKE CONNECT TO <USER> as dbo
    GO

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Sudip_inn Monday, June 4, 2018 8:27 PM
    Monday, June 4, 2018 2:55 PM

All replies

  • You can remove the database user.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Monday, June 4, 2018 1:16 PM
  • Hello Sudip,

    You can give denydatareader db Role to that user. below is the TSQL.

    USE [DBName]
    GO
    EXEC sp_droprolemember N'db_denydatareader', N'abc123'
    GO

    Please mark me as answer if my post helps you.

    Br

    chetanV

    Monday, June 4, 2018 1:16 PM
  • is there any such role exist in sql server called db_denydatareader ?

    sql server which version has this role db_denydatareader ?

    Monday, June 4, 2018 2:36 PM
  • suppose there is a user who login with user123 and pwd is test123. now i want this user should not be able to access my db any object. what to do? thanks

    The below will disable the DB user. The user would still exist in the DB but without 'connect' access i.e. it would be disabled (with a red X sign by its name). It also won't show up in DB user mappings in corresponding login properties. 

    USE <DatabaseName>
    GO
    REVOKE CONNECT TO <USER> as dbo
    GO

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Sudip_inn Monday, June 4, 2018 8:27 PM
    Monday, June 4, 2018 2:55 PM
  • is there any such role exist in sql server called db_denydatareader ?

    sql server which version has this role db_denydatareader ?

    Its a db role and it exists in all of SQL versions.


    Monday, June 4, 2018 2:59 PM
  • will check and mark as answer this answer.
    Monday, June 4, 2018 8:27 PM