How to prevent user login to SQL Management Studio ?

  • thang_ngo_2002

    SSCrazy

    Points: 2187

    I created account with permissions in order my program (I use C#.net) can connect to SQL Server 2008 by this account

    But I don’t want that user can use this account to login to DB by SQL Management Studio for security purpose

    So how to prevent user login to SQL Management Studio ?

    Can I setup “db_denydatareader” to MASTER db to prevent the access ?

    Thanks

  • Adi Cohn-120898

    SSC-Dedicated

    Points: 33884

    You can create a DDL trigger for login and check if the specific login is using SSMS (by checking the value of app_name() and original_login()). If he does, do a rollback.

    Adi

    --------------------------------------------------------------
    To know how to ask questions and increase the chances of getting asnwers:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    For better answers on performance questions, click on the following...
    http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • spaghettidba

    SSC Guru

    Points: 105661

    I did a similar thing some years ago.

    Here’s the code:

    CREATE TRIGGER [TR_LOGON_APP]

    ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    DECLARE @program_name nvarchar(128)

    DECLARE @host_name nvarchar(128)

    SELECT @program_name = program_name,

    @host_name = host_name

    FROM sys.dm_exec_sessions AS c

    WHERE c.session_id = @@spid

    IF ORIGINAL_LOGIN() IN(‘YOUR_APP_LOGIN_NAME’)

    AND @program_name LIKE ‘%Management%Studio%’

    BEGIN

    RAISERROR(‘This login is for application use only.’,16,1)

    ROLLBACK;

    END

    END;

  • Robert Murphy UK1

    Default port

    Points: 1425

    What about using Application Roles?

    http://www.techrepublic.com/article/secure-sql-server-application-roles/5068954

    Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
  • thang_ngo_2002

    SSCrazy

    Points: 2187

    Adi Cohn-120898 (1/16/2012)


    You can create a DDL trigger for login and check if the specific login is using SSMS (by checking the value of app_name() and original_login()). If he does, do a rollback.

    Adi

    I think your solution is good even I didn’t try like that before, but is my solution correct (“db_denydatareader” to MASTER), it’s very easy and simple ?

    Thanks

  • SQLKnowItAll

    SSC Guru

    Points: 61781

    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • Gail Shaw

    SSC Guru

    Points: 1004424

    thang_ngo_2002 (1/16/2012)


    but is my solution correct ("db_denydatareader" to MASTER), it's very easy and simple ?

    What exactly is that intended to achieve and why do you think it will work?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ninja’s_RGR’us

    SSC Guru

    Points: 294069

    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Care to actually explain your answer?!?!

  • thang_ngo_2002

    SSCrazy

    Points: 2187

    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Could you please explain me why, I’m try that way, I can connect from my program and I can’t connect from Management Studio

    Thanks

  • SQLRNNR

    SSC Guru

    Points: 281074

    Gianluca Sartori (1/16/2012)


    I did a similar thing some years ago.

    Here's the code:

    CREATE TRIGGER [TR_LOGON_APP]

    ON ALL SERVER

    FOR LOGON

    AS

    BEGIN

    DECLARE @program_name nvarchar(128)

    DECLARE @host_name nvarchar(128)

    SELECT @program_name = program_name,

    @host_name = host_name

    FROM sys.dm_exec_sessions AS c

    WHERE c.session_id = @@spid

    IF ORIGINAL_LOGIN() IN('YOUR_APP_LOGIN_NAME')

    AND @program_name LIKE '%Management%Studio%'

    BEGIN

    RAISERROR('This login is for application use only.',16,1)

    ROLLBACK;

    END

    END;

    Good stuff Gianluca.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • SQLRNNR

    SSC Guru

    Points: 281074

    thang_ngo_2002 (1/16/2012)


    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Could you please explain me why, I'm try that way, I can connect from my program and I can't connect from Management Studio

    Thanks

    You probably have the master database set as the default database for that user. But in the connection string from the app, you have a specific database designated – bypassing the default database.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Gail Shaw

    SSC Guru

    Points: 1004424

    thang_ngo_2002 (1/16/2012)


    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Could you please explain me why, I'm try that way, I can connect from my program and I can't connect from Management Studio

    Thanks

    Not entirely…

    That setting prevents Object Explorer from working correctly, but it does not prevent the query window connections at all. Sure, the person’s going to have to know database names (because they can’t query sys.databases) and they’ll have to write queries rather than clicking around, but that’s all it’s done.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • SQLKnowItAll

    SSC Guru

    Points: 61781

    Ninja's_RGR'us (1/16/2012)


    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Care to actually explain your answer?!?!

    Sorry, I was getting pulled into a meeting as I was starting my answer, but I knew people would fill in on the answer. 🙂

    OP, I know that when you are learning it can be easy to change something, see what happens, and assume that the results you are getting are enough to assume that you are correct. However, with things such as security is is best to really understand why it is doing what it is doing. Think about what you really did… You denied read permissions to the master database. So, try to ask yourself WHY this would accomplish what you needed to accomplish. Ask yourself WHY you got the results you did, even though you did not explicitly block login to SQL Server. One of the things I have learned working with SQL Server is to constantly question myself and why I am doing what I am doing. If it doesn’t make sense to get the results I am getting, I must be missing something.

    Thanks,
    Jared
    PFE - Microsoft
    SQL Know-It-All
    How to post data/code on a forum to get the best help - Jeff Moden[/url]

  • thang_ngo_2002

    SSCrazy

    Points: 2187

    SQLKnowItAll (1/16/2012)


    Ninja's_RGR'us (1/16/2012)


    SQLKnowItAll (1/16/2012)


    is my solution correct ("db_denydatareader" to MASTER)

    No, it is not correct.

    Care to actually explain your answer?!?!

    Sorry, I was getting pulled into a meeting as I was starting my answer, but I knew people would fill in on the answer. 🙂

    OP, I know that when you are learning it can be easy to change something, see what happens, and assume that the results you are getting are enough to assume that you are correct. However, with things such as security is is best to really understand why it is doing what it is doing. Think about what you really did... You denied read permissions to the master database. So, try to ask yourself WHY this would accomplish what you needed to accomplish. Ask yourself WHY you got the results you did, even though you did not explicitly block login to SQL Server. One of the things I have learned working with SQL Server is to constantly question myself and why I am doing what I am doing. If it doesn't make sense to get the results I am getting, I must be missing something.

    Thank you very much for useful sharing.

    Best regards

  • spaghettidba

    SSC Guru

    Points: 105661

    SQLRNNR (1/16/2012)


    Gianluca Sartori (1/16/2012)


    I did a similar thing some years ago.

    Here's the code:

    <snip>

    Good stuff Gianluca.

    Thanks, glad you liked it.

Viewing 15 posts - 1 through 15 (of 34 total)

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