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 reduce log file size?

Hi all, i have a db with 200 mb log file size. It was set to unlimited grow,
and now i want to reduce its size to 50 mb. In db properties i restricted
log file size to the current size.
Any input would be very helpfull,
thanks> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
Have you tried to archive (backup) it and shrink it again after that? The
T-SQL statement for this is:
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/default.aspx?scid=kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/default.aspx?scid=kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>|||Thanks guys, you all helped me a lot,
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>

how to reduce log file size?

Hi all, i have a db with 200 mb log file size. It was set to unlimited grow,
and now i want to reduce its size to 50 mb. In db properties i restricted
log file size to the current size.
Any input would be very helpfull,
thanks> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
Have you tried to archive (backup) it and shrink it again after that? The
T-SQL statement for this is:
BACKUP LOG yourdbname WITH TRUNCATE_ONLY
sincerely,
--
Sebastian K. Zaklada
Skilled Software
http://www.skilledsoftware.com
This posting is provided "AS IS" with no warranties, and confers no rights.|||Check out below KB articles:
INF: How to Shrink the SQL Server 7.0 Transaction Log
http://support.microsoft.com/defaul...kb;en-us;256650
INF: Shrinking the Transaction Log in SQL Server 2000 with DBCC SHRINKFILE
http://support.microsoft.com/defaul...kb;en-us;272318
Log File Grows too big
http://www.support.microsoft.com/?id=317375
Log file filling up
http://www.support.microsoft.com/?id=110139
Considerations for Autogrow and AutoShrink
http://www.support.microsoft.com/?id=315512
http://www.mssqlserver.com/faq/logs-shrinklog.asp
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>|||Thanks guys, you all helped me a lot,
"Jessy" <admin@.zambak.com> wrote in message
news:uMhRU0D$DHA.620@.TK2MSFTNGP11.phx.gbl...
> Hi all, i have a db with 200 mb log file size. It was set to unlimited
grow,
> and now i want to reduce its size to 50 mb. In db properties i restricted
> log file size to the current size.
> Any input would be very helpfull,
> thanks
>sql

How to reclaim DB space?

I have a sql2000 database at almost 25gigs. When i checked the
properties, it says 87% is unused space. When i went into the shrink
menu, database file looks reasonable(around 2gigs), but log file is
17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
that space and return it to the NT file system? I also did a backup of
full database and also re-backup the log files. Also tried all kinds
of shrinking switches like truncating, rearranging etc...shrink to
3gigs...it comes back with successful but size remains around 17gigs.
what gives?
thank you.If you don't do regular log backups, set the database to simple recovery mode. As for shrinking of
the physical files, see the links at about the middle of this article:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
>I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||Slycat,
Have you tried DBCC SHRINKFILE on the log file?
Ron
--
Ron Talmage
SQL Server MVP
"slycat" <slycat@.consultant.com> wrote in message
news:dec83278.0410281333.57bca24c@.posting.google.com...
> I have a sql2000 database at almost 25gigs. When i checked the
> properties, it says 87% is unused space. When i went into the shrink
> menu, database file looks reasonable(around 2gigs), but log file is
> 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> that space and return it to the NT file system? I also did a backup of
> full database and also re-backup the log files. Also tried all kinds
> of shrinking switches like truncating, rearranging etc...shrink to
> 3gigs...it comes back with successful but size remains around 17gigs.
> what gives?
> thank you.|||please issue a checkpoint command before trying to shrinkfile
"Ron Talmage" wrote:
> Slycat,
> Have you tried DBCC SHRINKFILE on the log file?
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "slycat" <slycat@.consultant.com> wrote in message
> news:dec83278.0410281333.57bca24c@.posting.google.com...
> > I have a sql2000 database at almost 25gigs. When i checked the
> > properties, it says 87% is unused space. When i went into the shrink
> > menu, database file looks reasonable(around 2gigs), but log file is
> > 17gigs with 2gigs used. So, almost 15gigs free space. How do i reclaim
> > that space and return it to the NT file system? I also did a backup of
> > full database and also re-backup the log files. Also tried all kinds
> > of shrinking switches like truncating, rearranging etc...shrink to
> > 3gigs...it comes back with successful but size remains around 17gigs.
> > what gives?
> >
> > thank you.
>
>|||Hi
Change the recovery model to simple (found by right
clicking on the database, then going to the options tab).
Then run the following:
backup log <database_name> with truncate_only
go
dbcc shrinkfile (<logical_log_file>, 100)
Note: to get the <ligical_log_file>, run sp_helpfile.
important: This will not cause you to lose any data, BUT
you will not be able to restore using your tx-log backups
anymore (because you just truncated it!). So, make sure
you do a full SQL backup on this database before giving my
script a go.
You can then change the recovery model back to bulk-logged
or Full if you so require.
Enjoy
Jono|||Sorry for being a noob but what does that do and how do i do it?
newbie <newbie@.discussions.microsoft.com> wrote in message news:<7D798618-FA75-4245-8D44-FC7FD79C9AC6@.microsoft.com>...
> please issue a checkpoint command before trying to shrinkfile
>|||I tried this and it works fine. Thanks a lot!
"Jono" <anonymous@.discussions.microsoft.com> wrote in message news:<1d8701c4bd4d$01954df0$a601280a@.phx.gbl>...
> Hi
> Change the recovery model to simple (found by right
> clicking on the database, then going to the options tab).
> Then run the following:
> backup log <database_name> with truncate_only
> go
> dbcc shrinkfile (<logical_log_file>, 100)
> Note: to get the <ligical_log_file>, run sp_helpfile.
> important: This will not cause you to lose any data, BUT
> you will not be able to restore using your tx-log backups
> anymore (because you just truncated it!). So, make sure
> you do a full SQL backup on this database before giving my
> script a go.
> You can then change the recovery model back to bulk-logged
> or Full if you so require.
> Enjoy
> Jono

How to read SQL Statement in T-SQL Statement Task?

hi all,

after convert from DTS to SSIS, how can i open the SQL statement? because i only saw a line in the properties of the task.

i open the DTSX in vs2005, but i can open to view the SQL statement.

in sql 2000 just double click on the SQL Task, then will popup a dialog form to show the SQL task.

please help.

thanks a lot

hi all,

i found it already....

right click the task and choose edit and then click on the SQL statement.

thanks.....

How to read dynamically Sql Task properties?

Hi everyone,

I’d like to read the SqlStatement property for a Sql Task from a Script Task (previously Sql Task is executed) in execution.

Keeping on mind that such SqlStatement receives an input parameter.

Is it possible? I think so but how do I such thing?

Let me know if you need further details.

Thanks a lot for your time and thoughts,

I don't think you can get it directly from the task, but you could change the task to take its SqlStatement from an expression-based variable. You'd be taking the parameter mapping into your own hands via the expression, but the variable would be available to your script. You'd just need to be sure that at the point you read the SqlStatement variable, the other parameter variables have settled values.

how to read database connection data from a properties file?

Hi guys,
I am very new to Reporting Services, so please bear with me here. I am
curious about how we can configure the database server, database name, and
login data using data from a properties file so that when Reporting Services
and the Report Server start up, they have all the data they need to connect
to the appropriate database. Has anyone tried this yet? If so how would it
be possible?
Thanks a bunch,
Carolyn
cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.comWhen you design reports you have the option of using shared data sources for
running the report. Or each report can use its own data source. I always use
the shared datasources. The shared data source is referred to when you
design the report. When you deploy the default is that the shared datasource
does not get written over. What this means is you can configure the shared
datasource on the server and it can be different than what the developers
are using. For the credentials for the shared datasource I use a specific
username. The db username I use is one I have setup for reporting that has
only read access to the database. An advantage of this is it allows
connection pooling since all the connections for reporting will use the
exact same connection string (a necessity for connection pooling).
Note that what I am talking about here is getting to the database which has
the data for the report. RS also has a database which is used for caching of
the reporting objects. This can be the same or different database from which
you are reporting on.
Bruce L-C
"Carolyn Vo" <cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com> wrote in
message news:O3lMQ9BhEHA.556@.tk2msftngp13.phx.gbl...
> Hi guys,
> I am very new to Reporting Services, so please bear with me here. I am
> curious about how we can configure the database server, database name, and
> login data using data from a properties file so that when Reporting
Services
> and the Report Server start up, they have all the data they need to
connect
> to the appropriate database. Has anyone tried this yet? If so how would
it
> be possible?
> Thanks a bunch,
> Carolyn
> cvoREMOVE_ALL_CAPS@.REMOVE_ALL_CAPSacornsys.com
>

How to query report properties

I would like to get a listing of all my reports and what stored procedures
they use. What would would be the best way to go about this? Is this stored
in the ReportServer database or would I have to query each RDL file?I was not able to find a way to do it, so I'm keeping track manually as I
create sps and reports.
If you find a way, please pass it on!
"DBA72" wrote:
> I would like to get a listing of all my reports and what stored procedures
> they use. What would would be the best way to go about this? Is this stored
> in the ReportServer database or would I have to query each RDL file?