Kay Smoljak wrote: > I'm having some weird kind of permissions problem with our dev box SQL > Server that I've never come across before, which makes me wonder if > something has changed on the machine to cause it. So either a > permanent solution or a temporary work around would be fantastic. > This is using CF5.
Hmmm... Ok, so obviously no one knows what this is... Well, how about a first step, can anyone explain to me how table ownership works? In Enterprise Manager, when you list the tables in a particular database, they each display an owner. I'm presuming this is the login that was used to create the tables... So if I'm logged in to my local development box using Windows authentication, any tables I create are owned by "dbo", is that correct? Whereas if I'm logged in to the remote box as a particular user, the tables are owned by that user? How can I change the ownership of a table from a user to the Windows authentication login... Or is that not possible? Running sp_changedbowner only seems to allow me to change to one of the logins setup on that box... Or am I completely missing the point? Thanks for any insights... Kay. > The problem is this: After deleting a local database, I use DTS to > transfer a copy of it from the production server (SQL Server 7) to my > dev server (SQL Server 2000 w/latest SP). Most of the tables are owned > by the user set up on the production server - let's say "Fred". Two > tables are owned by "dbo". I'm not sure how/why this is, but the > original database was created on the dev machine and then DTS'd up to > the production server. The tables owned by "dbo" were created later, > possibly directly on the production box. Each DTS operation was using > the "copy objects and data between sql servers" option, and the logins > are the same on both boxes so all keys, identities and default values > come across. > > Now that I've created the new database and populated it with the data, > the local code can only access those tables that are owned by dbo. > Trying to access one of Fred's tables results in "Base table not > found". Specifying the database name in the CFQUERY (ie select * from > databasename.table) will make it work. > > I've tried specifying "database=databasename;" in the connection > string area of the CF Administrator's ODBC area (under the "CF > Settings" tab) but that didn't have any effect. I've tried running > EXEC sp_changedbowner "dbo" but I get "Server: Msg 15007, Level 16, > State 1, Procedure sp_changedbowner, Line 33 The login 'dbo' does not > exist." presumably because "dbo" is not referring to an actual user. > > All I want is to be able to develop locally again without changing > 100+ queries... help! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
