Change dbo login name
-
Hi, Im a bit embarrased to be asking what I think should be an elementary question. If I create a database while logged in as SA then sa is assigned as the login name for DBO. How to I assign a different owner for the database?
The only way I have been able to acheive what I want so far is to create the new login, then connect to SQL using that new login, then create the database. Which is a real pain.
When I search google all I get is advice on how to set DBO as the owner. What I want is DBO as the owner but not with sa as the login.
BTW: why does sqlservercentral have such bad search engine ranking. I rarley see the domain while searching but think it's excellent site.
Cheers,
dave
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
OK So I'm going to answer my own question. The answer came to me as soon as I posted the question.
The short answer is that I don't need to. I simply need to assign the new account the public role. This is the login I use from my ADO driven web pages.
Previously, I had assumed that having sa as dbo was a security problem, so I made a different account the owner and logged in as that user from ADO. Kinda backasswords now that I think of it.
Makes much more sense to leave sa as the owner and have ADO access through a much lower privilige account...duh
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
If you ever need to, you can use sp_changedbowner. In practice I do exactly what you wrote, leave as the actual owner, assign permissions and users to roles as needed.
As for the search engine ranking, who knows? Dont you wish the engines just worked?
-
What about making objects DBO owner to start with, such as tables and stored procs ?
Does a user have to be a System Administrator in order for their objects to automatically be DBO owner ? It seems a bit clunky to have to run sp_changeobjectowner all the time.
-
Well the DBO defaults to whoever is logged in at the time. I don't know about you but I connect to about 6 remote SQL servers each with a number of databases, each with their unique owners and permissions etc.
So because I connect via enterprise manager as sa (so that I can admin each of the many db'd) then any time I create a new database or object sa is the owner.
I tried the sp_changeobjectowner but it didn't seem to work. I'll try it again.
Probably the reason that these forum messages don't show up in google is that you need to login in to access them. Perhaps sqlserver central should write old threads out as static pages, or better yet simply write out a static index page that google can crawl to all the old threads. If they are interested I have had great success in search engine optimizations for my clients and could give them a quote
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Homebrew01,
A database user can create an object owned by DBO by specifying the object name as 'dbo.objectname' rather than just 'objectname'.
Greg
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply