how to remove login that was dbo of an object

I saw a login that was in the sysadmin role and shouldn't have been. When I
went to the database access tab under properties for the login, each database
had "dbo" in the user column. I couldn't change it so I removed the login and
added him back.
After being sure the login had only datareader rights, when I tried to save
the new login I got an error "the login already has an account under a
different user name". When I clicked ok I got an error "Cannot drop the
database owner". I'm in a loop and can't get out of EM.
How do I fix it?
Thanks,
--
Dan D.Hi Dan
Please read about logins and users in the Books Online. It seems you have
some confusion about those topics.
Per your subject, there is no such thing as the 'dbo of an object'.
DBO is a user name that exists in every database and can not be removed. Any
one who is in the sysadmin role uses the user name dbo in every database.
There will also be some login name who is the true owner of the database,
and that person also will use the user name dbo.
When you are trying to add this login back, what user name are you giving
him?
To see what databases this login has access to, use Query Analyzer (not EM)
and run sp_helplogin for this login. Let us know what you get back.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>I saw a login that was in the sysadmin role and shouldn't have been. When I
> went to the database access tab under properties for the login, each
> database
> had "dbo" in the user column. I couldn't change it so I removed the login
> and
> added him back.
> After being sure the login had only datareader rights, when I tried to
> save
> the new login I got an error "the login already has an account under a
> different user name". When I clicked ok I got an error "Cannot drop the
> database owner". I'm in a loop and can't get out of EM.
> How do I fix it?
> Thanks,
> --
> Dan D.
>|||This is what I get using sp_helplogins. I'm trying to add back the
'archer\jvanhoy' login which is the same one that I removed. Do you think
using sp_changedbowner to change the databases to a different login will help?
ARCHER\jvanhoy 0x010500000000000515000000FCE3153178006D1F43170A328E0E0000 bathworld us_english yes no
ARCHER\jvanhoy bathworld db_owner MemberOf
ARCHER\jvanhoy bathworld dbo User
ARCHER\jvanhoy insideMemphis db_owner MemberOf
ARCHER\jvanhoy insideMemphis dbo User
ARCHER\jvanhoy interactivePortfolio db_owner MemberOf
ARCHER\jvanhoy interactivePortfolio dbo User
ARCHER\jvanhoy jpvMemZoo db_owner MemberOf
ARCHER\jvanhoy jpvMemZoo dbo User
ARCHER\jvanhoy leadershipAcad db_owner MemberOf
ARCHER\jvanhoy leadershipAcad dbo User
ARCHER\jvanhoy mmPRToolkit db_owner MemberOf
ARCHER\jvanhoy mmPRToolkit dbo User
ARCHER\jvanhoy rdc db_owner MemberOf
ARCHER\jvanhoy rdc dbo User
ARCHER\jvanhoy rio30 db_owner MemberOf
ARCHER\jvanhoy rio30 dbo User
ARCHER\jvanhoy saigon db_owner MemberOf
ARCHER\jvanhoy saigon dbo User
ARCHER\jvanhoy tellMerryMaids db_owner MemberOf
ARCHER\jvanhoy tellMerryMaids dbo User
ARCHER\jvanhoy tellMerryMaids04 db_owner MemberOf
ARCHER\jvanhoy tellMerryMaids04 dbo User
ARCHER\jvanhoy tempZoo db_owner MemberOf
ARCHER\jvanhoy tempZoo dbo User
ARCHER\jvanhoy testZone db_owner MemberOf
ARCHER\jvanhoy testZone dbo User
ARCHER\jvanhoy tmx_BugsEvite db_owner MemberOf
ARCHER\jvanhoy tmx_BugsEvite dbo User
ARCHER\jvanhoy todaysspas db_owner MemberOf
ARCHER\jvanhoy todaysspas dbo User
ARCHER\jvanhoy valentDeepGreen db_owner MemberOf
ARCHER\jvanhoy valentDeepGreen dbo User
Thanks,
--
Dan D.
"Kalen Delaney" wrote:
> Hi Dan
> Please read about logins and users in the Books Online. It seems you have
> some confusion about those topics.
> Per your subject, there is no such thing as the 'dbo of an object'.
> DBO is a user name that exists in every database and can not be removed. Any
> one who is in the sysadmin role uses the user name dbo in every database.
> There will also be some login name who is the true owner of the database,
> and that person also will use the user name dbo.
> When you are trying to add this login back, what user name are you giving
> him?
> To see what databases this login has access to, use Query Analyzer (not EM)
> and run sp_helplogin for this login. Let us know what you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
> >I saw a login that was in the sysadmin role and shouldn't have been. When I
> > went to the database access tab under properties for the login, each
> > database
> > had "dbo" in the user column. I couldn't change it so I removed the login
> > and
> > added him back.
> >
> > After being sure the login had only datareader rights, when I tried to
> > save
> > the new login I got an error "the login already has an account under a
> > different user name". When I clicked ok I got an error "Cannot drop the
> > database owner". I'm in a loop and can't get out of EM.
> >
> > How do I fix it?
> >
> > Thanks,
> > --
> > Dan D.
> >
>
>|||Is this user actually the owner of all these databases? sp_helpdb can
confirm.
What is your goal? I can't tell you if changing the owner would 'help' if I
don't know what you are hoping to get.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
> This is what I get using sp_helplogins. I'm trying to add back the
> 'archer\jvanhoy' login which is the same one that I removed. Do you think
> using sp_changedbowner to change the databases to a different login will
> help?
> ARCHER\jvanhoy 0x010500000000000515000000FCE3153178006D1F43170A328E0E0000
> bathworld us_english yes no
> ARCHER\jvanhoy bathworld db_owner MemberOf
> ARCHER\jvanhoy bathworld dbo User
> ARCHER\jvanhoy insideMemphis db_owner MemberOf
> ARCHER\jvanhoy insideMemphis dbo User
> ARCHER\jvanhoy interactivePortfolio db_owner MemberOf
> ARCHER\jvanhoy interactivePortfolio dbo User
> ARCHER\jvanhoy jpvMemZoo db_owner MemberOf
> ARCHER\jvanhoy jpvMemZoo dbo User
> ARCHER\jvanhoy leadershipAcad db_owner MemberOf
> ARCHER\jvanhoy leadershipAcad dbo User
> ARCHER\jvanhoy mmPRToolkit db_owner MemberOf
> ARCHER\jvanhoy mmPRToolkit dbo User
> ARCHER\jvanhoy rdc db_owner MemberOf
> ARCHER\jvanhoy rdc dbo User
> ARCHER\jvanhoy rio30 db_owner MemberOf
> ARCHER\jvanhoy rio30 dbo User
> ARCHER\jvanhoy saigon db_owner MemberOf
> ARCHER\jvanhoy saigon dbo User
> ARCHER\jvanhoy tellMerryMaids db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids dbo User
> ARCHER\jvanhoy tellMerryMaids04 db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids04 dbo User
> ARCHER\jvanhoy tempZoo db_owner MemberOf
> ARCHER\jvanhoy tempZoo dbo User
> ARCHER\jvanhoy testZone db_owner MemberOf
> ARCHER\jvanhoy testZone dbo User
> ARCHER\jvanhoy tmx_BugsEvite db_owner MemberOf
> ARCHER\jvanhoy tmx_BugsEvite dbo User
> ARCHER\jvanhoy todaysspas db_owner MemberOf
> ARCHER\jvanhoy todaysspas dbo User
> ARCHER\jvanhoy valentDeepGreen db_owner MemberOf
> ARCHER\jvanhoy valentDeepGreen dbo User
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>> Hi Dan
>> Please read about logins and users in the Books Online. It seems you have
>> some confusion about those topics.
>> Per your subject, there is no such thing as the 'dbo of an object'.
>> DBO is a user name that exists in every database and can not be removed.
>> Any
>> one who is in the sysadmin role uses the user name dbo in every database.
>> There will also be some login name who is the true owner of the database,
>> and that person also will use the user name dbo.
>> When you are trying to add this login back, what user name are you giving
>> him?
>> To see what databases this login has access to, use Query Analyzer (not
>> EM)
>> and run sp_helplogin for this login. Let us know what you get back.
>> --
>> HTH
>> Kalen Delaney, SQL Server MVP
>> www.solidqualitylearning.com
>>
>> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
>> news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>> >I saw a login that was in the sysadmin role and shouldn't have been.
>> >When I
>> > went to the database access tab under properties for the login, each
>> > database
>> > had "dbo" in the user column. I couldn't change it so I removed the
>> > login
>> > and
>> > added him back.
>> >
>> > After being sure the login had only datareader rights, when I tried to
>> > save
>> > the new login I got an error "the login already has an account under a
>> > different user name". When I clicked ok I got an error "Cannot drop the
>> > database owner". I'm in a loop and can't get out of EM.
>> >
>> > How do I fix it?
>> >
>> > Thanks,
>> > --
>> > Dan D.
>> >
>>
>>
>|||This login should only have read permissions or read and write permissions to
all databases. Someone gave the login sysadmin permissions.
I used sp_changedbowner on all of the databases then I was able to save the
new login with read only permissions to the databases.
Thanks for your help.
--
Dan D.
"Kalen Delaney" wrote:
> Is this user actually the owner of all these databases? sp_helpdb can
> confirm.
> What is your goal? I can't tell you if changing the owner would 'help' if I
> don't know what you are hoping to get.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
> > This is what I get using sp_helplogins. I'm trying to add back the
> > 'archer\jvanhoy' login which is the same one that I removed. Do you think
> > using sp_changedbowner to change the databases to a different login will
> > help?
> >
> > ARCHER\jvanhoy 0x010500000000000515000000FCE3153178006D1F43170A328E0E0000
> > bathworld us_english yes no
> > ARCHER\jvanhoy bathworld db_owner MemberOf
> > ARCHER\jvanhoy bathworld dbo User
> > ARCHER\jvanhoy insideMemphis db_owner MemberOf
> > ARCHER\jvanhoy insideMemphis dbo User
> > ARCHER\jvanhoy interactivePortfolio db_owner MemberOf
> > ARCHER\jvanhoy interactivePortfolio dbo User
> > ARCHER\jvanhoy jpvMemZoo db_owner MemberOf
> > ARCHER\jvanhoy jpvMemZoo dbo User
> > ARCHER\jvanhoy leadershipAcad db_owner MemberOf
> > ARCHER\jvanhoy leadershipAcad dbo User
> > ARCHER\jvanhoy mmPRToolkit db_owner MemberOf
> > ARCHER\jvanhoy mmPRToolkit dbo User
> > ARCHER\jvanhoy rdc db_owner MemberOf
> > ARCHER\jvanhoy rdc dbo User
> > ARCHER\jvanhoy rio30 db_owner MemberOf
> > ARCHER\jvanhoy rio30 dbo User
> > ARCHER\jvanhoy saigon db_owner MemberOf
> > ARCHER\jvanhoy saigon dbo User
> > ARCHER\jvanhoy tellMerryMaids db_owner MemberOf
> > ARCHER\jvanhoy tellMerryMaids dbo User
> > ARCHER\jvanhoy tellMerryMaids04 db_owner MemberOf
> > ARCHER\jvanhoy tellMerryMaids04 dbo User
> > ARCHER\jvanhoy tempZoo db_owner MemberOf
> > ARCHER\jvanhoy tempZoo dbo User
> > ARCHER\jvanhoy testZone db_owner MemberOf
> > ARCHER\jvanhoy testZone dbo User
> > ARCHER\jvanhoy tmx_BugsEvite db_owner MemberOf
> > ARCHER\jvanhoy tmx_BugsEvite dbo User
> > ARCHER\jvanhoy todaysspas db_owner MemberOf
> > ARCHER\jvanhoy todaysspas dbo User
> > ARCHER\jvanhoy valentDeepGreen db_owner MemberOf
> > ARCHER\jvanhoy valentDeepGreen dbo User
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "Kalen Delaney" wrote:
> >
> >>
> >> Hi Dan
> >>
> >> Please read about logins and users in the Books Online. It seems you have
> >> some confusion about those topics.
> >> Per your subject, there is no such thing as the 'dbo of an object'.
> >>
> >> DBO is a user name that exists in every database and can not be removed.
> >> Any
> >> one who is in the sysadmin role uses the user name dbo in every database.
> >> There will also be some login name who is the true owner of the database,
> >> and that person also will use the user name dbo.
> >>
> >> When you are trying to add this login back, what user name are you giving
> >> him?
> >>
> >> To see what databases this login has access to, use Query Analyzer (not
> >> EM)
> >> and run sp_helplogin for this login. Let us know what you get back.
> >>
> >> --
> >> HTH
> >> Kalen Delaney, SQL Server MVP
> >> www.solidqualitylearning.com
> >>
> >>
> >> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> >> news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
> >> >I saw a login that was in the sysadmin role and shouldn't have been.
> >> >When I
> >> > went to the database access tab under properties for the login, each
> >> > database
> >> > had "dbo" in the user column. I couldn't change it so I removed the
> >> > login
> >> > and
> >> > added him back.
> >> >
> >> > After being sure the login had only datareader rights, when I tried to
> >> > save
> >> > the new login I got an error "the login already has an account under a
> >> > different user name". When I clicked ok I got an error "Cannot drop the
> >> > database owner". I'm in a loop and can't get out of EM.
> >> >
> >> > How do I fix it?
> >> >
> >> > Thanks,
> >> > --
> >> > Dan D.
> >> >
> >>
> >>
> >>
> >>
> >
>
>

