Solved

DBO Owner vs. other users as the owner

Posted on 2004-08-27
5
1,339 Views
Last Modified: 2008-01-09
I manage an MSSQL 2000 Windows 2000 server with about 100 databases on it for various customers.  All of these customers manage their databases remotely via Enterprise Manager and scripts on their Web Sites that connect to the server.  Each database has a unique login that is in the public and db_owner roles of a particular database.  The problem is that some customers complain that when they create tables or transfer them from elsewhere with DTS, the owner is either dbo or the custom user for their database.  Which user is the owner varies based on reasons I am not aware of.  Some customers complain they cannot do certain things because the owner is dbo and others complain about the owner not being dbo.

Can someone explain what causes this discrepancy of owner and also what limitations this puts on functionality either way?

Thanks in advance,
Dan
0
Comment
Question by:easycgi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 70

Expert Comment

by:Qlemo
ID: 11932546
If you log into sql server with an user different than the db creating user,

(1) you need explicitly granted rights to access objects in this database

(2) all operations will first be applied to objects defined for that user, than for dbo. Creating tables will create a table for the user logged in, not for dbo.

Main approach to this is: use one account for schema definitions (this will be named internally as 'dbo'), another one for querying data.
0
 

Author Comment

by:easycgi
ID: 11942997
Let me explain a specific situation in a little more detail.  We create the databases for the users manually logged into Enterprise Manager with the sa account.  We create their database and a login which has public and db_owner rights to that database only.  The customer then logs into the db server with Enterprise Manager remotely and creates his tables, objects, etc.  The problem is that he has scripts that reference all his tables as dbo like the following:

ALTER TABLE [dbo].[tbl_groupmaster]

When he creates his tables with his user (cdss), he would have to modify all his scripts to reference his tables like this:

ALTER TABLE [cdss].[tbl_groupmaster]

Is there any remedy or workaround for this?
0
 
LVL 70

Expert Comment

by:Qlemo
ID: 11943233
(1) You could change ownership "manually":

declare csr cursor for select creator+'.'+name from sysadm.systables where creator <> 'DBO' and name not like 'SYS%' for read only;
declare @tbl varchar(32);
declare @sel nvarchar(4000);
open csr;
fetch csr into @tbl
while @@fetch_status = 0
begin
   print  @tbl;
   exec sp_changeobjectowner @tbl, 'dbo'
   fetch csr into @tbl
end
deallocate csr
commit;

Maybe (didn't try this) you might put this into a stored procedure, which could be executed by your client after creating things. I don't know which rights your client will need to do this, but this should be easy for you to find out :-)

(2) First create the user, than create the db as corresponding user.Each object created as tgha

0
 
LVL 70

Accepted Solution

by:
Qlemo earned 500 total points
ID: 11943270
Sorry.
(2) First create the user, than create the db as corresponding user. Ech object createed as that user will be owned by dbo.
0
 

Author Comment

by:easycgi
ID: 11944136
Option number 2 solved my problem.  Thanks so much.
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question