Can not remove user from SQL database....

Hello,
I have a Db that has user name of 'dbo' and login name of 'sa'. For
security reasons, I want this gone.
This user was the owner, but I changed the owner to another user and verified.
After doing this, I still cannot remove the dbo - sa user from the DB. I
get errors:
"Selected user cannot be dropped because the user owns objects"
I cannot remove the public or owner roles from this user either.
Any Ideas?
Thanks much,
MarkThe new owner *is* dbo, so you can't remove that.
You cannot remove the sa login either, since this is a special login. In 2005, you can disable it
and rename it (see ALTER LOGIN).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:A8C854BD-4307-4557-8479-4EC9CE34353B@.microsoft.com...
> Hello,
> I have a Db that has user name of 'dbo' and login name of 'sa'. For
> security reasons, I want this gone.
> This user was the owner, but I changed the owner to another user and verified.
> After doing this, I still cannot remove the dbo - sa user from the DB. I
> get errors:
> "Selected user cannot be dropped because the user owns objects"
> I cannot remove the public or owner roles from this user either.
> Any Ideas?
> Thanks much,
> Mark|||Tibor,
I'm not sure I understand.
I have many DB's and most of them do not have a "DBO" user attached to them
(especially dbo with sa login name).
I added another user to the DB, SQLADMIN, and made that user the owner.
I now want to remove the dbo user with login name of "sa". Or, I at least I
want to change the "sa" on the dbo user to "SQLADMIN" user login name. I get
the errors when I try to remove the dbo-sa user/login name.
I'm doing this from the DB USERS folder, I am not trying to remove the SA
LOGIN from the logins folder.
How to do it?
Thanks much,
Mark
"Tibor Karaszi" wrote:
> The new owner *is* dbo, so you can't remove that.
> You cannot remove the sa login either, since this is a special login. In 2005, you can disable it
> and rename it (see ALTER LOGIN).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:A8C854BD-4307-4557-8479-4EC9CE34353B@.microsoft.com...
> > Hello,
> >
> > I have a Db that has user name of 'dbo' and login name of 'sa'. For
> > security reasons, I want this gone.
> >
> > This user was the owner, but I changed the owner to another user and verified.
> >
> > After doing this, I still cannot remove the dbo - sa user from the DB. I
> > get errors:
> >
> > "Selected user cannot be dropped because the user owns objects"
> >
> > I cannot remove the public or owner roles from this user either.
> >
> > Any Ideas?
> >
> > Thanks much,
> >
> > Mark
>|||> I have many DB's and most of them do not have a "DBO" user attached to them
> (especially dbo with sa login name).
I find the terminology " have a "DBO" user attached to them" confusing. What do you mean by
"attached"? Are you saying that there is no user named dbo in these databases? Note: I'm referring
to the "users" folder! If you have databases in which there is no dbo user, then you have something
strange going on!
> I added another user to the DB, SQLADMIN, and made that user the owner.
Above confuses me even more. You don't add a user to a database. You add a login *as a user in the
database*. Also, how did you make that user the owner of the database? Exactly what steps did you
do, or what commands did you run. If possible please communicate using TSQL commands, as we can run
thses to see whether we get the same result or not.
> I now want to remove the dbo user with login name of "sa".
So you want to change the owner of the database from the login "sa" to some other login? OK, that is
fine, no problems. Use sp_changedbowner for that (you can also use ALTER AUTHORIZATION if you are on
2005).
> Or, I at least I
> want to change the "sa" on the dbo user to "SQLADMIN" user login name.
Again, the terminology used above confuses me. Are you saying that you want to change the owner of
the database from the login "sa" to some other login? If so, see my above comment.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
news:510EE746-48E4-4A9C-8D36-84EB0B5F432B@.microsoft.com...
> Tibor,
> I'm not sure I understand.
> I have many DB's and most of them do not have a "DBO" user attached to them
> (especially dbo with sa login name).
> I added another user to the DB, SQLADMIN, and made that user the owner.
> I now want to remove the dbo user with login name of "sa". Or, I at least I
> want to change the "sa" on the dbo user to "SQLADMIN" user login name. I get
> the errors when I try to remove the dbo-sa user/login name.
> I'm doing this from the DB USERS folder, I am not trying to remove the SA
> LOGIN from the logins folder.
> How to do it?
> Thanks much,
> Mark
>
>
> "Tibor Karaszi" wrote:
>> The new owner *is* dbo, so you can't remove that.
>> You cannot remove the sa login either, since this is a special login. In 2005, you can disable it
>> and rename it (see ALTER LOGIN).
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
>> news:A8C854BD-4307-4557-8479-4EC9CE34353B@.microsoft.com...
>> > Hello,
>> >
>> > I have a Db that has user name of 'dbo' and login name of 'sa'. For
>> > security reasons, I want this gone.
>> >
>> > This user was the owner, but I changed the owner to another user and verified.
>> >
>> > After doing this, I still cannot remove the dbo - sa user from the DB. I
>> > get errors:
>> >
>> > "Selected user cannot be dropped because the user owns objects"
>> >
>> > I cannot remove the public or owner roles from this user either.
>> >
>> > Any Ideas?
>> >
>> > Thanks much,
>> >
>> > Mark|||Tibor,
Sorry about that, I got a little mixed up. There are dbo users on my DB's.
What I want to do is get rid of the "sa" login name and change it to
something else like my "SQLadmin" user.
I tried the sp_changedbowner to change my DB owner to this other user, and
now the "sa" is gone. I believe there was some delay however as it still
showed "sa" after I ran the sp_changedbowner and that is why I was confused.
All seems well now!
Thanks!
Mark
"Tibor Karaszi" wrote:
> > I have many DB's and most of them do not have a "DBO" user attached to them
> > (especially dbo with sa login name).
> I find the terminology " have a "DBO" user attached to them" confusing. What do you mean by
> "attached"? Are you saying that there is no user named dbo in these databases? Note: I'm referring
> to the "users" folder! If you have databases in which there is no dbo user, then you have something
> strange going on!
>
> > I added another user to the DB, SQLADMIN, and made that user the owner.
> Above confuses me even more. You don't add a user to a database. You add a login *as a user in the
> database*. Also, how did you make that user the owner of the database? Exactly what steps did you
> do, or what commands did you run. If possible please communicate using TSQL commands, as we can run
> thses to see whether we get the same result or not.
>
> > I now want to remove the dbo user with login name of "sa".
> So you want to change the owner of the database from the login "sa" to some other login? OK, that is
> fine, no problems. Use sp_changedbowner for that (you can also use ALTER AUTHORIZATION if you are on
> 2005).
>
> > Or, I at least I
> > want to change the "sa" on the dbo user to "SQLADMIN" user login name.
> Again, the terminology used above confuses me. Are you saying that you want to change the owner of
> the database from the login "sa" to some other login? If so, see my above comment.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> news:510EE746-48E4-4A9C-8D36-84EB0B5F432B@.microsoft.com...
> > Tibor,
> >
> > I'm not sure I understand.
> >
> > I have many DB's and most of them do not have a "DBO" user attached to them
> > (especially dbo with sa login name).
> >
> > I added another user to the DB, SQLADMIN, and made that user the owner.
> >
> > I now want to remove the dbo user with login name of "sa". Or, I at least I
> > want to change the "sa" on the dbo user to "SQLADMIN" user login name. I get
> > the errors when I try to remove the dbo-sa user/login name.
> >
> > I'm doing this from the DB USERS folder, I am not trying to remove the SA
> > LOGIN from the logins folder.
> >
> > How to do it?
> >
> > Thanks much,
> >
> > Mark
> >
> >
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The new owner *is* dbo, so you can't remove that.
> >>
> >> You cannot remove the sa login either, since this is a special login. In 2005, you can disable it
> >> and rename it (see ALTER LOGIN).
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://sqlblog.com/blogs/tibor_karaszi
> >>
> >>
> >> "Mrpush" <Mrpush@.discussions.microsoft.com> wrote in message
> >> news:A8C854BD-4307-4557-8479-4EC9CE34353B@.microsoft.com...
> >> > Hello,
> >> >
> >> > I have a Db that has user name of 'dbo' and login name of 'sa'. For
> >> > security reasons, I want this gone.
> >> >
> >> > This user was the owner, but I changed the owner to another user and verified.
> >> >
> >> > After doing this, I still cannot remove the dbo - sa user from the DB. I
> >> > get errors:
> >> >
> >> > "Selected user cannot be dropped because the user owns objects"
> >> >
> >> > I cannot remove the public or owner roles from this user either.
> >> >
> >> > Any Ideas?
> >> >
> >> > Thanks much,
> >> >
> >> > Mark
> >>
>
>