deny users from accessing server from Management S

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

deny users from accessing server from Management S

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-25 : 20:24:45

Dear All,

How can I deny users from accessing server from Management Studio but allow doing process add, edit & delete through web application? My authetication is using window authetication, sql server authetication is disabled.

Please advise.

Thank you.

Regards,
Micheale

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-26 : 12:32:18

You can't prevent it. So instead you should have all of your data access through stored procedures, and then they won't be able to view/modify the tables unless they use the stored procedures, which can be challenging if they aren't too familiar with them.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-26 : 12:45:38

You can accomplish this using logon triggers, described here:

http://msdn.microsoft.com/en-us/library/bb326598.aspx

You can modify that example fairly easily to detect which application is being used and deny anyone from logging in with Management Studio. I'd suggest you make sure your users are not members of sysadmin or other elevated server roles, and make sure your trigger doesn't prevent such roles from using SSMS in case of emergency.

You should research the APP_NAME(), ISSRVROLEMEMBER(), and HOST_NAME() functions to use in your logon trigger, and also get familiar with EVENTDATA().

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-26 : 12:54:35

Oh! Yeah that'll do it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2012-03-26 : 14:21:05

I'd focus more on what permissions I'm giving to whom, rather than determine what tools they're using.

And, when creating logon triggers, be careful not to lock yourself out.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-03-26 : 14:22:46

Plus you'd have to account for all SQL client tools, which would be practically impossible as a savvy user could create their own and get around the trigger. Controlling permissions would help circumvent that.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

micnie_2020
Posting Yak Master

232 Posts

Posted - 2012-03-26 : 20:27:21

Thank you for all the suggestion:

Here is my solution, It's seem working well. Am i do the right step?
Please advise.

USE master
GO
CREATE TRIGGER trgRestrictUser
ON ALL SERVER WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
IF (ORIGINAL_LOGIN()= 'Domain\my.test' AND APP_NAME() = 'Microsoft SQL Server Management Studio - Query')
ROLLBACK;
END;

robvolk
Most Valuable Yak

15732 Posts

Posted - 2012-03-27 : 03:00:49

That's close, but won't prevent people from using the Object Explorer in SSMS. This should:

IF (ORIGINAL_LOGIN()= 'Domain\my.test' AND APP_NAME() LIKE 'Microsoft SQL Server Management Studio%')

Sachin.Nand

2937 Posts

Posted - 2012-03-27 : 03:39:55

Smart people would use command prompt and use sqlcmd to do what they want.Maybe you should think of preventing that also.

After Monday and Tuesday even the calendar says W T F ....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources