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 |
|
yonabout
Posting Yak Master
112 Posts |
 Posted - 2009-07-14 : 06:26:22
|
| Hi, got a really weird issue.We've got a database where all the tables are owned by a user called HSADBO. So when the application accesses the table as that user, it owns the tables. About half an hour ago, users couldn't log into the application any more. It turns out that thhe user that owns the tables can't access them propoerly anymore.If you log on to query analyser with SQL server authentication as the user who is the table owner, you still can't access the tables unless you stick the HSADBO.[table name] qualifier in front of it.Checked the permissions, and the user still has full access to all the relevant tables etc.Anyone know a) how this could happen?b) how to fix it?Cheers,Yonabout |
|
|
rajdaksha
Aged Yak Warrior
595 Posts |
Posted - 2009-07-14 : 06:29:56
|
| Hisp_changeobjectowner '[OldOwner].[table1]', 'NewOwner'go-------------------------R.. |
 |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-14 : 06:50:19
|
| I would say that your default schema for this user has changed. To check this, look under the security tab at the database level, on Schemas and make sure the owner of the HSADBO schema is the login you are using.rajdaksha - That is to change the owner. That is definitely NOT what the OP wants. |
|
|
|
yonabout
Posting Yak Master
112 Posts |
Posted - 2009-07-14 : 06:52:41
|
| Thanks for that.That's the route we've gone down - we created a new user (HSADummy) and made it the owner of all the tables. Then we deleted the original user (HSADBO), re-created it and then used sp_changeobjectowner to make it the owner of the tables again.Seems to have worked on the tables we've tested it on, so we'll script it in bulk.But how can I find out how it happened? This kind of stuff can't just happen randomly can it?Cheers,Yonabout |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2009-07-14 : 06:57:17
|
| Have you had an upgrade or moved objects? The software should really require that all objects are schema qualified if you are using other than the default schema.You did not need to change the owner, you just needed to ensure the owner of the schema was the HSADBO user. |
|
|
|
yonabout
Posting Yak Master
112 Posts |
Posted - 2009-07-14 : 08:53:04
|
| Thats the weird thing. The schema doesn't appear to have changed at all, and the user is the owner of the schema. Just the same as the same database on a test server, which is fine.We have had an upgrade - to SP4 (only 4 years after the fact), but that was done at the weekend, and everythings been fine for a couple of days since then - it was just this morning that things suddenly went screwy.It has been fixed now - we got rid of the user, re-created it and re-made it the owner of all the tables. I just don't want it to happen again!Cheers,Yonabout |
|
|
|
EMarkM
Starting Member
16 Posts |
Posted - 2009-07-16 : 10:56:22
|
| I'm not aware of any way in which such a schema owner change could happen "by accident".Is there any chance that it was either a malicious attack or an inappropriately-powerful user with insufficient knowledge? |
|
|
|
|
|
|
|
|
table owner has lost its rights??