January 6, 2015 at 8:46 am
Hi,
I want to force my SQL Server developers to run a script to create a new database and restoring a database so
I want to hide/disable the menus New Database and Restore Database in SQL Server Management Studio.
Please help me on the above.
Thanks in advance.
Mohan
January 6, 2015 at 8:56 am
Mohan
I don't know whether that's possible. But I did have an incident last year where a contractor who was working at the weekend got very upset that he wasn't able to do a database restore. I had sent him the scripts to do the database, but he insisted on using the GUI. It failed, I think because his own account didn't have access to the backup file. If he's used the scripts I provided, he wouldn't have needed access. So you might try, as a partial solution, denying your developers access to the backup folder.
John
January 6, 2015 at 9:03 am
Mohan G (1/6/2015)
I want to force my SQL Server developers to run a script to create a new database and restoring a database so
WHY??? :blink: Developers should NOT have this privs on a production system and, if you gave them such privs on a Dev box, then you need to let them use the tools.
As a bit of a side bar, I've given my Devs privs to do anything they want on the Dev box so they can experiment with many things to foster innovation, etc, but I also have an understanding with them that if they create a new DB, do a restore, or do a backup, there will be a serious "come to Jesus" session in the woodshed because I do Point-in-time backups on the Dev box just like I do in production.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2015 at 9:16 am
There's no way I know of to disable menu options inside of SQL Server Management Studio. The tool is what it is. Permissions given within a server instance will allow people to use any part of the tool that operates within that set of permissions. There's no way around that except documentation, training and cooperation.
It's the same as if you give individual users access to a server and inform them how to make connections to it, but you don't supply them with SSMS. Nothing keeps them from downloading that tool on their own and then connecting it to the server where they have access and know how to connect.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 9:37 am
While you can't 'mask' the GUI options, there is a possible solution (at least for the CREATE DATABASE part).
Try a Database Trigger....
CREATE TRIGGER tName
ON ALL SERVER
FOR CREATE_DATABASE
AS
<code the criteria>
I've never done one of those....so play with it on a DEV server and see if it will work for you. One warning....include in the code a check for the user's login...you don't want to stop everyone from creating databases.
Something like:
If login IN....
PRINT 'You are not authorized to create a database on this server. Please submit proper paperwork to have the database created.'
ELSE
create database
-SQLBill
January 6, 2015 at 10:48 am
Hi All,
Thank you very much for your quick replies. Below is the scenario for which i want to disable/hide the menus.
Whenever team take the production DB backup to their computer and restore, they need to run a script which will update all the client employee email ids, phone nos with the developers' email ids, phone nos. in the restored DB so that while they debug any scenario/code no email or SMS (text) will go to the client employees.
Please note : In our application we have a feature to send emails and text in some scenarios.
Sometimes the developers may forget (it happened couple of times) to run the script once they restore/create a new DB resulting unwanted emails, SMS will go to the client employees.
In the above scenarios, if we can disable/hide the New Database, Restore Database menus then i can force them to run a script which will restore the DB followed by another script which will update the email ids and phone nos.
in the restored DB.
Thanks,
Mohan
January 6, 2015 at 11:14 am
Turn that around. You should only supply them a backup that has clean data. What's to keep a bad person from just taking the backup offsite and doing what they want? You should restore the production database to a test server or staging server or something, then clean the data, then back that up. This will do two things for you. You'll be validating your production backups by running a restore and you'll have clean data for the developers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 1:12 pm
Mohan G (1/6/2015)
Hi All,Thank you very much for your quick replies. Below is the scenario for which i want to disable/hide the menus.
Whenever team take the production DB backup to their computer and restore, they need to run a script which will update all the client employee email ids, phone nos with the developers' email ids, phone nos. in the restored DB so that while they debug any scenario/code no email or SMS (text) will go to the client employees.
Please note : In our application we have a feature to send emails and text in some scenarios.
Sometimes the developers may forget (it happened couple of times) to run the script once they restore/create a new DB resulting unwanted emails, SMS will go to the client employees.
In the above scenarios, if we can disable/hide the New Database, Restore Database menus then i can force them to run a script which will restore the DB followed by another script which will update the email ids and phone nos.
in the restored DB.
Thanks,
Mohan
I hear ya but... this breaks one of my cardinal rules and that rule is that no stored procedure or package will automatically mail anything for the very reasons that you state. Only jobs will email things. It's sometimes a bit of a chore to make that happen but it saves a pile o' work when doing a restore, like the ones your Developers do.
--Jeff Moden
Change is inevitable... Change for the better is not.
January 6, 2015 at 1:17 pm
I agree, NEVER use the GUIs for backups and restores. For one thing, you have no record of what was done, whereas a script can be enhanced over time.
I have the developers call a stored proc to do a backup. All they have to provide is the db name, although there are additional application-level options. That way, they also don't have to know which dbs need multiple backup files, specific BUFFERCOUNT values and other technical issues that they don't really need to deal with.
I let them do dev restores, but not to QA or prod.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 6, 2015 at 4:50 pm
One other point, I'm not sure what industry you're working in, but many, especially healthcare, now have criminal penalties if you expose personal data to people without permission or a method of tracking it. I would also suggest you check into this in regards to your position wherever it is that you work. This is a pretty seriously touchy subject.
I don't have an objection to developers having a copy of production to do their coding against (assuming the database is small enough to allow this). However, it must be a clean copy of the database before I give it to them, not through some secondary process that I can't control (assuming I'm the one that's going to sign off on a legal document that our data is appropriately protected).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 6, 2015 at 5:17 pm
Grant Fritchey (1/6/2015)One other point, I'm not sure what industry you're working in, but many, especially healthcare, now have criminal penalties if you expose personal data to people without permission or a method of tracking it. I would also suggest you check into this in regards to your position wherever it is that you work. This is a pretty seriously touchy subject.
For healthcare, absolutely (at least in the U.S., the only region I know about).
I worked at Smith & Nephew for many years -- they make human implants -- and the FDA made us track every modification to every line of code. There were very serious penalties for being able to answer FDA's questions on all medical devices.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 6, 2015 at 11:44 pm
Grant Fritchey (1/6/2015)
One other point, I'm not sure what industry you're working in, but many, especially healthcare, now have criminal penalties if you expose personal data to people without permission or a method of tracking it. I would also suggest you check into this in regards to your position wherever it is that you work. This is a pretty seriously touchy subject.I don't have an objection to developers having a copy of production to do their coding against (assuming the database is small enough to allow this). However, it must be a clean copy of the database before I give it to them, not through some secondary process that I can't control (assuming I'm the one that's going to sign off on a legal document that our data is appropriately protected).
True enough but MSDB and the SQL Server logs track backups and restores as well or better than most human processes (if it's even ever tracked by humans) whether you run a script or use the GUI. Default trace comes in real handy for things, as well. I back both up and squirrel them away as my Ace up the sleeve. Most of the people I work with don't even know about the default trace until I use it to prove they had their hand in the wrong cookie jar.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply