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 |
|
kelleyb
Yak Posting Veteran
61 Posts |
 Posted - 2005-09-27 : 22:16:27
|
| I might be in a bit of a pickle and I could *REALLY* use some expert advice here. I'll keep this susinct.Against my better judgement, I took a consulting gig working for my brother-in-law's business. My task was to finish his 85% complete website using ASP/SQL. He designed the whole thing himself using all the cute little Dreamweaver (MX 2004) wizards and a pretty complicated template. He did some custom ASP/SQL, but not much because he's a novice. His primary method for building and executing queries was using a DW plugin called "AdvRS" which creates all SQL statements on the fly using ADO. There are no objects, outside of tables, in the actual database. This plugin creates and drops triggers, views, and uses the four primary SQL commands (s,i,u,d)...again, all on the fly.I found it very difficult to work in DW and decided to export a copy of the ASP code, as well as the database, to my own development server so I could use different methods of development.My plan was to author my own SQL routines for the work he wanted me to do, and simply merge the changes upward to his development server. I created a DTS package using the EM GUI for dragging everything down from his server. I used the "copy SQL server objects task". This worked perfectly, no problems, I had a good copy of his db. I created another DTS package to push my changes upward, to his server.When I ran the package, 75% of the way through, I got an error telling me that I did not have permission to perform the selected operation. "That sucks", I thought, and moved on to other code stuff. Later, he was troubleshooting why some of his code wasn't working and he found every table completely empty of data. Being the honest person that I am, I told him about my failed attempt (due to permissions) to upload db changes to his server. Immediately the fingers started pointing and the voices became raised. His wife (also involved in creating this business, but not technical) was so upset she left for several hours.Though I do not know precisely what permissions I had on the server, I do know that I was a database administrator ONLY for the database I was to be working on. Earlier I found that I didn't have execute permissions on MASTER when I was working on something else, but I can't remember what it was.My question is this... what kind of permissions do I need on the remote server to be able to run a DTS package stored on my own personal development server where I am pushing database objects/data to another server? All the tables were present, but I don't know whether I was able to create just the tables with my DTS package, or his insane DW template created them all on the fly.He was able to restore from backup, so the loss is nearly negligible. There are, as you can guess, issues of money, competence, reputation, and family at play here.If I did wrong, I'll take the heat. I'm a big boy. But I have a feeling that I was thwarted by over-zealous over administration here, and this wouldn't be the first time with this guy. There are numerous security/permissions issues we had to take substantial time ironing out even before I could get to this point.Help!-Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-28 : 00:08:29
|
| were you trying to:1. create the objects by dropping the destination objects if they already exists and re-creating them again plus the data if any-- needs dbo rights2. only deleting the entire table(s) then inserting the new data--needs only insert and delete permissionthere are objects that probably reference to another database which caused the error and you don't have permission in that database?HTH--------------------keeping it simple... |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2005-09-28 : 01:30:37
|
| ok, here is the information on the copy sql server objects task http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_elemtsk2_27xn.aspHere is the key bit of info:If either the destination table or the view exists and contains data, you can specify whether to overwrite or append the incoming data or drop and re-create the table.What did you have selected in your DTS package? If you selected overwrite I think you are screwed. If you selected drop/recreate you are probably not at fault.This can be easily tested in your own environment btw. Test it and let us know what happens. Run sql profiler and see what commands DTS is using to be completely certain.-ec |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2005-09-28 : 09:47:22
|
| Jen, EC... thanks so much for the info. I'm going to test this later today to try an duplicate the trouble I had. Everything on the *real* database is working ok, but I feel the need to *prove* that I didn't do anything wrong... or at least didn't do anything that hundreds of other SQL people might do. Oh... and thanks for the tip on using profiler to help. I'll report back here when I have my findings (and resolution to the issue).(Note to self: when you have that uneasy feeling about working for family... it's probably a tumor in your stomach)-Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2005-09-28 : 14:20:48
|
UPDATE:Tracking down anything I can related to this issue I have, I ran across an error log in my \80\Tools directory. The first error, I only saw one of, the second error I have 6 different instances on various lines of one file.Looks like the script that DTS created (based on my "checkbox" selections in the GUI... i.e. my fault?) intended on re-creating the logins that I have here on my development server, up on the remote server.::Error::In file DEV1.mouse_house.LGN in statement on line 145Transfer Status: Creating Logins on destination database[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot change default database belonging to someone else.::Error::In file DEV1.mouse_house.LGN in statement on line 145Transfer Status: Creating Logins on destination database[Microsoft][ODBC SQL Server Driver][SQL Server]User does not have permission to perform this action. This is obviously something I needed to do when I transfered the db from the remote server, but going back up, I probably should have had the foresight to know that I wouldn't be able to create db logins and grant specific db permissions on the remote server.That being said, would the DTS package have dropped the tables (yes that was the option I selected in the GUI), recreated the tables, but errored out before transfering data? Doesn't really make sense.But, as badly as I'd like to shift the blame to my brother-in-law's messed up permissions problems, I keep kicking myself for not checking the "Rollback transaction on failure" option of the workflow properties. I could have also checked the "fail package on step failure" option. I actually thought about both of these, but for what ever reason declined.Long story short.... this is probably my fault, isn't it?~sigh~-Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-09-28 : 15:49:25
|
| I don't see why going from his database to a remote server should have done anything to the source database.It would have only messed (Dropped) with the destination database.Rule #1: Never work for family, unless it's for free.And fyi, you're better off just doing a dump and restore.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2005-09-28 : 15:59:01
|
| Actually Brett, these errors occurred when I tried to update the production server from my development server using a DTS pkg.Seriously though, you'd recommend a dump and restore for pushing my changes to the prod database? Or are you talking about when I'm grabbing data to work with on my development server?-Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2005-09-28 : 18:53:59
|
| When going from dev to production, I either manually create a change script that will make the table and stored proc changes needed, or use a tool like Red Gate's SQL Compare.When you were going from Dev to Production using DTS, were you changing database structure, or moving data? You might have copied your "empty" dev database to production, thus zapping all data in production.Michael<Yoda>Use the Search page you must. Find the answer you will. Cursors, path to the Dark Side they are. Avoid them, you must. Use Order By NewID() to get a random record you will.</Yoda> |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2005-09-28 : 21:10:32
|
If you really want to see if you caused the problem, just run your DTS package again. If it messes up the database again in the same way, you know what caused it. Simple.  Seriously though, Michael gave you good advise about making changes with scripts. I would just add that you should test your change scripts by doing a backup of the production database, restoring it to a different server, and then running them to verify it does what you want. If you think that seems like a lot of work, think about what you just went through.CODO ERGO SUM |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-09-28 : 23:35:15
|
it was your fault for using the wizard to transfer objects  kidding aside, using the wizard without knowing how it actually does something is a mistake i've also learned the hard wayDTS commits certain steps even if you explicitly tell it to rollback if it encountered an error, atleast from my experiencenext time, create your own script or backup and restoreeither way, what's done is done and most often the best lessons in life are the ones that give us a good kicking  --------------------keeping it simple... |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2005-09-28 : 23:48:50
|
RESOLUTION:(but first some responses)Yeah Michael (P), I should have. In the past I actually got into the habit of creating all of my SQL objects using script instead of the GUI. It really helped when I had to make changes since I knew exactly where to go, plus it helped immensely with my command of the syntax. I completely ignored this when working with my brother-in-laws stuff. Thanks for the tip on that software -- I'll be checking it out soon. Oh... and I thought about the possibility that I could have had empty tables as well, but I remember running queries against it. Good thought though.Michael Jones. I have a better idea... I'll just tell my brother he can find a script kiddie who's a good h4x0r wit dr3mw3av3r for what he's paying me. I gotta tell you though... I'm half tempted by your suggestion  Scripts are the way to the light... I know this now that I have been saved. Ok, resolution. I did a bit more poking around and inquiring of SQL DBA friends. I was refered to this MSDN article: http://tinyurl.com/d649e which provide a bit of guidance. Should've know about these options for all the poking around I tend to do, but I didn't.I still don't know why the package would have (in this order) dropped tables, created tables, create logins and grant perms (<--- which is where I ended up getting my permissions error), and restore data. Had I done a little bit more checking, proding, fondling, I probably could have avoided the whole mess with one of those "Fail package on first error" checkboxes or a transaction with "Commit on successful package completion" enabled. Alas, I allowed someone else's hurry to get the job done become my own hurry thereby setting up this nasty situation.I spent most of the day using tools like SQL Profiler and MS Script Debugger (server-side) just to find the simplest SQL statement coming out of this guy's code and cute little query wizards. I've NEVER had to use tools this advanced, just to get the #*%*#&! SQL statement that the ASP page was sending to the SQL server. Took me about 6 hours to hack through this abortion of wizard-created code, but in the end, one ASP/SQL change I made took all of 10 lines of code and 20 minutes to write, debug, and test. When you combine this with the finger pointing, I think I have the *real* answer to the question.I'd rather be able to see him at family gatherings and smile.I don't show up here often, but I'm grateful that when I do, with a somewhat important question/issue, you guys are here. My sincere appreciation for your links/help/advice. Let's hope my next post is more along the lines of "How do I..."Cheers-Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
|
kelleyb
Yak Posting Veteran
61 Posts |
Posted - 2005-09-28 : 23:53:23
|
quote:
it was your fault for using the wizard to transfer objects
No kidding necessary, I get it now. I'm a  ...I get that now too.Thanks for the tips jenn -Brian"Pray, 'prA, from Latin precari, from prec-, prex; verb:. To ask that the laws of the universe be annulled in behalf of a single petitioner confessedly unworthy." - Ambrose Bierce (1842-1914) |
|
|
|
|
|
|
|
|
Request advice on CMA (covering my a**)