Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: up the dbo user owning all tables. The DSN set up in the CF obviously not. cfquery datasource=whatever name=test SELECT * FROM [db_user].sometable /cfquery trust the sql server box. that table is owned by that db user which is not the DBO and/or the DSN is setup w/another user. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232836 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
trust the sql server box. that table is owned by that db user which is not the DBO and/or the DSN is setup w/another user. Well, I'm remote-desktopped into the live box right now, I'm staring at both the CF Administrator DSN setup screen AND the database table listing in Enterprise Manager and they both have the exact same user. I'd send screenshots but it would be easier if you just believed me :) Is there anything else it could be? -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232838 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: Is there anything else it could be? not really. while that table might belong to that user name (as seen in EM), if they aren't the DBO you'll always *have* to supply the user name when accessing the table. pop open that user or the roles in that db see what's what. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232844 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/19/06, Paul Hastings [EMAIL PROTECTED] wrote: not really. while that table might belong to that user name (as seen in EM), if they aren't the DBO you'll always *have* to supply the user name when accessing the table. pop open that user or the roles in that db see what's what. Been through that - the dbo role has the user in question listed first on both servers. I'm really stuck! -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232845 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: Been through that - the dbo role has the user in question listed first on both servers. I'm really stuck! if you can only access that table using a user name as a qualifier then that user's not the DBO. can you query the unqualified table using query analyzer using that user/password? if you add the user/password to cfquery does it work? dumb questions: any chance you're not looking at same db/server? what driver? JDBC? ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232846 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/19/06, Paul Hastings [EMAIL PROTECTED] wrote: if you can only access that table using a user name as a qualifier then that user's not the DBO. can you query the unqualified table using query analyzer using that user/password? Through Query Analyzer using that user/pass, I cannot query the table unqualified. Yet Enterprise Manager-roles-dbo-properties tells me that user is in the dbo role. It makes no sense. if you add the user/password to cfquery does it work? No. dumb questions: any chance you're not looking at same db/server? what driver? JDBC? Each server is only running the FarCry site, basically. There's only one database on each, essentially, so no chance at all (I wish that's what it was). It's JDBC... both SQL Servers are running the same MDAC, if that means anything. Is there a chance that something like this could happen if the database was had to be restored from a backup? The client didn't reply when I asked if anything had happened to the box. -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232848 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: Through Query Analyzer using that user/pass, I cannot query the table then that user cannot be in the DBO for that database. Is there a chance that something like this could happen if the database was had to be restored from a backup? The client didn't reply when I asked if anything had happened to the box. well the users should be in the backup but i suppose it's possible the user went south wasn't restored then created later on. lets try kicking it where the sun don't shine: login as SA w/query analyzer connect to that db EXEC sp_changedbowner 'that_user_name' then see what happens. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232849 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/20/06, Paul Hastings [EMAIL PROTECTED] wrote: lets try kicking it where the sun don't shine: login as SA w/query analyzer connect to that db I don't have the SA password on that box... I have my own login to the server (not administrator). If I open Query Analyser and log in with Windows authentication that won't work, will it? EXEC sp_changedbowner 'that_user_name' I can probably get the sysadmin to do that tomorrow morning. Thanks for all your help, Paul, by the way... much appreciated :) -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232850 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/20/06, Kay Smoljak [EMAIL PROTECTED] wrote: I don't have the SA password on that box... I have my own login to the server (not administrator). If I open Query Analyser and log in with Windows authentication that won't work, will it? EXEC sp_changedbowner 'that_user_name' Actually, I logged in with my Windows user and tried it anyway... and got: Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47 The proposed new database owner is already a user in the database. If I delete the user first, the site will stop working completely and all hell will break loose, no? -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232851 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: I don't have the SA password on that box... I have my own login to the server (not administrator). If I open Query Analyser and log in with Windows authentication that won't work, will it? no. ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232852 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
Kay Smoljak wrote: Actually, I logged in with my Windows user and tried it anyway... and got: i guess you're in the admin group on that server. i wouldn't have expected that. Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47 The proposed new database owner is already a user in the database. but isn't recognized as existing db owner. If I delete the user first, the site will stop working completely and all hell will break loose, no? does it work now? do you have backups? if you do, take the db off-line drop the user from the db (not from the logins) then try again. i've tried to recreate this but no matter what i do as long as that user either created the object or was DBO it can access the objects unqualified (connecting using that user name in query analyzer). ditto using a DSN for that user/password. you might have more luck on the sql server list/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232853 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: perplexing SQL Server database permissions problem
Login with enterprise manager, look at the tables. Who doe sit say is the owner of the tables, DBO or a specific user. If it is a specific user, then that means the tables were created as that user, and thus why you have to prefix all queries with that user name. Russ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232854 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/20/06, Paul Hastings [EMAIL PROTECTED] wrote: If I delete the user first, the site will stop working completely and all hell will break loose, no? does it work now? do you have backups? if you do, take the db off-line drop the user from the db (not from the logins) then try again. Most of it works - the FarCry errors are mostly in the admin when you try to update objects. The one or two front end areas where it wasn't working I have worked around the issue by altering the code to output the database owner in the SQL statements. i've tried to recreate this but no matter what i do as long as that user either created the object or was DBO it can access the objects unqualified (connecting using that user name in query analyzer). ditto using a DSN for that user/password. Well, thanks so much for all your help, it's much appreciated and has been very useful. As soon as he gets in this morning I will be getting the sysadmin to try reowning the tables. Cheers! -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232865 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: perplexing SQL Server database permissions problem
On 2/20/06, Snake [EMAIL PROTECTED] wrote: Login with enterprise manager, look at the tables. Who doe sit say is the owner of the tables, DBO or a specific user. If it is a specific user, then that means the tables were created as that user, and thus why you have to prefix all queries with that user name. Hi Russ, When I'm logged in as the local Windows users, both servers (working and non-working) say the same thing - that the specific user owns all the tables, and that user is in the dbo role. Yet on one machine I have to prefix the query names, and on the other I don't. I will get the client's sysadmin to reown the tables when he gets in (I don't have SA access) as I suspect the database was restored at some point and the users are somehow messed up. -- Kay Smoljak http://kay.zombiecoder.com/ ~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:232867 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
