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 |
|
albertleng
Starting Member
3 Posts |
 Posted - 2007-10-08 : 00:17:12
|
| Hi all.I have developed a VB6 program which will activate an Application Role to UPDATE and INSERT some tables in SQL Server 2005.My program will login with a username, say USER and then run with the rights of the Application Role which will SELECT, UPDATE and INSERT the following tables:1) Table A2) Table B 3) Table CThe USER login is a restricted user which has only SELECT permission to Table A, B and C.I encounter a problem in which my program can only UPDATE table B and table C but Table A. I have double checked the settings of Table A, Table B and Table C. Theirs are all same. Fyi, the column which can't be updated is of type "datetime".Once i grant USER login UPDATE permission to Table A, my program works perfectly in which it can UPDATE all the tables including Table A.I have tried for almost 2 days but am still clueless. Any ideas?Thank you so much. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 01:13:47
|
| TableA owned by "user" rather than "dbo" ?Kristen |
 |
|
|
albertleng
Starting Member
3 Posts |
Posted - 2007-10-08 : 10:58:58
|
| I dont think tableA is owned by "user" instead of "dbo" but i'm not 100% sure. Basically i configure all 3 tables exactly the same in terms of permissions of Application Role and user as per in my first post.How can i check the ownership of each table?If table A is really owned by "user", how can i revoke the ownership and make sure "dbo" is the one which owns?Thanks. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-08 : 12:24:28
|
| You should be able to see the owner in SSMS or one of the other tools.If it is "user.table" rather than "dbo.table" see Change Owner in Books Online (or search here, it comes up quite often!)(But I think this is a long-shot to be honest; but its possible the table was created such that the Owner was set to something other than "dbo")Kristen |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-08 : 21:28:13
|
| Ensure sp and all related tables have same owner. |
|
|
|
albertleng
Starting Member
3 Posts |
Posted - 2007-10-09 : 02:52:20
|
| All tables have name like "dbo.TABLEx". So, i think all of them have the same owner.I'll have to try the option of making sure sp and all tables have same owner. Even if it's so, it's strange that only TABLE A cant be updated.I'll get back to you all after i try this.Thanks again! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-09 : 04:15:32
|
| "i think all of them have the same owner"Yup, looks like it."it's strange that only TABLE A cant be updated"I agree, but I can't see much from here to make any suggestions!Script the DDL for the tables, and their permissions, and post here and we'll see if we can spot anything.Kristen |
|
|
|
|
|
|
|
|
SQL Server 2005 Application Role's Problem