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 Remove a charater from a SQL Data.

Dear Frineds

I have a hyperlink datatype column in Access database.I import that column into SQL Table.

Now my column in sql table say 'imp_cl 'have all data from Acces table.
But all record are prefix with"#" and at the end of value also have "#".
that means it imported in following way

eg. #//Server/image/img1.pdf#

Now I want to remove this # from both side.As we have around 80,000 record of same type,it is very difficult to do it record by record.

I would like to know aay fuction ,method or programme to remove this "#" from all record.

Thannk you

gracesonDECLARE @.c NVARCHAR(200)

SET @.c = '#//Server/image/img1.pdf#'

SELECT DataLength(@.c), @.c, SubString(@.c, 2, Datalength(@.c) / 2 - 2)
-PatP

How to refresh Access Table from SSIS package?

Hi all,

I have created an SSIS package to export rows of data from SQL to Access using SSIS package. The package is executed from asp.net web application. Below is what i want to achieve:

-User enters a date range

-SSIS package will export data between the date range from SQL to Access database.

-When user enter another date range, I want to clear the contents of the Access database. (Im using Execute Sql Task-- Delete tablename)

The problem is that when I look at the table after the second user request, the fields will show #deleted. Only after i click refresh will the new data appear. How can I make the data appear without manually refreshing the Access table.

Thks alot.

Can't you just drop off the access table and re-create it upon every user request ?

|||

Hi,

dropping the table means that I will need to create a the table again at the OLE DB destination. However I am unable to do that. I tried to use SQL command data access mode to create table. However, I received an error "CREATE TABLE SQL is not supported".

|||

Hi,

if it is not possible to refresh the Access table, can any expert here advise me on how to create a table when executing the package? The only way to go now is to drop the table and create a new table to solve my problem, but i am facing this problem of creating table.

Thks

How to reference different databases on different servers in one query

Hi,

I'm a novice to SQL Server, though I have some experience in Access.

My problem is this:

I have a table on my local database that I want to insert into a remove database on a different server

What are the steps neccessary?

My local server name is 'HOME\SQLEXPRESS' Local database: 'MyDBTest', table name: 'dbo.Courses' (the fields are: ID, CourseName, TeacherID)

The remote SQL Server IP is: 129.113.271.58 (I've changed the real IP) The name of the database on this remote server is: 'ku.univ', and the remote table name I would like to update is 'dbo.Courses' (the table has the same fields as the local one).

I use SQL Server Express Edition

Please help!!

Thank you in advance

Michael

Any ideas?

I'm really stuck here - I am able to connect to the remote server and browse the database, but how do I make an INSERT query to the remote table from my local table?

In Access you could just put any table (local and linked) in one query - there has to be a way to do it in SQL Server

Thanx

|||

There are a couple of options for you. You could use BCP to export the table on the local server to a text file, then use the same utility to insert into the remote server. You can also use a linked server.

To setup a linked server run these commands from sqlcmd, replace the appropriate parameters:

exec sp_addlinkedserver 'RemoteServerName', N'SQL Server'
exec sp_addlinkedsrvlogin 'RemoteServerName',@.locallogin=NULL /*any login*/, @.rmtuser = 'remoteusername', @.rmtpassword='rmtpassword'

Then you will be able to run a query such as this when connected to your local server:

insert [remoteserver].[ku.univ].[dbo].[Tableneme] (columns...)

select columns... from localtable

I'm assuming here that the remote server is also SQL Server. If not you'll need to modify the sp_addlinkedserver call to specify the proper connection string.

How to read windows registry thru query ?

Hi
How can I access the windows registry entries from MSSQL Server query analyzer. I am looking for some stored procedures. Are there any system stored procedures ?
ThxExtended procedure xp_regread
USE Master
EXEC xp_regread 'HKEY_LOCAL_MACHINE',
'SOFTWARE\Microsoft\MSSQLServer\Setup',
'SQLPath'

How to read the names of package variables in custom task

Hi there,
I found a lot posts about package variables. But nothing about how getting access to the
names of the package variables programatically.
Why? - Well I want to create a list of variable names from which the user can choose
one of in a UI of a custom task. That's all... :)
I thought of using the variable dispenser in the Initialize method of the task and then storing the variable names into a property which can be read by the UI through the taskhost.
But it seems that you have to know all your variables by name if you want to use then in a task.
Any idea?
Fridtjof
In your IDtsTaskUI.Initialize method, capture the TaskHost. Store it in a field and pass it through to the form within GetView. To get a list of variables in the form, just use the Variables collection that is exposed on the TaskHost.
|||Thanks again Darren,
I got confused by the VarableService which is available from the IServiceProvider in the contructor of the UI form which does not lead to a solution.
I found some sample code in BOL to read out the variable names:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9mref/html/P_Microsoft_SqlServer_Dts_Runtime_Variables_Item_1_b4c5a2da.htm
CU
Fridtjof
|||

You should use the LockForRead when accessing them from the component class. However, on the UI it is easiest to "Variables vars" collection to access the SSIS Package Variables.

e.g. vars["Name_Of_Variable"].Value

Stuart

How to read the names of package variables in custom task

Hi there,
I found a lot posts about package variables. But nothing about how getting access to the
names of the package variables programatically.
Why? - Well I want to create a list of variable names from which the user can choose
one of in a UI of a custom task. That's all... :)
I thought of using the variable dispenser in the Initialize method of the task and then storing the variable names into a property which can be read by the UI through the taskhost.
But it seems that you have to know all your variables by name if you want to use then in a task.
Any idea?
FridtjofIn your IDtsTaskUI.Initialize method, capture the TaskHost. Store it in a field and pass it through to the form within GetView. To get a list of variables in the form, just use the Variables collection that is exposed on the TaskHost.
|||Thanks again Darren,
I got confused by the VarableService which is available from the IServiceProvider in the contructor of the UI form which does not lead to a solution.
I found some sample code in BOL to read out the variable names:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/dtsref9mref/html/P_Microsoft_SqlServer_Dts_Runtime_Variables_Item_1_b4c5a2da.htm
CU
Fridtjof

How to read database compatibility level from c#

I'm trying to access the db compatibility level of a database from c# code.
If you execute sp_dbcmptlevel with no parameters, the current level is
returned to the output stream. My problem is how to get at this from c#
since it is not a scalar or tabular result. Any help gratefully received
JamesLook like it's returned via raiserror(). You need to catch it via
InfoMessage.
http://msdn.microsoft.com/library/d...ctionEvents.asp
-oj
"James E. Clarke" <james@.r> wrote in message
news:OacPMeLOGHA.2268@.TK2MSFTNGP09.phx.gbl...
> I'm trying to access the db compatibility level of a database from c#
> code. If you execute sp_dbcmptlevel with no parameters, the current level
> is returned to the output stream. My problem is how to get at this from c#
> since it is not a scalar or tabular result. Any help gratefully received
> James
>

How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

http://www.databasejournal.com/features/mssql/article.php/10894_3331881_2

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1467861&SiteID=1

|||

For openrowset function to work properly in SQL Server 2005, you need to check "Enable OPENROWSET and OPENDATASOURCE Support" using SQL Server Surface Area Configuration for Features.

Later you can use the following command to access csv or text files;

SELECT * FROM

OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=D:\mmc;', 'SELECT * from test.csv');

Regards,

Salman Shehbaz.

|||

we can also enable 'Adhoc Distributed Queries' option using the following code;

EXEC sp_configure 'show advanced options', 1

GO

RECONFIGURE

GO

EXEC sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE with override

GO

How to read CSV File in SQL Server 2005 using OpenRowSet Function

Hi

i want to access a CSV file using OpenRowSet function in SQL Server 2005.

Anyone having any idea; would be of great help.

Regards,

Salman Shehbaz.

here's a similar post, you might want to try the query posted here

How to query for transaction's isolation level...

Hi all,
Is it any way to query a running transaction to see the isolation level it
is using? I'm debugging a mobile .net application that access a SQL 2K
database and I need to verify if it is running transaction on the desired
isolation level (read uncomitted). My idea is to run a test app that left a
transaction opened, and test the isolation level by running some query from
the QueryAnalyzer.
Any hint is welcomed
Thanks in advance
SammyThis is one of the rows from DBCC USEROPTIONS
"SammyBar" <sammybar@.gmail.com> wrote in message
news:%23Bt97iglGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Is it any way to query a running transaction to see the isolation level it
> is using? I'm debugging a mobile .net application that access a SQL 2K
> database and I need to verify if it is running transaction on the desired
> isolation level (read uncomitted). My idea is to run a test app that left
> a transaction opened, and test the isolation level by running some query
> from the QueryAnalyzer.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>|||Hi, Sammy
To determine the transaction isolation level currently set for a given
connection, execute the DBCC USEROPTIONS statement from that
connection.
Razvan|||> This is one of the rows from DBCC USEROPTIONS
but can I make a "DBCC USEROPTIONS" not for my own connection, but for
anoter process or spid?|||>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
Not that I know of. If you're interested in it for a specific procedure,
you could probably jam data into a table based on SPID at the beginning of
the proc, then you can query it for any active spids from other sessions.
However, if you're able to modify the proc to do this, you could probably
just check the proc manually to see if the default isolation level is being
overriden.
A|||In SQL Server 2005, the view sys.dm_exec_sessions (one of the replacements
for sysprocesses) shows the isolation level for every connection.
HTH
Kalen Delaney, SQL Server MVP
"SammyBar" <sammybar@.gmail.com> wrote in message
news:eGfdsQhlGHA.3528@.TK2MSFTNGP02.phx.gbl...
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
>

How to query for transaction's isolation level...

Hi all,
Is it any way to query a running transaction to see the isolation level it
is using? I'm debugging a mobile .net application that access a SQL 2K
database and I need to verify if it is running transaction on the desired
isolation level (read uncomitted). My idea is to run a test app that left a
transaction opened, and test the isolation level by running some query from
the QueryAnalyzer.
Any hint is welcomed
Thanks in advance
SammyThis is one of the rows from DBCC USEROPTIONS
"SammyBar" <sammybar@.gmail.com> wrote in message
news:%23Bt97iglGHA.4708@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> Is it any way to query a running transaction to see the isolation level it
> is using? I'm debugging a mobile .net application that access a SQL 2K
> database and I need to verify if it is running transaction on the desired
> isolation level (read uncomitted). My idea is to run a test app that left
> a transaction opened, and test the isolation level by running some query
> from the QueryAnalyzer.
> Any hint is welcomed
> Thanks in advance
> Sammy
>
>|||Hi, Sammy
To determine the transaction isolation level currently set for a given
connection, execute the DBCC USEROPTIONS statement from that
connection.
Razvan|||> This is one of the rows from DBCC USEROPTIONS
but can I make a "DBCC USEROPTIONS" not for my own connection, but for
anoter process or spid?|||>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
Not that I know of. If you're interested in it for a specific procedure,
you could probably jam data into a table based on SPID at the beginning of
the proc, then you can query it for any active spids from other sessions.
However, if you're able to modify the proc to do this, you could probably
just check the proc manually to see if the default isolation level is being
overriden.
A|||In SQL Server 2005, the view sys.dm_exec_sessions (one of the replacements
for sysprocesses) shows the isolation level for every connection.
--
HTH
Kalen Delaney, SQL Server MVP
"SammyBar" <sammybar@.gmail.com> wrote in message
news:eGfdsQhlGHA.3528@.TK2MSFTNGP02.phx.gbl...
>> This is one of the rows from DBCC USEROPTIONS
> but can I make a "DBCC USEROPTIONS" not for my own connection, but for
> anoter process or spid?
>

How to query a view from the master DB?

Is there a way to access your views in x database from the master database?

I'm getting this error

Invalid object name 'v_StatisticsScalars'.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Invalid object name 'v_StatisticsScalars'.Use the fully qualified path <database>.<owner>.<view
Or you can use "use"|||cant use USE in stored procedures can you?

im using system stored procedures.....and i thought the context stayed at the database calling the system stored procedure? am i wrong?|||i really dont want to use x.y.z because im using these stored procedures for a few different sites. it would be good if i could add the view to the master database. can that be done? i tried....but then it says my tables dont exist. :/ -so now im back at square1|||Now I'm confused too. I understand that you're relucant to use the fully qualified path. I don't understand what you mean by "I'm using system stored procedures". You can create a view to access other database table but we're back to using the fully qualified path again! If the database was on a different server (or server instance) you could setup a linked server. Apart from that I think you're stuck. Well you could construct some dynamic sql and exec that to get around the 'different sites' issue. In fact just have a util prog that generates the procs/views based upon the database names you want to use.

How to query a remote server from sql 2005

Ok,,im stuck.
I can access a remote 2000 sql server from sql 2005. I connect directly to
the 2000 database ok. I can run queries on it all day.
I can do the same thing with another remote ODBC server with SQL2005.
THe question is how do you login to SQL2000 from sql 2005 and yet still be
able to run queries on the other remote ODBC server?
--
HarryTry using opendatasource which will accept a connection string to access the
remote server.
--
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry|||Create linked servers and use openquery() method.
"Omnibuzz" wrote:
> Try using opendatasource which will accept a connection string to access t
he
> remote server.
> --
>
>
> "Harry" wrote:
>|||Create linked servers and use OpenQuery() method
"Harry" wrote:

> Ok,,im stuck.
> I can access a remote 2000 sql server from sql 2005. I connect directly t
o
> the 2000 database ok. I can run queries on it all day.
> I can do the same thing with another remote ODBC server with SQL2005.
> THe question is how do you login to SQL2000 from sql 2005 and yet still be
> able to run queries on the other remote ODBC server?
> --
> Harry