Limit users with Management Studio Accessing a DB

  • Gaby Abed (7/6/2009)

    Third party app unfortunately that uses SQL Logins, not domain. It's a great start though, gives us food for thought. And we'll definitely test it in QA (make sure I'm logged in as SA before we start, in case I need to disarm the trigger).

    ... umm.. if the application use SQL Logins, why would the scanners have read/write/update access to the DB via the AD group you mentioned?

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • Moreover, you mention in the beginning:

    We have a DB that uses Windows Authentication, all domain users have INSERT/UPDATE/DELETE access to all table

    and then

    We have an application that end users use and the type of access the user has is control by application roles within the application

    .

    If the DB is accessed mainly via the Application, why have all users in an AD Group access to thee DB?

    _______________________________________________________________________
    For better assistance in answering your questions, click here[/url]

  • go with the logon trigger, we had to implement one to keep devs out of production SQL servers using SQL tools and MS Access

    only change i would make is to create a group in AD with any people who you want to limit. then create a SQL login for this group and create the trigger for this login. this way if you add or remove people you can do it via AD and not changing the trigger code. less room for error.

  • Thanks everyone for the help, although I didn't initiate this thread. heh heh.

    This has given me food for thought.

    Gaby________________________________________________________________"In theory, theory and practice are the same. In practice, they are not." - Albert Einstein

  • Hi there

    Bit of legacy app and has been around for like 10 years.. Not too sure why it was designed like this.

  • Free (7/6/2009)


    Hi there

    Bit of legacy app and has been around for like 10 years.. Not too sure why it was designed like this.

    I've seen several apps that were developed in the SQL 6.5 or earlier days, and they all seem to take liberties with security, or demand that users run as sa equivalents. We had to lean hard on one vendor to remove the sa requirement for upgrades and application access, because our corporate policies prohibit sa access except under certain, very limited, circumstances. I suspect that many vendors don't want to spend the time nad money updating their apps to use new features and best practices because it's not going to enhance revenue.

Viewing 6 posts - 16 through 20 (of 20 total)

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