How to prevent user login to SQL Management Studio ?

Question

How to prevent user login to SQL Management Studio ?

thang_ngo_2002

SSCrazy

Points: 2187
More actions
January 16, 2012 at 4:56 am

#249087

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

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

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

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

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/

spaghettidba SSC Guru Points: 105732 More actions · Answer 2

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

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 3

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

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 4

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

Jared SSC Guru Points: 61793 More actions · Answer 5

is my solution correct ("db_denydatareader" to MASTER)

No, it is not correct.

Jared
CE - Microsoft

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

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, MVP, M.Sc (Comp Sci)
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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

SQLKnowItAll (1/16/2012)
is my solution correct ("db_denydatareader" to MASTER)
No, it is not correct.

Care to actually explain your answer?!?!

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 8

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

thang_ngo_2002 (1/16/2012)
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

You probably have the master database set as the default database for that user. But in the connection string from the app, you have a specific database designated - bypassing the default database.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 11

thang_ngo_2002 (1/16/2012)
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

Not entirely...

That setting prevents Object Explorer from working correctly, but it does not prevent the query window connections at all. Sure, the person's going to have to know database names (because they can't query sys.databases) and they'll have to write queries rather than clicking around, but that's all it's done.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
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

Jared SSC Guru Points: 61793 More actions · Answer 12

Ninja's_RGR'us (1/16/2012)
SQLKnowItAll (1/16/2012)
is my solution correct ("db_denydatareader" to MASTER)
No, it is not correct.
Care to actually explain your answer?!?!

Sorry, I was getting pulled into a meeting as I was starting my answer, but I knew people would fill in on the answer. 🙂

OP, I know that when you are learning it can be easy to change something, see what happens, and assume that the results you are getting are enough to assume that you are correct. However, with things such as security is is best to really understand why it is doing what it is doing. Think about what you really did... You denied read permissions to the master database. So, try to ask yourself WHY this would accomplish what you needed to accomplish. Ask yourself WHY you got the results you did, even though you did not explicitly block login to SQL Server. One of the things I have learned working with SQL Server is to constantly question myself and why I am doing what I am doing. If it doesn't make sense to get the results I am getting, I must be missing something.

Jared
CE - Microsoft

thang_ngo_2002 SSCrazy Points: 2187 More actions · Answer 13

SQLKnowItAll (1/16/2012)
Ninja's_RGR'us (1/16/2012)
SQLKnowItAll (1/16/2012)
is my solution correct ("db_denydatareader" to MASTER)
No, it is not correct.
Care to actually explain your answer?!?!
Sorry, I was getting pulled into a meeting as I was starting my answer, but I knew people would fill in on the answer. 🙂
OP, I know that when you are learning it can be easy to change something, see what happens, and assume that the results you are getting are enough to assume that you are correct. However, with things such as security is is best to really understand why it is doing what it is doing. Think about what you really did... You denied read permissions to the master database. So, try to ask yourself WHY this would accomplish what you needed to accomplish. Ask yourself WHY you got the results you did, even though you did not explicitly block login to SQL Server. One of the things I have learned working with SQL Server is to constantly question myself and why I am doing what I am doing. If it doesn't make sense to get the results I am getting, I must be missing something.

Thank you very much for useful sharing.

Best regards

spaghettidba SSC Guru Points: 105732 More actions · Answer 14

SQLRNNR (1/16/2012)
Gianluca Sartori (1/16/2012)
I did a similar thing some years ago.
Here's the code:
<snip>
Good stuff Gianluca.

Thanks, glad you liked it.

-- Gianluca Sartori