how to remove login that was dbo of an object

I saw a login that was in the sysadmin role and shouldn't have been. When I
went to the database access tab under properties for the login, each databas
e
had "dbo" in the user column. I couldn't change it so I removed the login an
d
added him back.
After being sure the login had only datareader rights, when I tried to save
the new login I got an error "the login already has an account under a
different user name". When I clicked ok I got an error "Cannot drop the
database owner". I'm in a loop and can't get out of EM.
How do I fix it?
Thanks,
--
Dan D.Hi Dan
Please read about logins and users in the Books Online. It seems you have
some confusion about those topics.
Per your subject, there is no such thing as the 'dbo of an object'.
DBO is a user name that exists in every database and can not be removed. Any
one who is in the sysadmin role uses the user name dbo in every database.
There will also be some login name who is the true owner of the database,
and that person also will use the user name dbo.
When you are trying to add this login back, what user name are you giving
him?
To see what databases this login has access to, use Query Analyzer (not EM)
and run sp_helplogin for this login. Let us know what you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>I saw a login that was in the sysadmin role and shouldn't have been. When I
> went to the database access tab under properties for the login, each
> database
> had "dbo" in the user column. I couldn't change it so I removed the login
> and
> added him back.
> After being sure the login had only datareader rights, when I tried to
> save
> the new login I got an error "the login already has an account under a
> different user name". When I clicked ok I got an error "Cannot drop the
> database owner". I'm in a loop and can't get out of EM.
> How do I fix it?
> Thanks,
> --
> Dan D.
>|||This is what I get using sp_helplogins. I'm trying to add back the
'archer\jvanhoy' login which is the same one that I removed. Do you think
using sp_changedbowner to change the databases to a different login will hel
p?
ARCHER\jvanhoy 0x01050000000000051500000
0FCE3153178006D1F43170A328E0E0000 ba
thworld us_english yes no
ARCHER\jvanhoy bathworld db_owner Member
Of
ARCHER\jvanhoy bathworld dbo User
ARCHER\jvanhoy insideMemphis db_owner Me
mberOf
ARCHER\jvanhoy insideMemphis dbo User
ARCHER\jvanhoy interactivePortfolio db_o
wner MemberOf
ARCHER\jvanhoy interactivePortfolio dbo
User
ARCHER\jvanhoy jpvMemZoo db_owner Member
Of
ARCHER\jvanhoy jpvMemZoo dbo User
ARCHER\jvanhoy leadershipAcad db_owner M
emberOf
ARCHER\jvanhoy leadershipAcad dbo User
ARCHER\jvanhoy mmPRToolkit db_owner Memb
erOf
ARCHER\jvanhoy mmPRToolkit dbo User
ARCHER\jvanhoy rdc db_owner MemberOf
ARCHER\jvanhoy rdc dbo User
ARCHER\jvanhoy rio30 db_owner MemberOf
ARCHER\jvanhoy rio30 dbo User
ARCHER\jvanhoy saigon db_owner MemberOf
ARCHER\jvanhoy saigon dbo User
ARCHER\jvanhoy tellMerryMaids db_owner M
emberOf
ARCHER\jvanhoy tellMerryMaids dbo User
ARCHER\jvanhoy tellMerryMaids04 db_owner
MemberOf
ARCHER\jvanhoy tellMerryMaids04 dbo User
ARCHER\jvanhoy tempZoo db_owner MemberOf
ARCHER\jvanhoy tempZoo dbo User
ARCHER\jvanhoy testZone db_owner MemberO
f
ARCHER\jvanhoy testZone dbo User
ARCHER\jvanhoy tmx_BugsEvite db_owner Me
mberOf
ARCHER\jvanhoy tmx_BugsEvite dbo User
ARCHER\jvanhoy todaysspas db_owner Membe
rOf
ARCHER\jvanhoy todaysspas dbo User
ARCHER\jvanhoy valentDeepGreen db_owner
MemberOf
ARCHER\jvanhoy valentDeepGreen dbo User
Thanks,
--
Dan D.
"Kalen Delaney" wrote:

