Need to check who is connected:
-
Hello,
As we our using one or two accounts in our applications which are hard coded and they have SQL full access, I am just guessing that one of our developer is using one of the accounts to connect to the database engine to make the changes,
Is it the way I configure in SQL and automatically I can be alerted who are the users connected to the database engine, and if that particular user make the changes,
I would appreciate if I get an assistance with this,
Thanks,
Dev
-
You could possibly setup a logon trigger and do something based on the login. Maybe you could write an event out to the error log(xp_logevent) and report if that entry ever occurs. Or, if you are tracking what they are doing, you may want to setup a profiler trace.
CREATE TRIGGER tr_who_has_accessed
ON ALL SERVER
FOR LOGON
AS
BEGIN
.
.
.
END;
GO
-
Hello,
Vould you please let me know what should be the complete query statement if the username is cnsuser
Thanks,
Dev
-
here is a simple model that might help: take a look a this thread on a similar subject, where limits were needed to prevent connections from anywhere but the "right" machines, with allowed logins, and filtering by app name to prevent ad hoc edits / statements from developers on production instances.
.
http://www.sqlservercentral.com/Forums/Topic927131-359-1.aspx?Highlight=logon+trigger
--Prevent access from SSMS
--drop TRIGGER logon_trigger_not_from_SSMS on all server
CREATE TRIGGER logon_trigger_not_from_SSMS
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%Microsoft SQL Server%' --OR not equal to your app name?
BEGIN
--prevent this login from using SSMS
IF suser_name() IN ('cnsuser')
BEGIN
--do you raise an error, or send an email?
RAISERROR('SSMS connections are not permitted with this logon on the Production Instance', 16, 1)
ROLLBACK
END
END --app name check
END --trigger
GO
ENABLE TRIGGER logon_trigger_not_from_SSMS ON ALL SERVER
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks for this, let me explain you what is happening, I have this one developer he using cnsuser account to connect to the SQL via SQL Studio of his machine,
this cnsuser is being used in some applications config. files, so I cannot change the password in SQL either, as this is owner in ket databases,
the only thing for now I need to do is I want to see if the developer is using to connect to the SQL using this account and if he is making any changes,
I will highly appreciate if you could get me the complete statement for that, or teh best practice I should be using,
as I need to run the report in regard to that,
Thanks in Advance,
Dev
-
well then there is two parts to your issue:
I want to see if the developer is using to connect
you can do that with a login trigger...you can write to a table, or send an email. do you have sp_send_mail set up?
the second part
and if he is making any changes,
I think i would create a trace that is filtering on the same three things: database name, user name and app name, to track what, if anything, he is changing. do you know how to set up a trace, or start profiler so you can save a trace that it makes? with that, you don't need a logon trigger, as the info would be captured anyway, just no instant notification of when they connect.
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hello, actually I do not want him to connect using cnsuser, but on the otherside I do not want that if I add the trigger as above, I do not want applications to fail as this account is hard coded in few web and other applications,
only thing I want is that the developer is not able to connect to the server instance using cnsuser,
Thanks,
Dev
-
dsohal (11/29/2010)
Hello, actually I do not want him to connect using cnsuser, but on the otherside I do not want that if I add the trigger as above, I do not want applications to fail as this account is hard coded in few web and other applications,only thing I want is that the developer is not able to connect to the server instance using cnsuser,
Thanks,
Dev
yep that's what the logon trigger i posted above does. I edited it slightly again to do exactly that condition...it tests two things...
If the user is cnsuser and also the application is LIKE '%Microsoft SQL Server%' it prevents a login.
if the user is cnsuser and the application name is , say ".NET" or anything else, no problem, no harm, no foul.
if the user is anything other than cnsuser, also no problem, no harm, no foul.
now depending on the developer, he could still write something that has a different app_name(); whether he writes one himself, connects with excel/access/linqpad/vbs/; the connection string can be written so it say anything for the application name, regardless of the executable calling it.
it'd be very simple to test on your test server until you are confident of the the affects; your other option is to log the information via the trigger or trace, roll up the information as to where when and what, and present the findings to whoever is going to yell at the developer for using unauthorized credentials.
i
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Hello,
Thanks for your help, I guess it is going to help me, just couple of things, I am trying this in my development environment right now,
1- How I can drop or disable delete this trigger?
2- There is another user salesuser, How to add to this trigger? Please Advice,
Much appreciated for your help,
Thanks,
Dev
-
dsohal (11/30/2010)
Hello,Thanks for your help, I guess it is going to help me, just couple of things, I am trying this in my development environment right now,
1- How I can drop or disable delete this trigger?
once you add a server trigger, a new folder appears in the Object Explorer under Server Objects.
you then know the name of it so you can type DISABLE TRIGGER TRIGGERNAME ON ALL SERVER, or you can right click to script or delete the trigger as well.
2- There is another user salesuser, How to add to this trigger? Please Advice,
in the example i posted above, change this line to this:
IF suser_name() IN ('cnsuser')
--changes to
IF suser_name() IN ('cnsuser','salesuser')
Much appreciated for your help,
Thanks,
Dev
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
Thanks a Lot, the only difference I see is the quesry you send me the very 1st time, it creates the trigger and when I try using cnsuser, it will not allow to log on to SQL instance, it says trigger is there so cannot logon, but now after you edit the query the cnsuser can logon,
is it with the minimum permission, please advice,
Thanks,
Dev
-
Thanks a Lot, really appreciate,
It is working for me, we are testing on the development server,
Thanks & Regards,
Dev
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply