none
How to secure db login for a specific user

    Pertanyaan

  • 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
    • Dipindahkan oleh Tom Phillips 04 Juni 2018 16:36 Security question
    04 Juni 2018 13:09

Jawaban

  • 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.

    • Ditandai sebagai Jawaban oleh Sudip_inn 04 Juni 2018 20:27
    04 Juni 2018 14:55

Semua Balasan

  • You can remove the database user.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    04 Juni 2018 13:16
  • 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

    04 Juni 2018 13:16
  • is there any such role exist in sql server called db_denydatareader ?

    sql server which version has this role db_denydatareader ?

    04 Juni 2018 14:36
  • 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.

    • Ditandai sebagai Jawaban oleh Sudip_inn 04 Juni 2018 20:27
    04 Juni 2018 14:55
  • 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.


    04 Juni 2018 14:59
  • will check and mark as answer this answer.
    04 Juni 2018 20:27