> Hi Dan
> Please read about logins and users in the Books Online. It seems you have
> some confusion about those topics.
> Per your subject, there is no such thing as the 'dbo of an object'.
> DBO is a user name that exists in every database and can not be removed. A
ny
> one who is in the sysadmin role uses the user name dbo in every database.
> There will also be some login name who is the true owner of the database,
> and that person also will use the user name dbo.
> When you are trying to add this login back, what user name are you giving
> him?
> To see what databases this login has access to, use Query Analyzer (not EM
)
> and run sp_helplogin for this login. Let us know what you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>
>|||Is this user actually the owner of all these databases? sp_helpdb can
confirm.
What is your goal? I can't tell you if changing the owner would 'help' if I
don't know what you are hoping to get.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
> This is what I get using sp_helplogins. I'm trying to add back the
> 'archer\jvanhoy' login which is the same one that I removed. Do you think
> using sp_changedbowner to change the databases to a different login will
> help?
> ARCHER\jvanhoy 0x010500000000000515000000FCE3153178006D
1F43170A328E0E0000
> bathworld us_english yes no
> ARCHER\jvanhoy bathworld db_owner MemberOf
> ARCHER\jvanhoy bathworld dbo User
> ARCHER\jvanhoy insideMemphis db_owner MemberOf
> ARCHER\jvanhoy insideMemphis dbo User
> ARCHER\jvanhoy interactivePortfolio db_owner MemberOf
> ARCHER\jvanhoy interactivePortfolio dbo User
> ARCHER\jvanhoy jpvMemZoo db_owner MemberOf
> ARCHER\jvanhoy jpvMemZoo dbo User
> ARCHER\jvanhoy leadershipAcad db_owner MemberOf
> ARCHER\jvanhoy leadershipAcad dbo User
> ARCHER\jvanhoy mmPRToolkit db_owner MemberOf
> ARCHER\jvanhoy mmPRToolkit dbo User
> ARCHER\jvanhoy rdc db_owner MemberOf
> ARCHER\jvanhoy rdc dbo User
> ARCHER\jvanhoy rio30 db_owner MemberOf
> ARCHER\jvanhoy rio30 dbo User
> ARCHER\jvanhoy saigon db_owner MemberOf
> ARCHER\jvanhoy saigon dbo User
> ARCHER\jvanhoy tellMerryMaids db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids dbo User
> ARCHER\jvanhoy tellMerryMaids04 db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids04 dbo User
> ARCHER\jvanhoy tempZoo db_owner MemberOf
> ARCHER\jvanhoy tempZoo dbo User
> ARCHER\jvanhoy testZone db_owner MemberOf
> ARCHER\jvanhoy testZone dbo User
> ARCHER\jvanhoy tmx_BugsEvite db_owner MemberOf
> ARCHER\jvanhoy tmx_BugsEvite dbo User
> ARCHER\jvanhoy todaysspas db_owner MemberOf
> ARCHER\jvanhoy todaysspas dbo User
> ARCHER\jvanhoy valentDeepGreen db_owner MemberOf
> ARCHER\jvanhoy valentDeepGreen dbo User
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
>|||This login should only have read permissions or read and write permissions t
o
all databases. Someone gave the login sysadmin permissions.
I used sp_changedbowner on all of the databases then I was able to save the
new login with read only permissions to the databases.
Thanks for your help.
--
Dan D.
"Kalen Delaney" wrote:

> Is this user actually the owner of all these databases? sp_helpdb can
> confirm.
> What is your goal? I can't tell you if changing the owner would 'help' if
I
> don't know what you are hoping to get.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
>
>

how to remove login that was dbo of an object

I saw a login that was in the sysadmin role and shouldn't have been. When I
went to the database access tab under properties for the login, each database
had "dbo" in the user column. I couldn't change it so I removed the login and
added him back.
After being sure the login had only datareader rights, when I tried to save
the new login I got an error "the login already has an account under a
different user name". When I clicked ok I got an error "Cannot drop the
database owner". I'm in a loop and can't get out of EM.
How do I fix it?
Thanks,
Dan D.
Hi Dan
Please read about logins and users in the Books Online. It seems you have
some confusion about those topics.
Per your subject, there is no such thing as the 'dbo of an object'.
DBO is a user name that exists in every database and can not be removed. Any
one who is in the sysadmin role uses the user name dbo in every database.
There will also be some login name who is the true owner of the database,
and that person also will use the user name dbo.
When you are trying to add this login back, what user name are you giving
him?
To see what databases this login has access to, use Query Analyzer (not EM)
and run sp_helplogin for this login. Let us know what you get back.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>I saw a login that was in the sysadmin role and shouldn't have been. When I
> went to the database access tab under properties for the login, each
> database
> had "dbo" in the user column. I couldn't change it so I removed the login
> and
> added him back.
> After being sure the login had only datareader rights, when I tried to
> save
> the new login I got an error "the login already has an account under a
> different user name". When I clicked ok I got an error "Cannot drop the
> database owner". I'm in a loop and can't get out of EM.
> How do I fix it?
> Thanks,
> --
> Dan D.
>
|||This is what I get using sp_helplogins. I'm trying to add back the
'archer\jvanhoy' login which is the same one that I removed. Do you think
using sp_changedbowner to change the databases to a different login will help?
ARCHER\jvanhoy0x010500000000000515000000FCE3153178006D1F43170A32 8E0E0000bathworldus_englishyes no
ARCHER\jvanhoybathworlddb_ownerMemberOf
ARCHER\jvanhoybathworlddboUser
ARCHER\jvanhoyinsideMemphisdb_ownerMemberOf
ARCHER\jvanhoyinsideMemphisdboUser
ARCHER\jvanhoyinteractivePortfoliodb_ownerMemberOf
ARCHER\jvanhoyinteractivePortfoliodboUser
ARCHER\jvanhoyjpvMemZoodb_ownerMemberOf
ARCHER\jvanhoyjpvMemZoodboUser
ARCHER\jvanhoyleadershipAcaddb_ownerMemberOf
ARCHER\jvanhoyleadershipAcaddboUser
ARCHER\jvanhoymmPRToolkitdb_ownerMemberOf
ARCHER\jvanhoymmPRToolkitdboUser
ARCHER\jvanhoyrdcdb_ownerMemberOf
ARCHER\jvanhoyrdcdboUser
ARCHER\jvanhoyrio30db_ownerMemberOf
ARCHER\jvanhoyrio30dboUser
ARCHER\jvanhoysaigondb_ownerMemberOf
ARCHER\jvanhoysaigondboUser
ARCHER\jvanhoytellMerryMaidsdb_ownerMemberOf
ARCHER\jvanhoytellMerryMaidsdboUser
ARCHER\jvanhoytellMerryMaids04db_ownerMemberOf
ARCHER\jvanhoytellMerryMaids04dboUser
ARCHER\jvanhoytempZoodb_ownerMemberOf
ARCHER\jvanhoytempZoodboUser
ARCHER\jvanhoytestZonedb_ownerMemberOf
ARCHER\jvanhoytestZonedboUser
ARCHER\jvanhoytmx_BugsEvitedb_ownerMemberOf
ARCHER\jvanhoytmx_BugsEvitedboUser
ARCHER\jvanhoytodaysspasdb_ownerMemberOf
ARCHER\jvanhoytodaysspasdboUser
ARCHER\jvanhoyvalentDeepGreendb_ownerMemberOf
ARCHER\jvanhoyvalentDeepGreendboUser
Thanks,
Dan D.
"Kalen Delaney" wrote:

> Hi Dan
> Please read about logins and users in the Books Online. It seems you have
> some confusion about those topics.
> Per your subject, there is no such thing as the 'dbo of an object'.
> DBO is a user name that exists in every database and can not be removed. Any
> one who is in the sysadmin role uses the user name dbo in every database.
> There will also be some login name who is the true owner of the database,
> and that person also will use the user name dbo.
> When you are trying to add this login back, what user name are you giving
> him?
> To see what databases this login has access to, use Query Analyzer (not EM)
> and run sp_helplogin for this login. Let us know what you get back.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:43BC627A-17C5-4E0F-B284-8135FFDB8279@.microsoft.com...
>
>
|||Is this user actually the owner of all these databases? sp_helpdb can
confirm.
What is your goal? I can't tell you if changing the owner would 'help' if I
don't know what you are hoping to get.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
> This is what I get using sp_helplogins. I'm trying to add back the
> 'archer\jvanhoy' login which is the same one that I removed. Do you think
> using sp_changedbowner to change the databases to a different login will
> help?
> ARCHER\jvanhoy 0x010500000000000515000000FCE3153178006D1F43170A32 8E0E0000
> bathworld us_english yes no
> ARCHER\jvanhoy bathworld db_owner MemberOf
> ARCHER\jvanhoy bathworld dbo User
> ARCHER\jvanhoy insideMemphis db_owner MemberOf
> ARCHER\jvanhoy insideMemphis dbo User
> ARCHER\jvanhoy interactivePortfolio db_owner MemberOf
> ARCHER\jvanhoy interactivePortfolio dbo User
> ARCHER\jvanhoy jpvMemZoo db_owner MemberOf
> ARCHER\jvanhoy jpvMemZoo dbo User
> ARCHER\jvanhoy leadershipAcad db_owner MemberOf
> ARCHER\jvanhoy leadershipAcad dbo User
> ARCHER\jvanhoy mmPRToolkit db_owner MemberOf
> ARCHER\jvanhoy mmPRToolkit dbo User
> ARCHER\jvanhoy rdc db_owner MemberOf
> ARCHER\jvanhoy rdc dbo User
> ARCHER\jvanhoy rio30 db_owner MemberOf
> ARCHER\jvanhoy rio30 dbo User
> ARCHER\jvanhoy saigon db_owner MemberOf
> ARCHER\jvanhoy saigon dbo User
> ARCHER\jvanhoy tellMerryMaids db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids dbo User
> ARCHER\jvanhoy tellMerryMaids04 db_owner MemberOf
> ARCHER\jvanhoy tellMerryMaids04 dbo User
> ARCHER\jvanhoy tempZoo db_owner MemberOf
> ARCHER\jvanhoy tempZoo dbo User
> ARCHER\jvanhoy testZone db_owner MemberOf
> ARCHER\jvanhoy testZone dbo User
> ARCHER\jvanhoy tmx_BugsEvite db_owner MemberOf
> ARCHER\jvanhoy tmx_BugsEvite dbo User
> ARCHER\jvanhoy todaysspas db_owner MemberOf
> ARCHER\jvanhoy todaysspas dbo User
> ARCHER\jvanhoy valentDeepGreen db_owner MemberOf
> ARCHER\jvanhoy valentDeepGreen dbo User
> Thanks,
> --
> Dan D.
>
> "Kalen Delaney" wrote:
>
|||This login should only have read permissions or read and write permissions to
all databases. Someone gave the login sysadmin permissions.
I used sp_changedbowner on all of the databases then I was able to save the
new login with read only permissions to the databases.
Thanks for your help.
Dan D.
"Kalen Delaney" wrote:

> Is this user actually the owner of all these databases? sp_helpdb can
> confirm.
> What is your goal? I can't tell you if changing the owner would 'help' if I
> don't know what you are hoping to get.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:B7A644AB-83D5-4C3F-B498-7BAC01693F0E@.microsoft.com...
>
>

how to remap dbo alias

Using Sqlserver 2000. We recently changed the ownership of a database using
sp_changedbowner 'new_owner', true. According to BOL, when the second
argument is true, the existing dbo alias for that database should be remappe
d
to new_owner. Although the database ownership did change to new_owner, the
Users display still shows dbo mapped to the old owner. Am I using
sp_changedbowner correctly? How do I remap the dbo user in that database to
the new owner?
--
MunchingBillThe login specified as the new dbowner should get mapped as the new 'dbo'
user regardless of the @.map specification. Did you refresh Enterprise
Manager?
Note that the @.map parameter indicates that existing *aliases* are remapped.
The dbo user is always remapped to the specified login. Aliases are a
backwards compatibility feature that allows multiple logins to be mapped to
the same database user.
Hope this helps.
Dan Guzman
SQL Server MVP
"MunchingBill" <munchingbill@.newsgroups.nospam> wrote in message
news:8FBEDA24-8F33-4FD7-BE5C-BE9B318D305C@.microsoft.com...
> Using Sqlserver 2000. We recently changed the ownership of a database
> using
> sp_changedbowner 'new_owner', true. According to BOL, when the second
> argument is true, the existing dbo alias for that database should be
> remapped
> to new_owner. Although the database ownership did change to new_owner, the
> Users display still shows dbo mapped to the old owner. Am I using
> sp_changedbowner correctly? How do I remap the dbo user in that database
> to
> the new owner?
> --
> MunchingBill

How To Remap Back To DBO?

How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Server
login? I want this:
someuser.sometable
to be:
dbo.sometable
Thanks!
======================================
David McCarter
www.vsdntips.com
VSDN Tips & Tricks .NET Coding Standards available at:
www.cafepress.com/vsdntips.20412485Using alter schema - for example:
ALTER SCHEMA dbo
TRANSFER someuser.sometable
-Sue
On Mon, 11 Sep 2006 14:28:01 -0700, dotNetDave
<dotNetDave@.discussions.microsoft.com> wrote:

>How to I change the owner (schema) in SQL 2005 back to dbo from a SQL Serve
r
>login? I want this:
>someuser.sometable
>to be:
>dbo.sometable
>Thanks!
>======================================
>David McCarter
>www.vsdntips.com
>VSDN Tips & Tricks .NET Coding Standards available at:
>www.cafepress.com/vsdntips.20412485

How to reference the primary key of a newly added record in trigger?

Please help me somebody solve my problem with my first :o trigger:

ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
INSERT INTO partner (name) SELECT name FROM deleted
UPDATE invoice SET id_partner= *** WHERE id_partner = (SELECT id_partner FROM deleted)


*** - here I want to add a "reference" to the newly added record's automatically generated primary key (not to the updated!) Is it possible?I'd use:ALTER TRIGGER partner_update
ON dbo.partner
FOR UPDATE
AS
BEGIN
INSERT INTO partner (name)
SELECT name FROM deleted

UPDATE o
SET id_partner= i.id_partner
FROM dbo.invoice AS o
INNER JOIN deleted AS d
ON (d.id_partner = o.id_partner)
LEFT OUTER JOIN inserted AS i
ON (i.id_partner = o.id_partner)
ENDThis trigger takes a rather perverse view of the universe, allowing for the possiblity that some low-down, high-smelling, bundle-of-dung might possibly change the value of the id_partner column. The invoice table might or might not like that, since the trigger would then NULL out the invoice.id_partner column!

-PatP