Solved

How do I change the login for dbo?

Posted on 2006-10-26
3
633 Views
Last Modified: 2008-02-07
I have a SQL2005 Express database (GAMDB) which I created using SQL Management Studio Express while logged in as a local administrator on the host system.  I am have SQL native authentication enabled.  I created an new login (gam), which is assigned and a new user (gamuser) belonging to a new role (gamsystem).  I have granted 'gamsystem' access to all the table in my database.

Now I would like to disable access by all Windows administrators on the host system (including myself), so that the SA is the boss, so to speak.  I have removed the BUILTIN\Administrators login from the sysadmin server role.  My problem is that the database user 'dbo' is associated with my login on the host system, and since dbo is the owner for the database, I still have access to do what I want with the database when logged in using Windows authentication.

If my understanding is correct, then what I am trying to do is the change the 'dbo' database user so that is is not associated with my Windows login any more, but rather with the 'sa' native login.

Any suggestions (besides that it is recommended to disable SQL native authentication!)

Thanks,

Nicolas
0
Comment
Question by:nicolasn
  • 2
3 Comments
 
LVL 9

Accepted Solution

by:
gpompe earned 288 total points
ID: 17811785
First remove the new user from the DB (gamuser)

and then run sp_changedbowner 'gam'
0
 
LVL 1

Author Comment

by:nicolasn
ID: 17811874
Wow, so simple:

USE GAMDB
GO

EXEC sp_changedbowner 'sa', true
GO

Can I have my points back?!

Nicolas
0
 
LVL 1

Author Comment

by:nicolasn
ID: 17811904
gpompe,

1) I wanted to make the sa the owner, not 'gamuser'

2) The sa login wasn't a defined user for the database (only by being in the sysadmin server role), so I didn't need to remove it.

3) See my sql which did the trick.

Never the less, I give you the points for a pretty good pointer that would definitely helped if I hadn't found the answer myself at the same time as your post.

Thanks,
Nicolas
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now