January 18, 2024 at 5:59 pm
Hi. I just read the documentation on setting up the RG and didnt like it.
Isnt there a way to write a quick home grown one? I want to regulate just one user's query run times he'll be writing and running from ssms. Its kind of a trial thing. We dont usually give ssms out to non IT folks.
January 18, 2024 at 7:23 pm
My opinion - if an end user is connecting to a DB using SSMS that isn't a DB Developer or DBA, they should only connect to the TEST system, not live.
Now, as far as I know, RG isn't designed to limit things on a user by user basis, so I don't think that is the right way to go. As for limiting their query run times, I am not aware of anything built in, but if you REALLY wanted to, you could probably build a job that watches for long running queries and force kills them, but there is risk with that. If they are modifying data and it is taking a while, killing their query will roll the query back and you just have to wait for the rollback to complete.
I would REALLY be hesitant giving a non-DBA and non-DB Developer permissions to run arbitrary SQL against production systems...
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 19, 2024 at 2:08 pm
Thx Brian. He wont be updating. I appreciate your response. A sandbox is my next choice but i want to go down this path first.
January 19, 2024 at 2:47 pm
You say he won't be updating, but is it because he lacks permissions OR because he promised he wouldn't?
I would make sure your backups are good too. You do NOT want to find out this employee is a malicious employee who is about to blow stuff up then quit. Not likely to happen, but that 1 in a million chance always makes me hesitant to give people access with SSMS to my prod servers. SSMS to test/dev I don't care as much about because I can just refresh from live and no harm, no foul. But production is a much larger risk.
But, as for long running transactions, I think if that is the ONLY thing you care about limiting AND the end user is just doing SELECTs, then having a job that runs every minute to look for any query that is running longer than your threshold and then calls KILL on the spid (will need dynamic SQL for that part, so a bit of a risk here too), that'll handle what you need. It is messy and has risk, but in the end they are your systems. You control the risk so it's your call.
Depending on the size of the database and if it has no PII or confidential data, you could also provide them with a backup of the DB and they could self-host it. VERY little extra work on your side, no risk of long running queries impacting prod, no risk of data changes to prod, no risk of them doing a BEGIN TRANSACTION and forgetting to put a ROLLBACK/COMMIT and locking the table, and if they DO break anything, they can restore from the backup you provided. Now if it is a HUGE database, then that isn't a good solution, but if it is small enough for you and your end user(s), it is a decent approach. The reason I say "no PII and no confidential data" is again due to the "malicious employee" thing - if they have a backup WITH confidential data or PII, they could sell that backup and hurt the company.
OR if you have the disk space on the server, restore the DB onto the same instance with a new name for the DB. Then let the user do whatever they need on the TEST/DEV version of the DB and no need for a new instance. Not good if they fill tempdb or use up all the memory on the sever, but is another option.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
January 19, 2024 at 3:40 pm
he wont have permissions but i think i'm liking your local host idea. I have to check how big that db is.
January 24, 2024 at 3:32 am
This was removed by the editor as SPAM
January 24, 2024 at 3:33 am
This was removed by the editor as SPAM
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply