Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

How to prevent user login to SQL Management Studio ? Expand / Collapse
Author
Message
Posted Monday, January 16, 2012 4:56 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 9:11 AM
Points: 85, Visits: 363
I created account with permissions in order my program (I use C#.net) can connect to SQL Server 2008 by this account

But I don't want that user can use this account to login to DB by SQL Management Studio for security purpose

So how to prevent user login to SQL Management Studio ?

Can I setup "db_denydatareader" to MASTER db to prevent the access ?

Thanks
Post #1236514
Posted Monday, January 16, 2012 6:12 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, July 9, 2014 1:28 AM
Points: 2,105, Visits: 5,393
You can create a DDL trigger for login and check if the specific login is using SSMS (by checking the value of app_name() and original_login()). If he does, do a rollback.

Adi


--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #1236554
Posted Monday, January 16, 2012 6:20 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 5:18 AM
Points: 5,014, Visits: 10,515
I did a similar thing some years ago.
Here's the code:

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid


IF ORIGINAL_LOGIN() IN('YOUR_APP_LOGIN_NAME')
AND @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;



--
Gianluca Sartori

Get your two-cent-answer quickly
spaghettidba.com
@spaghettidba
Post #1236562
Posted Monday, January 16, 2012 6:22 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 7:38 AM
Points: 283, Visits: 226
What about using Application Roles?


http://www.techrepublic.com/article/secure-sql-server-application-roles/5068954



Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified
Post #1236563
Posted Monday, January 16, 2012 9:46 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 9:11 AM
Points: 85, Visits: 363
Adi Cohn-120898 (1/16/2012)
You can create a DDL trigger for login and check if the specific login is using SSMS (by checking the value of app_name() and original_login()). If he does, do a rollback.

Adi


I think your solution is good even I didn't try like that before, but is my solution correct ("db_denydatareader" to MASTER), it's very easy and simple ?

Thanks
Post #1236705
Posted Monday, January 16, 2012 10:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 12:42 PM
Points: 2,691, Visits: 3,375
is my solution correct ("db_denydatareader" to MASTER)


No, it is not correct.


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1236738
Posted Monday, January 16, 2012 10:44 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 3:46 PM
Points: 42,462, Visits: 35,525
thang_ngo_2002 (1/16/2012)
but is my solution correct ("db_denydatareader" to MASTER), it's very easy and simple ?


What exactly is that intended to achieve and why do you think it will work?



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1236741
Posted Monday, January 16, 2012 10:44 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: 2 days ago @ 11:08 PM
Points: 21,385, Visits: 9,602
SQLKnowItAll (1/16/2012)
is my solution correct ("db_denydatareader" to MASTER)


No, it is not correct.


Care to actually explain your answer?!?!
Post #1236742
Posted Monday, January 16, 2012 10:44 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 20, 2012 9:11 AM
Points: 85, Visits: 363
SQLKnowItAll (1/16/2012)
is my solution correct ("db_denydatareader" to MASTER)


No, it is not correct.


Could you please explain me why, I'm try that way, I can connect from my program and I can't connect from Management Studio

Thanks
Post #1236743
Posted Monday, January 16, 2012 10:45 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 9:21 AM
Points: 21,244, Visits: 14,946
Gianluca Sartori (1/16/2012)
I did a similar thing some years ago.
Here's the code:

CREATE TRIGGER [TR_LOGON_APP]
ON ALL SERVER
FOR LOGON
AS
BEGIN

DECLARE @program_name nvarchar(128)
DECLARE @host_name nvarchar(128)

SELECT @program_name = program_name,
@host_name = host_name
FROM sys.dm_exec_sessions AS c
WHERE c.session_id = @@spid


IF ORIGINAL_LOGIN() IN('YOUR_APP_LOGIN_NAME')
AND @program_name LIKE '%Management%Studio%'
BEGIN
RAISERROR('This login is for application use only.',16,1)
ROLLBACK;
END
END;



Good stuff Gianluca.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #1236745
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse