February 19, 2006 at 8:58 am
When I Attach a database to my local server, I set the database owner to sa but after the database is attached successfully, the owner has reverted back to the Windows login user - which is me. Why does that happen?
February 19, 2006 at 5:12 pm
That's wierd.
Did you choose owner properly while attaching database?
sp_changedbowner is the next option for you.
February 19, 2006 at 8:18 pm
Yes, it is wierd. I used the stored procedure to change the db owner and that was OK. I just wish I knew why when I attach a database that happens. It doesn't happen when I create a new database, just to let you know.
February 19, 2006 at 8:20 pm
I didn't answer your other question - yes, I did specifically try to set the owner to sa.
February 20, 2006 at 4:05 am
I am bit confused here....
You did it by stored procedure sp_attach_db?
I researched on it and found that there is no parameter to specify owner using stored procedure sp_attach_db.
If you do it from Enterpise manager, it explicitly runs the command sp_changedbowner.
Did i misunderstand your situation?
February 20, 2006 at 4:55 am
Hi, no you did not misunderstand my question - sorry for the confusion. What I meant was that I did the next step you mentioned and used the stored procedure sp_changedbowner to change the dbo in Query Analyzer and it worked fine. I never ran this in Enterprise Manager, as a matter of fact, I don't know how to do it from there.
The steps I took to attach the database was simply to right click on Databases in the server instance and then go to All Task and click on Attach Database. I then explicitly set the Database Owner to sa. However, once the 'Database attached successfully' message came and I checked the properties on the database, the owner is not sa, but my Windows login. I don't have an explanation of why that is happening.
February 22, 2006 at 8:15 am
First and most important.......what type of authentication does your SQL Server use? Windows Only or Mixed Mode? If it is Windows Only - you cannot set the owner as SA since that is a SQL Server Authentication login.
-SQLBill
February 22, 2006 at 8:54 am
Hi SQLBill,
Thanks for your message. It is set to Mixed Mode so if you have any ideas please let me know.
Brian
February 22, 2006 at 11:26 am
Which version and edition of SQL Server do you have? (Edition = Standard, Enterprise, Developer, MSDE, etc; Version = Service Pack level).
-SQLBill
February 22, 2006 at 12:40 pm
Try setting the user to someone else other than yourself or sa, and see if that takes. If it takes, then set it to sa.
Tom
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply