Limit users with Management Studio Accessing a DB

Question

Limit users with Management Studio Accessing a DB

Free-292871

Right there with Babe

Points: 721
More actions
July 4, 2009 at 8:51 pm

#384914

Hey there everyone,
We have a DB that uses Windows Authentication, all domain users have INSERT/UPDATE/DELETE access to all tables. We have an application that end users use and the type of access the user has is control by application roles within the application. Now this is good for a general end user.
Because all users on the domain have access to the DB, this mean any user that has management studio can accessing the DB (because all domain users are able connect) and then they have been able to run scripts against the db..
So:
Can I somehow limit the DB so only the application can make the change?
or can somehow make it so that anyone in the "Dev AD group" can not connect to the DB with Management Studio?
oh, and how is it possible to this in SQL 2005 or 2008?
Hope this makes sense.
cheers

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

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

ps. One Orange Chip Points: 29422 More actions · Answer 1

You can group the users into several AD groups, Like one group having insert/delete persmission, Other group having only select permissions and add these group to your database with appropriate permissions.

Pradeep Singh

Animal Magic SSChampion Points: 13964 More actions · Answer 2

is the DB that uses windows authentication and the application database the same database?? if so why do your domain users all have full insert/update/delete when you are using application role security? they only need connect rights to the server, nothing more. You can set up an AD group for your developers or whoever you want to give full read/write access too and just setup this group to have the access rather than domain users group.

That way if anyone who has MS on their machine tries to run any scripts they wont have DB access.

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

Hi and thanks for your reply. Sorry I just try and clarify.

We not using the application role within sql. Withing DB1 we have a security table that stores the username, access to what application and what functions they are allowed to perform within the application.

How the application is design

AD group groupMyDB1Users - that have insert/update/delete access to all tables in the DB1 database (all domain users belong in this group).

The problem i'm having is that different users have SQL Server Management studio.. and since they are a domin user and have full access to DB1, then they can use this tool to run scripts on DB1..

I think it might be quite a big of job to rejig the app to application_roles.

At a server level or DB level; I'd like a way to say.. If someone connects to this SQL Server and they are using SQL Server management studio then raise this error..

cheers

Animal Magic SSChampion Points: 13964 More actions · Answer 4

you could look at the possibility of logon trigger, where you roll back logins based on the application type. you would obviously have to put some checking in to make sure the valid users can still login ok.

Another thing you could do is get the network guys to block the management studio exe through group policy for those users who shouldnt be using it.

Id be interested to see if there are any other solutions to this, but when an application is tied into windows auth i cant see many feasible ways of stopping people connecting.

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

I would also question how you prevent users from using Access and ODBC to make changes to tables directly. This is one reason we do all of our updates via stored procedures and don't grant any table access other than select.

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 6

I often have 3rd party apps that insist the App id have 'sa' or some other high level acccess. if the App connects, fine, but I need to keep the developers from using the App id for their own use. I use a Logon Trigger:

create trigger Login_Trigger

on all server

for logon

as begin

IF ORIGINAL_LOGIN() in ('appid1','appid2','appid3')

AND (APP_NAME() like 'Microsoft SQL Server Management%' or APP_NAME() = 'SQLCMD')

ROLLBACK;

end

Tim White

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 7

ooops, sorry, this code is bad. It actually should be just the opposite. use the developers id's and not the app id in the script....

Tim White

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 8

2 Tim 3:16 (7/6/2009)
I often have 3rd party apps that insist the App id have 'sa' or some other high level acccess. if the App connects, fine, but I need to keep the developers from using the App id for their own use. I use a Logon Trigger:
create trigger Login_Trigger
on all server
for logon
as begin
IF ORIGINAL_LOGIN() in ('appid1','appid2','appid3')
AND (APP_NAME() like 'Microsoft SQL Server Management%' or APP_NAME() = 'SQLCMD')
ROLLBACK;
end

Tim, looks like a great idea. I was wondering if this trigger would impact a really busy server where lots of logins occur? For example, we have a warehouse where the scanners access a database (readonly) and we want to make sure those scanner logins are ONLY used for that.

Thanks,

Gaby Abed

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

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 9

Actually, I got thinking about this some more, and I had it right the first time. You want the App Id's listed and if they're trying to use SSMS or SQLCMD, then rollback. Any other APP, they're good to go.

MAKE SURE YOU TEST A LOGON TRIGGER FIRST IN A PURE DEVELOPMENT SYSTEM.

Tim White

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 10

Sorry, I don't know what the overhead is (If Any) for a logon trigger.

Tim White

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

Gaby Abed (7/6/2009)
Tim, looks like a great idea. I was wondering if this trigger would impact a really busy server where lots of logins occur? For example, we have a warehouse where the scanners access a database (readonly) and we want to make sure those scanner logins are ONLY used for that.
Thanks,
Gaby Abed

Can't these scanners be in a different AD group than everyone else and then just have R/O access to the DB?

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

GabyYYZ SSCertifiable Points: 7913 More actions · Answer 12

Richard M (7/6/2009)
Gaby Abed (7/6/2009)
Tim, looks like a great idea. I was wondering if this trigger would impact a really busy server where lots of logins occur? For example, we have a warehouse where the scanners access a database (readonly) and we want to make sure those scanner logins are ONLY used for that.
Thanks,
Gaby Abed
Can't these scanners be in a different AD group than everyone else and then just have R/O access to the DB?

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

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

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 13

If you're going to use a logon trigger keep a couple of commands ready to go if needed:

DROP TRIGGER login_trigger ON ALL SERVER

DISABLE Trigger ALL ON ALL SERVER;

DISABLE Trigger login_trigger ON ALL SERVER;

ENABLE Trigger login_trigger ON ALL SERVER;

select * from sys.server_triggers;

Tim White

2 Tim 3:16 SSCarpal Tunnel Points: 4541 More actions · Answer 14

keep in mind that this will not keep out Access or Excel logins. You'll need to add those if desired. Come to think of it, you probably just want negative logic. Somthing like ...

.....

IF ORIGINAL_LOGIN() = ('appid')

AND (APP_NAME() 'Application name')

ROLLBACK;

end

You can get the App Name from the Activity Monitor.

Of course - TEST

Tim White