Post reply

Login Permission

  • March 1, 2016 at 4:35 am

    #308952

    Hi Experts,

    We have a SQL Server and application uses login 'demo' to connect to DB which is having db_owner privilege. Developers can read the config file and they use this login to connect to SQL from there local installed SSMS, how can I prevent users from connecting using local SSMS?

    TIA

  • March 1, 2016 at 6:47 am

    #1862084

    I'm not sure that you could do this. If the SQL login details are in the configuration file and this is freely available then anybody who gets hold of it can use it to access the sql server.

    Can you encrypt the details in the configuration file or reduce the privileges of the account you are using to readonly?

  • March 1, 2016 at 9:09 am

    #1862130

    VastSQL (3/1/2016)

    Hi Experts,

    We have a SQL Server and application uses login 'demo' to connect to DB which is having db_owner privilege. Developers can read the config file and they use this login to connect to SQL from there local installed SSMS, how can I prevent users from connecting using local SSMS?

    TIA

    You could use a login trigger to rollback connections coming from SSMS for these users

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • March 1, 2016 at 9:21 am

    #1862135

    Could they not then just go and connect via Excel/Access or anything else?

  • March 2, 2016 at 1:45 am

    #1862295

    Thanks Perry & Jon,

    Why cant use same idea suggested by perry in the opposite way? Instead of blocking SSMS will only allow appname=.net

  • March 2, 2016 at 7:54 am

    #1862394

    VastSQL (3/2/2016)

    Thanks Perry & Jon,

    Why cant use same idea suggested by perry in the opposite way? Instead of blocking SSMS will only allow appname=.net

    Just keep in mind that the application name can be spoofed quite easily, so depending on how stringent your requirements are that may not be enough.

    Cheers!

  • March 3, 2016 at 4:36 am

    #1862689

    Thanks Jacob,

    Is there anyway we can filter with hostname as well?

  • March 4, 2016 at 6:32 am

    #1863078

    Jon-183221 (3/1/2016)

    Could they not then just go and connect via Excel/Access or anything else?

    Yes and you can also roll those back too 😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 8 posts - 1 through 7 (of 7 total)

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