Helpdesk to ''Kill'' users
-
We have one database where, occasionally and due to the software of a third party, we get blocks. Normally they clear pretty rapidly or we 'kill' the user.
Helpdesk have asked us if they can have a login which will enable them to also kill users when it is 'out of hours'. They also need to be able to back up in an emergency.
Which is the most appropriate fixed server role? Or should I enable db_backupoperator and db_accessadmin on each database?
Madame Artois
-
I would advise writing a stored procedure to do the work, and grant access to the procedure.
You could include checks in the procedure so it only works in a certain database, during certain hours, etc.
You could also have the procedure insert a row into a log table when it's called - who ran it, who did the kill, when, force them to type a reason, etc.
Granting elevated fixed server role access to an account that will be shared around the helpdesk is some scary stuff.
-Eddie
Eddie Wuerch
MCM: SQL -
ROTFLMAO.........I love the subject line here.......I also have some users I would like to "kill".....
-
Sorry to be 'thick' but I have never written a stored procedure in anger (only having done it in a training environment). I've had a trawl round the templates in Query Analyser but I'm uncertain. Has anyone got an 'idiots guide' to writing such a procedure?
I will be looking at the scripts on this site but any help would be appreciated.
Madame Artois
-
the stored proc would look like :
create proc sp_kill @spid int
as
declare @sql varchar(50)
Set @sql = 'kill ' + cast(@spid as varchar)
exec (@sql)
and you can call like:
sp_kill 55
BUT. The sp will be executed under the user's security context, which is hopefully not a sysadmin or processadmin. Therefore it won't work because based on BOL:
"KILL permissions default to the members of the sysadmin and processadmin fixed database roles, and are not transferable."
You could write a small exe prog just to kill a process. So you can hardcode a specific user with the required sysadmin or processadmin right with a strong password and use this specific exe prog (wiritten in C, VB, delphi, whatever)
Bye
Gabor -
Don't use the "sp_" prefix on user stored procedures, especially when giving advice to newbies. It might be years before he realizes what a cruel joke you've played on him.
-
I have put the sp_ prefix because such kind of procs should be located in the master database as the purpose is system wide.
and as the sp_ type of procs are first searched in the master then elsewhere it fine.
Otherwise yes, you're right it has been a bad advise. I just wanted to show the how to's.
Bye
Gabor -
Hmm...whose permissions are used when a trigger is executed?
I'm imagining you could create a helpdesk kill log, then place an insert trigger that did the kill. Forgive me, I haven't thought this out thoroughly yet, so there may be a reason this isn't possible (can a trigger run an exec sql statement?), but I'm trying to figure a way around this.
If that's possible you could even automate it. Make a job to check for blocks every n seconds, inserting a row when one is found; if a block is discovered two runs in a row, have it automatically killed. Helpdesk never has to get involved.
Again, just thinking on the keyboard. I hope some of this is helpful.
Good luck,
Paul
-
Personally, I wouldn't let the "Help Desk" be able to do either. I guess the backup might be ok if you have a procedure for them, but again I wouldn't. What if you run out of disk space? What if they run a backup when the database is very busy and it slows down all users? Why would they need to run a backup if you have one scheduled?
I wouldn't let them kill SPIDS either. They may get the impression that it is a "fix all" for any problem and end transactions all the time. Killing SPIDS can also cause corruption on occasion--depends on what's happening in the database. I also don't think the processadmin role should be granted to rookies. Just my opinion. 🙂
-
Phew, guys, a lot of bits to respond to...........but here I go
1. Thanks for the thought about sp_ not being the correct name but I knew that one (Yes, we've got those in in-house databases; I'm already on to the Development Manager about those!).
2. We are already backing up the databases at night and at 1pm during the day (for all Live databases). The 1pm backup is also specific to each day so that we can rollback for a week. We could develop the backup routines to cover them.
3. It's these blocks that are causing me issues. 95% of our databases are third party supplied (the only other ones are the ones in item 1!). We always check to see who is doing what before we 'kill'. There is only one that blocks and we have already raised it with the suppliers; they are in the process of writing a new version but it's already 2 months late .............. We even think we know what causes it (see below) so, in theory, all should be sweetness and light (shortly).
The system allows users to use wildcards in screens to filter information. Unfortunately the screens, which are all 'tabbed' together (i.e. click on the next tab for a different screen) access several different tables linked by foreign keys. The users think they are just accessing columns in a table; they are actually changing around all over the database like demented wildebeests.
However the Senior Support Manager knows about the blocks and wants action.
Madame Artois
-
I think automating all backups would be better than having the Help Desk do them. Less room for errors or problems.
You said you always check processes before killing them. Does the Help Desk know how to do that as well? You'd pretty much have to train them. I understand the problem of 3rd party aps causing locks and if management says you "must" come up with a solution, grant the Help Desk permission to kill SPIDS with the understanding that it is only a temporary measure until the vendor fixes the application. Then make sure to take it away later.
We tend to be in a similar situation with 3rd party aps. One particular ap has a business analyst who ends SPIDS whenever there are locks--and there are many--and I found corruption on two tables just recently. The locks were due partly to the application design, partly due to the load, and partly due to additional code that a previous analyst added that supervisors insisted upon. Of course this was unavoidable because of the way the application was built--which gave full database permission to the analyst 🙁 So I know where you're coming from. Good luck.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply