August 4, 2008 at 12:56 pm
One of the applicaitons was using sa to connect the db, later I created an account and granted to db_owner role for this app connection. Now I want to revoke sa from the database maping as the db_owner, but got error message 'Drop failed for the database role 'db_owner', an exception occured while executing a Transact-SQL statement or batch, cannot use the special principal dbo (error:15405)'. Any idea?
August 4, 2008 at 1:18 pm
I don't think you can make sa not be an owner for every database on the server.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 4, 2008 at 1:50 pm
hi friend, by default whenever u create a database a default dbo schema will be given to it. i think u need to create a new login for the same database. u cannot drop the login 'sa'
August 4, 2008 at 2:13 pm
Most likely the sa account is the owner of the database and is mapped to the 'dbo' user. If you want to prevent the application from using the sa account, all you really need to do is change the password.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 4, 2008 at 2:47 pm
were you trying to drop db_owner role or sa, neither of which you can do.
use sp_changedbowner to change the dbo to the login you want. The login cannot already be a user in the database.
---------------------------------------------------------------------
August 5, 2008 at 7:45 am
The sa account has been mapped to db_owner role on some of the databases, I just want to uncheck the role from the user mapping since I created the new account with db_owner role for the databases.
August 5, 2008 at 12:22 pm
The sa account will be mapped to the user dbo when the owner of the database is sa. If you don't want sa to be dbo - then you need to change the owner of the database to a different user (one that does not exist as a user in that database).
I would say that you don't need to do that - or worry about it if you change the sa password and don't give it out.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 5, 2008 at 2:51 pm
sa has sysadmin access and therefore full rights in all the databases whatever you do, so as long as you have stopped the app connecting using sa, you are done.
---------------------------------------------------------------------
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply