dbo sql 2000 user access website

[ptidmarsh]

hi please can you give me some help and advice?

I am relatively inexpirienced with mssql and also with websites.

I changed some settings with user permissions and i seem to have now got a problem.

On Sunday I went to our website to find that it was not working (at least the sql part of it). all the text for the website stored in a database.

i investigated what was happening and found that (looking in enterprise manager) the ownership of the tables is no longer 'userabc' but 'dbo'. but not all the tables have been altered in this way only the ones where they are of type 'system' as opposed to type 'user'. I don't know what i did to bring about this change but i have found that if i change ownership of the tables that are of type 'user' from what they should be (userabc) to 'dbo' then the website once again works.

all the ownerships must be put back to 'userabc'. i don't know what i did to cause the change.

please can you help me fix this?

 

[mrdenny]

The system tables must be owned by dbo. They can not be changed. You can change the ownership of each table using the sp_ChangeObjectOwner procedure.

exec sp_ChangeObjectOwner '{TableName}', '{NewOwner}'

You can read up more on this object in Books OnLine.

I would start fixing this by rolling back the security changes that you made on the system. Don't try to correct the issue by making additional changes, just reverse the change that you made. Then you can plan out your changes better and try again.

You'll also want to try this on a dev system first.

By changing the object owner you may end up causing other problems, such as procedures or views looking for the objects with the current owner, not the new owner. This would end up making the problem worse, not better.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ptidmarsh]

thanks mr denny!

hopefully this will help you..
Once you become a client of Denny Cherry's, in order to be able to contact Denny as needed, you will be provided with Denny's cell phone number and pager email address. This information is not posted on this site as it is for current clients only.

Unlike many other consultants, if our schedules are full or we are unable to accept more customers, we will not accept payment or render services. We believe in an open and honest relationship with our clients.

i hope that's cool that i edited that - only typos mind you but it all helps!



thanks for the tips only i don't know what i did to cause the change and i don't know of any built in function that records these kinds of changes and can roll them back for me? is there one?

exec sp_ChangeObjectOwner '{TableName}', '{NewOwner}'

can you tell me... does this do the same thing as right clicking on the table in enterprise manager -> selecting design table -> clicking on the table and index properties -> selecting the new owner using the drop down box?

thanks again for your input!
ptidmarsh

 

[mrdenny]

Yes that will do the same thing.

Thanks for the grammer, etc. My SQL skills are l33t. My English skills show that I am a product of the California Public School system.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[ptidmarsh]

teamwork is a wonderful thing!