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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-08-29 : 12:14:23
|
| Paresh writes "I have a database which has to have a standard login, which is in an 'open for all' file dsn. we cannot do away with the file dsn based access to the application. This standard login has fairly high degree of permissions.I want to restrict the use of this login from outside of our department.One way I thought of was to have a trigger on sysprocesses table to see if anyone logged in from outside our department. The sysprocesses table has his/her machine name. Once that is found that thread could be terminated. But, I learnt that you cannot have triggers on system tables....Is there any other way to do it ???I also thought of replicating the sysprocesses table every 5 minutes and then have trigger on that table. This would at least highlight the login and host name to me, and I can then force the user off. But by then, damage would have been already done...Can somebody help ??Thanks..." |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-08-29 : 12:19:42
|
| Take a look at the HOST_NAME() and HOST_ID() functions in Books Online, and also sp_who and sp_who2. They allow you to get this information without querying sysprocesses. You can set up a table of allowed/authorized machine names and run a job once a minute looking for unauthorized logins and killing their spids.This is a really bad workaround however, what you REALLY need to do is reevaluate your security setup and create new logins, no matter how painful it is. There is no other way to guarantee that people don't login, cause problems, and then log out before they're automatically killed...as you already mentioned.BTW, you're talking about the sa login, aren't you? If you don't change that login to another one, forget it, might as well leave your car unlocked with the keys in the ignition and a big sign that says "STEAL ME!", 'cause that's the situation with your database. |
 |
|
|
pareshmotiwala
Constraint Violating Yak Guru
323 Posts |
Posted - 2002-08-29 : 13:51:32
|
| thanks guys..I will try to get the input of sp_who or who2 into a temp table every 1 minute or so...and kill spids based on the findings.... |
|
|
|
|
|
|
|
|
Protecting Databases from users