Limit users with Management Studio Accessing a DB

Question

Limit users with Management Studio Accessing a DB

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

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

Richard M. SSCertifiable Points: 7401 More actions · Answer 1

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]

Richard M. SSCertifiable Points: 7401 More actions · Answer 2

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]

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

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.

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 4

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

Free-292871 Right there with Babe Points: 721 More actions · Answer 5

Hi there

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

Ross McMicken SSCarpal Tunnel Points: 4436 More actions · Answer 6

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.