How to prevent user login to SQL Management Studio ?

Question

How to prevent user login to SQL Management Studio ?

Viewing 4 posts - 31 through 33 (of 33 total)

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

dallas13 SSCertifiable Points: 7491 More actions · Answer 1

Thanks but its not working the way it should work. Its bringing false emails. And instead of user name it brings DOMAINNAME\LOGINNAME that I put inside EXECUTE AS 'DOMAINNAME\LOGINNAME'

I am fine with other easy solution as well.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

dallas13 (12/3/2015)
Thanks but its not working the way it should work. Its bringing false emails. And instead of user name it brings DOMAINNAME\LOGINNAME that I put inside EXECUTE AS 'DOMAINNAME\LOGINNAME'
I am fine with other easy solution as well.

What you need to do is start your own thread instead of hijacking a 3 year old thread. And in that thread you need to provide some details about what you are trying to do. Then we can help you better.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

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

Just be careful because you can set the application name in the connection string and you can change it in Management Studio.

Edit: oops, saw Lowell mentioned this later in the thread. I replied in the first page. I didn't show you how like Lowell did though.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dallas13 SSCertifiable Points: 7491 More actions · Answer 4

Thanks but thats not what I aksed, I need an email when this activity happens and I dont want them to stop from logging. If u go back to page-3 u will see my code but its not working.

Also I have created a new thread at http://www.sqlservercentral.com/Forums/Topic1742461-1550-1.aspx

Thanks.