February 26, 2007 at 3:35 pm
Hope I can save someone some time. I restored a database with user owned objects. Tried to fix user name after restore, but discovered the user was database owner. I changed the database owner to sa, and then was able to fix the orphaned user. Then I discovered that the user could not access the tables they owned without using a user name prefix. Took a while to find that the logon the user was mapped to had been granted the "system administrator" role. I hadn't realized that this role not only causes all objects created by the user to default to DBO, but also changes the object resolution to only match DBO, not the user's own name! Fixed by removing the bad role, but it had me confused for a while.
March 1, 2007 at 8:00 am
This was removed by the editor as SPAM
April 23, 2007 at 2:36 pm
Ronzo,
Thanks for the post, I changed a user who owned objects to sysadmin to carry out a test and did not change it back, and came accross this issue, just saved me lots of head scratching time.
Andrew
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply