Hello,
If I restore a database backup from a foreign system (separate SQL Server in
a separate domain) then the result of:
sp_helpuser @.name_in_db = 'dbo'
is:
UserName GroupName LoginName SID
-- -- --
dbo db_owner NULL <SID>
Hence, 'dbo' is mapped to NULL.
It causes problems if Cross DB Ownership Chaining is enabled, because dbo in
different databases is mapped to different logins.
What is the right way to correct this problem?
So far, I have performed an ad-hoc update to system table sysusers setting
the sid for 'dbo' user to the correct value:
update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
master..syslogins table)
But I've got a feeling this is not the right way...
What should I do?
Thank you for your help!
Best regards,
AndrewHave you used sp_changedbowner before Andrew? provided the id is not a user
in the database, this will changed the dbo.
Chris Wood
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server
> in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo
> in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||You're feeling is right, don't update the system tables. Use sp_changedbowner instead.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:eyKune6kGHA.1028@.TK2MSFTNGP04.phx.gbl...
> Hello,
> If I restore a database backup from a foreign system (separate SQL Server in
> a separate domain) then the result of:
> sp_helpuser @.name_in_db = 'dbo'
> is:
> UserName GroupName LoginName SID
> -- -- --
> dbo db_owner NULL <SID>
> Hence, 'dbo' is mapped to NULL.
> It causes problems if Cross DB Ownership Chaining is enabled, because dbo in
> different databases is mapped to different logins.
>
> What is the right way to correct this problem?
>
> So far, I have performed an ad-hoc update to system table sysusers setting
> the sid for 'dbo' user to the correct value:
> update sysusers set sid = <SID> where name ='dbo' (<SID> retrieved from
> master..syslogins table)
> But I've got a feeling this is not the right way...
> What should I do?
> Thank you for your help!
> Best regards,
> Andrew
>
>
>|||Gentlemen:
Thank you very much for your help.
The funny thing is that the owner of the database is set correctly (e.g. in
the Enterprise Manager --> database properties --> 'General' tab),
only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
Therefore I haven't use sp_changedbowner, but I will give it a try next
time.
Thanks a lot again!
Best regards,
Andrew
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
> You're feeling is right, don't update the system tables. Use
> sp_changedbowner instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>|||This hopefully explains it:
The owner of a data is stored in two places. It is stored in a system table in the master database
(sysdatabases), but it is also reflected in sysusers.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Drake" <andrewdrake@.hotmail.com> wrote in message
news:%23peUt66kGHA.4044@.TK2MSFTNGP03.phx.gbl...
> Gentlemen:
> Thank you very much for your help.
> The funny thing is that the owner of the database is set correctly (e.g. in
> the Enterprise Manager --> database properties --> 'General' tab),
> only the mapping of 'dbo' users is missing (i.e. 'dbo' is mapped to NULL).
> Therefore I haven't use sp_changedbowner, but I will give it a try next
> time.
> Thanks a lot again!
> Best regards,
> Andrew
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:eKvkNp6kGHA.4224@.TK2MSFTNGP05.phx.gbl...
>> You're feeling is right, don't update the system tables. Use
>> sp_changedbowner instead.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>
>
Showing posts with label domain. Show all posts
Showing posts with label domain. Show all posts
Sunday, February 19, 2012
dbo login name wrong after copy
Just copied all databases, from Windows NT4 with SQL 7 in a domain to a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database wizard.
Now a few databases still have a dbo with login name DOMAINNAME/Administrator while this domain doesn't exist anymore in a few weeks from now
1. Can I get a problem when I don't resolve this
2. Is there a way to give the dbo the sa login name"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to a
new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database
wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a few
weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
First off, 'dbo' is a database role, not a user. A role is like a group in
Windows. Anyone you assign to the dbo role in a database can do anything
they want in that database.
'sysadmin' is a server role that can do anything anywhere in your SQL server
(and so is like a super dbo, if you like). By default, the local admin
group and the user sa are both assigned to the sysadmin role, and so can
both act as the dbo of a database (plus additional actions). So there is no
need to add sa to the dbo as it is already more powerful than dbo.
Any other user that you want to have dbo access just add them to the
appropriate database's dbo role. If your DOMAIN/Administrator is going,
then leaving the login there won't be a problem, as no-one will be able to
use it (though for completeness you might want to remove it). You can add
as many other accounts as you feel is safe to the dbo role.
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003|||> First off, 'dbo' is a database role, not a user.
Actually 'dbo' is a database user and is required in every SQL Server
database. Unlike other database users, the login mapping for the 'dbo'
user is determined by database ownership.
This should not be confused with user membership of the db_owner fixed
database role. Members of the db_owner role have similar permissions as
the 'dbo' role but are not mapped to the 'dbo' user. Members of the
sysadmin server role automatically impersonate the 'dbo' user in all
databases, even though their login is not necessarily mapped to the
'dbo' user.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:NjItb.1924$1u4.548@.news-binary.blueyonder.co.uk...
> "Nico" <anonymous@.discussions.microsoft.com> wrote in message
> news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> > Just copied all databases, from Windows NT4 with SQL 7 in a domain
to a
> new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database
> wizard.
> > Now a few databases still have a dbo with login name
> DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few
> weeks from now.
> > 1. Can I get a problem when I don't resolve this?
> > 2. Is there a way to give the dbo the sa login name?
> First off, 'dbo' is a database role, not a user. A role is like a
group in
> Windows. Anyone you assign to the dbo role in a database can do
anything
> they want in that database.
> 'sysadmin' is a server role that can do anything anywhere in your SQL
server
> (and so is like a super dbo, if you like). By default, the local
admin
> group and the user sa are both assigned to the sysadmin role, and so
can
> both act as the dbo of a database (plus additional actions). So there
is no
> need to add sa to the dbo as it is already more powerful than dbo.
> Any other user that you want to have dbo access just add them to the
> appropriate database's dbo role. If your DOMAIN/Administrator is
going,
> then leaving the login there won't be a problem, as no-one will be
able to
> use it (though for completeness you might want to remove it). You can
add
> as many other accounts as you feel is safe to the dbo role.
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003
>|||You can change database ownership to 'sa' with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
If you're not running SQL 2000 SP3, you may get an erroneous error
stating that the proposed owner is already a user in the database. In
that case, temporarily change ownership to a non-conflicting login:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to
a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
>|||Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change the dbo login to sa?|||sp_change_users_login?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <nvk@.galvano.nl> wrote in message news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change
the dbo login to sa?|||Tried this before:
dbo is a forbidden value.
See also books online says sa and dbo cannot be used.
Another suggestion?|||How did you change the owner? sp_changedbowner?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:0E3B442E-0722-4D65-9E54-2DC90AE18C29@.microsoft.com...
> Tried this before:
> dbo is a forbidden value.
> See also books online says sa and dbo cannot be used.
> Another suggestion?|||Perhaps this is an Enterprise Manager refresh issue. Does sp_helpdb
'MyDatabase' return the correct owner?
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <nvk@.galvano.nl> wrote in message
news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the
properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is
there a possibility to change the dbo login to sa?|||Yes, this works fine when I look at the database properties
Just found out a procedure to solve the problem
1. detach database (Query Analyser
2. copy mdf and ldf files (Explorer
3. create database (Query Analyser
4. Delete database (Enterprise Manager
5. copy back mdf and ldf files (Explorer
6. attach database (Query Analyser
Now the dbo user has login name of sa (login name Query Analyser when created database).
Now a few databases still have a dbo with login name DOMAINNAME/Administrator while this domain doesn't exist anymore in a few weeks from now
1. Can I get a problem when I don't resolve this
2. Is there a way to give the dbo the sa login name"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to a
new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy database
wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a few
weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
First off, 'dbo' is a database role, not a user. A role is like a group in
Windows. Anyone you assign to the dbo role in a database can do anything
they want in that database.
'sysadmin' is a server role that can do anything anywhere in your SQL server
(and so is like a super dbo, if you like). By default, the local admin
group and the user sa are both assigned to the sysadmin role, and so can
both act as the dbo of a database (plus additional actions). So there is no
need to add sa to the dbo as it is already more powerful than dbo.
Any other user that you want to have dbo access just add them to the
appropriate database's dbo role. If your DOMAIN/Administrator is going,
then leaving the login there won't be a problem, as no-one will be able to
use it (though for completeness you might want to remove it). You can add
as many other accounts as you feel is safe to the dbo role.
Bob
--
Warning: Do not look into the light sabre whilst switching it on
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003|||> First off, 'dbo' is a database role, not a user.
Actually 'dbo' is a database user and is required in every SQL Server
database. Unlike other database users, the login mapping for the 'dbo'
user is determined by database ownership.
This should not be confused with user membership of the db_owner fixed
database role. Members of the db_owner role have similar permissions as
the 'dbo' role but are not mapped to the 'dbo' user. Members of the
sysadmin server role automatically impersonate the 'dbo' user in all
databases, even though their login is not necessarily mapped to the
'dbo' user.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Bob Simms" <bob_simms@.somewhere.com> wrote in message
news:NjItb.1924$1u4.548@.news-binary.blueyonder.co.uk...
> "Nico" <anonymous@.discussions.microsoft.com> wrote in message
> news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> > Just copied all databases, from Windows NT4 with SQL 7 in a domain
to a
> new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database
> wizard.
> > Now a few databases still have a dbo with login name
> DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few
> weeks from now.
> > 1. Can I get a problem when I don't resolve this?
> > 2. Is there a way to give the dbo the sa login name?
> First off, 'dbo' is a database role, not a user. A role is like a
group in
> Windows. Anyone you assign to the dbo role in a database can do
anything
> they want in that database.
> 'sysadmin' is a server role that can do anything anywhere in your SQL
server
> (and so is like a super dbo, if you like). By default, the local
admin
> group and the user sa are both assigned to the sysadmin role, and so
can
> both act as the dbo of a database (plus additional actions). So there
is no
> need to add sa to the dbo as it is already more powerful than dbo.
> Any other user that you want to have dbo access just add them to the
> appropriate database's dbo role. If your DOMAIN/Administrator is
going,
> then leaving the login there won't be a problem, as no-one will be
able to
> use it (though for completeness you might want to remove it). You can
add
> as many other accounts as you feel is safe to the dbo role.
> Bob
> --
> Warning: Do not look into the light sabre whilst switching it on
>
> --
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.541 / Virus Database: 335 - Release Date: 14/11/2003
>|||You can change database ownership to 'sa' with sp_changedbowner:
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
If you're not running SQL 2000 SP3, you may get an erroneous error
stating that the proposed owner is already a user in the database. In
that case, temporarily change ownership to a non-conflicting login:
USE MyDatabase
EXEC sp_addlogin 'TempOwner'
EXEC sp_changedbowner 'TempOwner'
EXEC sp_changedbowner 'sa'
EXEC sp_droplogin 'TempOwner'
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:84E0FEA3-50AB-400F-981A-9C53951002E5@.microsoft.com...
> Just copied all databases, from Windows NT4 with SQL 7 in a domain to
a new Windows 2000 with SQL 2000 in workgroup, with the SQL 2000 copy
database wizard.
> Now a few databases still have a dbo with login name
DOMAINNAME/Administrator while this domain doesn't exist anymore in a
few weeks from now.
> 1. Can I get a problem when I don't resolve this?
> 2. Is there a way to give the dbo the sa login name?
>|||Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change the dbo login to sa?|||sp_change_users_login?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <nvk@.galvano.nl> wrote in message news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is there a possibility to change
the dbo login to sa?|||Tried this before:
dbo is a forbidden value.
See also books online says sa and dbo cannot be used.
Another suggestion?|||How did you change the owner? sp_changedbowner?
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Nico" <anonymous@.discussions.microsoft.com> wrote in message
news:0E3B442E-0722-4D65-9E54-2DC90AE18C29@.microsoft.com...
> Tried this before:
> dbo is a forbidden value.
> See also books online says sa and dbo cannot be used.
> Another suggestion?|||Perhaps this is an Enterprise Manager refresh issue. Does sp_helpdb
'MyDatabase' return the correct owner?
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Nico" <nvk@.galvano.nl> wrote in message
news:A5285328-1CBE-47E9-BC5F-428460D9F8FC@.microsoft.com...
> Ok. I've changed the dbowner to sa. No problem when I look at the
properties of the database.
> Still I've got the user dbo with login DOMAINNAME/Administrator. Is
there a possibility to change the dbo login to sa?|||Yes, this works fine when I look at the database properties
Just found out a procedure to solve the problem
1. detach database (Query Analyser
2. copy mdf and ldf files (Explorer
3. create database (Query Analyser
4. Delete database (Enterprise Manager
5. copy back mdf and ldf files (Explorer
6. attach database (Query Analyser
Now the dbo user has login name of sa (login name Query Analyser when created database).
Dbo access does not work.
I have a domain user account, DOM\User1, who I have granted dbo rights to
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin rights
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallenI apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
--
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Seems to me like you have done some DENY in the database, or possibly added to db_denydatareader and
db_denydatawriter to the db_owner role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uPjPeOAlGHA.408@.TK2MSFTNGP03.phx.gbl...
> Raymond Lewallen wrote:
>> I have a domain user account, DOM\User1, who I have granted dbo rights to DatabaseA, which is on
>> a server who is a member of the domain DOM as well. User1 can add, remove, alter tables and
>> stored procedures etc, but when User1 attempts to update, select, insert or delete a row from any
>> table in DatabaseA, even if it is a table User1 just created, the user is given a
>> Select/Update/Insert/Delete Permission Denied error, depending on the task.
>> The only way to get past the problem is to give DOM\User1 system admin rights on the server.
>> If I create a Sql Server user, UserSql1, and give that user dbo access to DatabaseA and attempt
>> to Select/Update/Insert/Delete, then it works just fine for UserSql1. Its only the domain
>> accounts that do not work correctly.
>> Any ideas on this?
>> Raymond Lewallen
>> http://www.codebetter.com/blogs/raymond.lewallen
> Have you explicitly DENIED access to any particular domain groups? Does DOM\User1 belong to one
> of those groups?
>
DatabaseA, which is on a server who is a member of the domain DOM as well.
User1 can add, remove, alter tables and stored procedures etc, but when
User1 attempts to update, select, insert or delete a row from any table in
DatabaseA, even if it is a table User1 just created, the user is given a
Select/Update/Insert/Delete Permission Denied error, depending on the task.
The only way to get past the problem is to give DOM\User1 system admin rights
on the server.
If I create a Sql Server user, UserSql1, and give that user dbo access to
DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
fine for UserSql1. Its only the domain accounts that do not work correctly.
Any ideas on this?
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallenI apologize for leaving of this important bit of information:
Windows Server 2003 Enterprise Edition SP1. Sql Server 2000 SP 4.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to
> DatabaseA, which is on a server who is a member of the domain DOM as
> well.
> User1 can add, remove, alter tables and stored procedures etc, but
> when
> User1 attempts to update, select, insert or delete a row from any
> table in
> DatabaseA, even if it is a table User1 just created, the user is given
> a Select/Update/Insert/Delete Permission Denied error, depending on
> the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen|||Hi Raymond
There really isn't anything called 'dbo rights'. DBO is a user name in a
database. You can put another user in the db_owner role, but this doesn't
give them the user name dbo. Can you elaborate on exactly what you granted
to DOM\User1?
Is it possible the Windows user belongs to a Windows group that was given
different access to the server and the database?
What is the value of user_name() when DOM\User1 connects to DatabaseA?
--
HTH
Kalen Delaney, SQL Server MVP
"Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>I have a domain user account, DOM\User1, who I have granted dbo rights to
>DatabaseA, which is on a server who is a member of the domain DOM as well.
>User1 can add, remove, alter tables and stored procedures etc, but when
>User1 attempts to update, select, insert or delete a row from any table in
>DatabaseA, even if it is a table User1 just created, the user is given a
>Select/Update/Insert/Delete Permission Denied error, depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access to
> DatabaseA and attempt to Select/Update/Insert/Delete, then it works just
> fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>|||Raymond Lewallen wrote:
> I have a domain user account, DOM\User1, who I have granted dbo rights
> to DatabaseA, which is on a server who is a member of the domain DOM as
> well. User1 can add, remove, alter tables and stored procedures etc, but
> when User1 attempts to update, select, insert or delete a row from any
> table in DatabaseA, even if it is a table User1 just created, the user
> is given a Select/Update/Insert/Delete Permission Denied error,
> depending on the task.
> The only way to get past the problem is to give DOM\User1 system admin
> rights on the server.
> If I create a Sql Server user, UserSql1, and give that user dbo access
> to DatabaseA and attempt to Select/Update/Insert/Delete, then it works
> just fine for UserSql1. Its only the domain accounts that do not work
> correctly.
> Any ideas on this?
> Raymond Lewallen
> http://www.codebetter.com/blogs/raymond.lewallen
>
Have you explicitly DENIED access to any particular domain groups? Does
DOM\User1 belong to one of those groups?|||Seems to me like you have done some DENY in the database, or possibly added to db_denydatareader and
db_denydatawriter to the db_owner role.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uPjPeOAlGHA.408@.TK2MSFTNGP03.phx.gbl...
> Raymond Lewallen wrote:
>> I have a domain user account, DOM\User1, who I have granted dbo rights to DatabaseA, which is on
>> a server who is a member of the domain DOM as well. User1 can add, remove, alter tables and
>> stored procedures etc, but when User1 attempts to update, select, insert or delete a row from any
>> table in DatabaseA, even if it is a table User1 just created, the user is given a
>> Select/Update/Insert/Delete Permission Denied error, depending on the task.
>> The only way to get past the problem is to give DOM\User1 system admin rights on the server.
>> If I create a Sql Server user, UserSql1, and give that user dbo access to DatabaseA and attempt
>> to Select/Update/Insert/Delete, then it works just fine for UserSql1. Its only the domain
>> accounts that do not work correctly.
>> Any ideas on this?
>> Raymond Lewallen
>> http://www.codebetter.com/blogs/raymond.lewallen
> Have you explicitly DENIED access to any particular domain groups? Does DOM\User1 belong to one
> of those groups?
>
Friday, February 17, 2012
DBO
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
ToddTry:
sp_changedbowner 'sa'
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
Todd|||Hi Todd
Read about sp_changedbowner in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
ToddTry:
sp_changedbowner 'sa'
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
Todd|||Hi Todd
Read about sp_changedbowner in the Books Online.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
quote:
> HI All
> does any one know the SQL code to change the ownership of
> a database from one user to another say: \\Domain\fred to
> SA account.
>
> thanks
> Todd
DBO
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
ToddThis is a multi-part message in MIME format.
--=_NextPart_000_049E_01C3DB83.94E41480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Try:
sp_changedbowner 'sa'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
Todd
--=_NextPart_000_049E_01C3DB83.94E41480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
sp_changedbowner 'sa'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Todd" wrote in message news:01e801c3dbac$e4=8efda0$a601280a@.phx.gbl...HI All does any one know the SQL code to change the ownership of =a database from one user to another say: \\Domain\fred to SA account.thanks Todd
--=_NextPart_000_049E_01C3DB83.94E41480--|||Hi Todd
Read about sp_changedbowner in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
> HI All
> does any one know the SQL code to change the ownership of
> a database from one user to another say: \\Domain\fred to
> SA account.
>
> thanks
> Todd
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
ToddThis is a multi-part message in MIME format.
--=_NextPart_000_049E_01C3DB83.94E41480
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Try:
sp_changedbowner 'sa'
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
HI All
does any one know the SQL code to change the ownership of
a database from one user to another say: \\Domain\fred to
SA account.
thanks
Todd
--=_NextPart_000_049E_01C3DB83.94E41480
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Try:
sp_changedbowner 'sa'
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Todd" wrote in message news:01e801c3dbac$e4=8efda0$a601280a@.phx.gbl...HI All does any one know the SQL code to change the ownership of =a database from one user to another say: \\Domain\fred to SA account.thanks Todd
--=_NextPart_000_049E_01C3DB83.94E41480--|||Hi Todd
Read about sp_changedbowner in the Books Online.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Todd" <anonymous@.discussions.microsoft.com> wrote in message
news:01e801c3dbac$e48efda0$a601280a@.phx.gbl...
> HI All
> does any one know the SQL code to change the ownership of
> a database from one user to another say: \\Domain\fred to
> SA account.
>
> thanks
> Todd
Subscribe to:
Posts (Atom)