August 12, 2009 at 1:38 am
Is it possible by any way, notifying a user when logging on or executing a script that this is a production database, on enterprise manager or query analyzer. even though your developement and production db exist on the same server.
August 12, 2009 at 6:41 am
The best way is to make sure that rights to the production database are given only to required users.
"Keep Trying"
August 12, 2009 at 7:01 am
Understandable, but what I mean is, like a notification popup when using the production db, a popup occurs warning you that you are on production. Why I ask, is that one of our developers wiped off data on the production thinking she was on the developement db.
August 12, 2009 at 8:43 am
If everyone connects to the dbs via EM then the answer is no.
I'll assume that you've found yourself in a similar position to most companies: every man and his dog has full unrestricted access to all prod and dev dbs, usually with an account like "admin" and a blank password. Dropping of tables, renaming of columns and general data mullering is going to happen again and again and again until you, the dba, put a stop to it. Use the momentum of this incident, pointing out the potential for future disasters to get managment on your side.
1. Create a new sql admin account on each prod system and test it. Don't give it out to anyone. Ever.
2. Downgrade the currently "well known" account to READ only or disable it - wait to see who complains
3. Create a obviously dev style account for the dev servers and hand it out to everyone who complains, such as "DevAccess" with a password of "I Can't Be Trusted To Access Live Systems"
Job's a good 'un.
August 12, 2009 at 8:51 am
Faiyaaz (8/12/2009)
Understandable, but what I mean is, like a notification popup when using the production db, a popup occurs warning you that you are on production. Why I ask, is that one of our developers wiped off data on the production thinking she was on the developement db.
Developers should not have access rights to a production database by default. If they need access, they should have to jump through a few hoops to gain that access so that they know they are in production.
August 12, 2009 at 9:57 am
It would be very dificult believe me .You will achieve it but will spend a lot of time .....WMI alerts will surely help .
But instead you can try creating triggers so that no one can drop any object and then do a print 'you are working in '.
The earlier reply was correct but unfortunately , Developers need to have sysdba role .
Search the net and se eif there is any combination available that allow the developer to work regularly with no sysdba permissions .
Regards
Abhay
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
August 12, 2009 at 10:05 am
hi_abhay78 (8/12/2009)
The earlier reply was correct but unfortunately , Developers need to have sysdba role .
On dev, maybe, though I'd prefer at most db_owner on the DBs that they're working in, but no way in hell do developers need sysadmin rights to the production server by default.
There's no reason why developers should be developing on the production server and a massive number of reasons why they shouldn't be. They should be working on development servers and then after their code goes through code review and testing, the DBA promotes it to production. If the developers need to troubleshoot in production, there can be a special account that they use just for that, one that the password for changes on a regular basis (like after each use)
p.s. I am a developer.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2009 at 10:10 am
For routine work, neither Developers or DBAs actually need sysadmin rights to production databases. That is how mistakes happen that can be avoided. Developers shouldn't even have db_owner rights on production servers. If they need to be on a production server, the most the should have is db_reader, that way they can't accidently delete data from the production system.
August 12, 2009 at 11:21 pm
You could look at DDL triggers. They should be your second line of defense first is always giving proper permissions to users.
"Keep Trying"
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply