Deadlocks

I have in a table with some 2000 records with a Document ID which is a Unique Seq Number and details with a Status Flag. Multiple Users access the Table. When a user accesses a particular Document ID the Status flag changes from "N" to "W". Once he finishes working with data on that particular Document ID the Status Changes to "C". When another user tries to pickup a Record the next record in the Seq with the Status as "N" will get fetched. I use a Stored Procedure to assign a Document id to a user and update the Record/status details in the Table. I have used the No lock clause in the SP while retrieving a particular Document ID. It was working fine till sometime. Now it has started giving the following error

Error: Run-time error '-2147467259(80004005)'

[Microsoft][ODBC SQL Server Driver][SQL Server]Your transaction(process ID#127)was deadlocked with another process and as been chosen as the deadlock victim. Return your transaction.

Some one please Help me on how to go abt the problem. Thanks in advance.
Regards
Dinesh1. Run sp_recompile 'object_name' for all objects used.
2. Post it all. SP+DDL

Good luck !

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
--
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

Deadlocking Limitations of SQL Server... tell me it isn't so.

Hi,
I have a client-server .NET system that uses an Enterprise Services
Serviced Component (COM+ component) for data access. Under high load,
I am getting deadlocking errors, they seem to be related to one table.
These situations are hard to debug, but I am guessing it is because
an update on a delete may be occurring on DIFFERENT ROWS in the same
table at the same time. This can't be right, can it?
I read something about problems when using indexes, but this table is
not indexed other than the primary key. The table definition is shown
below. Any suggestions would be appreciated.
Thanks!
*** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
ACCURATE) ***
CREATE TABLE [Boo_Record_Foo] (
[Boo_Id] [int] NOT NULL ,
[Fooed_By_User_Id] [int] NULL ,
[Fooed_By_User_Name] [varchar] (30) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
(
[Boo_Id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
*** ERROR MESSAGE ***
Transaction (Process ID 53) was deadlocked on {lock} resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.COM+ tends to use the SERIALIZED isolation level which is never good for
multi-user apps. I would check to see what the isolation level is on all
the connections. You say your table has no index other than the PK
constraint. Is it ever accessed by anything other than the PK? Can you
show the 2 statements that are being used when it deadlocks?
Andrew J. Kelly
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.|||Hi Don.
You can get the precise reason for the deadlock by writing it's detailed
deadlock report to the SQL error log & inspecting that report. It's complex
to analyse, but if you post it back perhaps we could help you analyse it.
To write the detailed deadlock report to the error log, issue the following
command:
dbcc traceon (1204, 3605, -1)
1204 is the trace flag for detailed deadlock reports
3605 is the instruction to write that report to the sqwl error log
-1 is the instruction that the trace should apply to all connections, not
just the current connection that is issuing the dbcc traceon command.
Regards,
Greg Linwood
SQL Server MVP
"Don MacKenzie" <cd_mackenzie@.hotmail.com> wrote in message
news:2544f4a.0402131647.7bbd58cf@.posting.google.com...
> Hi,
> I have a client-server .NET system that uses an Enterprise Services
> Serviced Component (COM+ component) for data access. Under high load,
> I am getting deadlocking errors, they seem to be related to one table.
> These situations are hard to debug, but I am guessing it is because
> an update on a delete may be occurring on DIFFERENT ROWS in the same
> table at the same time. This can't be right, can it?
> I read something about problems when using indexes, but this table is
> not indexed other than the primary key. The table definition is shown
> below. Any suggestions would be appreciated.
> Thanks!
> *** TABLE DEFINITION (FIELDS HAVE BEEN RENAMED, BUT STRUCTURE IS
> ACCURATE) ***
> CREATE TABLE [Boo_Record_Foo] (
> [Boo_Id] [int] NOT NULL ,
> [Fooed_By_User_Id] [int] NULL ,
> [Fooed_By_User_Name] [varchar] (30) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> CONSTRAINT [PK_Boo_Record_Foo] PRIMARY KEY CLUSTERED
> (
> [Boo_Id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> GO
>
> *** ERROR MESSAGE ***
> Transaction (Process ID 53) was deadlocked on {lock} resources with
> another process and has been chosen as the deadlock victim. Rerun the
> transaction.

Deadlock, Rerun the transaction

I always get the following error, when someone is trying
to to access our live site, the database recoirds are
more, how to get away with this problem.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction
(Process ID 53) was deadlocked on {lock | communication
buffer} resources with another process and has been chosen
as the deadlock victim. Rerun the transaction.Hi Bharathi,
This might be useful for you to solve the issue.
http://www.sql-server-performance.com/deadlocks.asp
Sankar Renganathan
DBA, SPAR Group Inc.,
Please reply only to the newsgroups.
This posting is provided AS IS with no warranties, and confers no rights.
"Bharathi" <vamsi5@.hotmail.com> wrote in message
news:022201c3d6fa$6ba96ee0$a601280a@.phx.gbl...

quote:

---

> I always get the following error, when someone is trying
> to to access our live site, the database recoirds are
> more, how to get away with this problem.
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction
> (Process ID 53) was deadlocked on {lock | communication
> buffer} resources with another process and has been chosen
> as the deadlock victim. Rerun the transaction.
>

---

|||very fuzzy! I got a similar error o a DTS that export a table in a .XLS: ...
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005)
Error string: Transaction (Process ID 55) was deadlocked on lock resou
rces with another process a
nd has been chosen as the deadlock victim. Rerun the transaction. Error
source: Microsoft OLE DB Provider for SQL Server Help file: He
lp context: 0 Error Detail Records: Error: -2147467259 (80004005
); Provider Error: 1205 (4
B5) Error string: Transaction (Process ID 55) was deadlocked on lock r
esources with another process and has been chosen as the deadlo... Process
Exit Code 1. The step failed.
I got the error both on scheduled JOB (by night) that run the DTS and starti
ng the job NOW...
So I run a trace with SQL Profiler and it works... ;-) It is not the first t
ime that tracing a process to find an error... the process doesn't fail
So...: "Fear it! And it will works!"
Ciao
Leonardo
****************************************
******************************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET
resources...

Deadlock accessing variables

I am trying to access a single variable in a script and a deadlock error continues to come up. I have a single string variable that is added to the readwritevariables collection in the editor. I am trying to execute the following code:

Dim variables As Variables

Try

Dts.VariableDispenser.LockForWrite("Test")

Dts.VariableDispenser.GetVariables(variables)

Catch ex As Exception

Throw ex

Finally

variables.Unlock()

End Try

I have installed Service Pack2 and this error continues. I know it has been posted on before and I appreciate any help.

Thanks

You shouldn't need to lock the variable in your script if you've added it in the editor. Try either removing the lock in your script or removing the variable in the editor.|||

Thank you. It took me awhile but I figured it out. If I was smart enough to read the error I would have figured it out sooner.

Thanks again

deadlock

Hi,
I have created a VB program to perform DTS tasks for data transfer from
ACCESS database to SQL Server. The DTS are created by saving the actual DTS
packages as VB file and used those bas files in VP app to run the DTS
programmatically.
The process works fine most of the time.
But occassionally the DTS process is getting locked. The two connections
from the DTS package to SQL server database gains DB lock on the database
and so the process does not go further .
Can any body know why this is happenning?
what could be the solution for this problem?
please help.
thanks
SamHow are you sure it's a deadlock instead of a normal blocking?
You can set up trace flag -T1204 and -T3605 in the start up parameters on
the SQL server and capture deadlock details in the SQL error log. Or if you
have lumigent Log Explorer, you can set up alert on deadlocks. YOu can also
use SQL profiler to capture a trace using Locks:Deadlocks and Lock:Deadlock
Chain.
Richard
"Sam" <samirsoni@.hotmail.com> wrote in message
news:elDzAt6MFHA.3340@.TK2MSFTNGP14.phx.gbl...
> Hi,
> I have created a VB program to perform DTS tasks for data transfer from
> ACCESS database to SQL Server. The DTS are created by saving the actual
> DTS
> packages as VB file and used those bas files in VP app to run the DTS
> programmatically.
> The process works fine most of the time.
> But occassionally the DTS process is getting locked. The two connections
> from the DTS package to SQL server database gains DB lock on the database
> and so the process does not go further .
> Can any body know why this is happenning?
> what could be the solution for this problem?
> please help.
> thanks
> Sam
>
>

deadlock

hello
im inserting some records from access file to sql server table with for loop
but oafter inseting some records i get this error
please help me
thank you
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
was deadlocked on lock | communication buffer resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.Hi
Any activities during the inserting? How do you perform this batch?
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>|||javad ebrahimnezhad (sorena@.parskhazar.net) writes:
> im inserting some records from access file to sql server table with for
> loop but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the
> transaction.
Apparently there is other activitity on the server that your insert process
collides with. You should contact your DBA. If he already have enabled
deadlock tracing, you might be enable to identify the issue by checking
the error log.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||It might work better bulk inserting the MS Access data using a DTS package.
"javad ebrahimnezhad" <sorena@.parskhazar.net> wrote in message
news:eCwO15KIGHA.516@.TK2MSFTNGP15.phx.gbl...
> hello
> im inserting some records from access file to sql server table with for
> loop
> but oafter inseting some records i get this error
> please help me
> thank you
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 87)
> was deadlocked on lock | communication buffer resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>

DBxtra Data explorer

DBxtra Data explorer
DBxtra version 1 is ready.
You can connect to unlimited MS Access, MS SQL Server, Paradox, PDF and Excel tables and queries.
Get the sense out of your data!1
1. Connect to your data
2. Explore your data
3. Design and deploy your reports
4. Export your data
5. Send your data by E-mail
6. Schedule reports and alerts
Try the free Download!
http://www.dbxtra.comADVERTISING

I think i am going to post ads for commercial items that i believe in

"hey you got you chocolate in my peanut butter"
"You got your peanut butter on my chocolate"
"two great tastes that taste great together"
reeses peanut butter cups :eek:|||Hey...whats with the dead chick on the couch?

http://www.dbxtra.com/images/p1.jpg|||I've got another idea for a great commercial venture.

Gasoline powered "adult" toys!

What a marketing opportunity! We can all get rich, very quickly... Live lives of leisure, travel the world, sample the best of everything. We can be rich I tell you, just rich if we all work together to tap this as yet unexplored marketing opportunity!!!

On a (very slightly) more serious note, will an admin please move this thread to the Marketplace (http://www.dbforums.com/f188) where it belongs?

-PatP|||But if will have to marketed for outdoor use only...

Maybe the chick on the couch was using one of your products and passed out from the exhaust...|||pat i hate to tell you this but we already have gasoline powered adult toys.

porsche
ferrari
jaguar
lamborghini
etc.|||pat i hate to tell you this but we already have gasoline powered adult toys... and look at the money that people will spend on them! I tell you, there is a fortune to be made here if we can just find the right products and advertising medium!!!

Somebody, PLEASE move this to the MarketPlace (http://www.dbforums.com/f188)!

-PatP

DBO Thing Doesn't Appear by Table Name

Hi, we are trying to simply import an existing MS Access 2003 table to MS SQL. I've gone through the export routine (the "upsizing" wizard in Access), but my Coldfusion app does not recognize the resultant MS SQL table as a real database object. It doesn't even include DBO next to the table name like all the other "real" tables that were made directly from within MS SQL. Is there something else I'm supposed to do? Thanks - and yes, I'm new at this. ;)
You can post your response here but feel free to drop me a direct reply at my website (which should hopefully be included in this message).For anyone that's interested, it appears that DBO may actually stand for Database Owner, not Database Object as I had previously speculated. Not sure how that helps resolve my issue per se, but there you have it. If anyone can offer a pointer on the original post it would be great, thanks!
Dave|||Hi,

dbo does stand for database owner. To help you determine what is going on with your application use SQL Server Profiler to trace the the queries that your application is issuing against the server. You can then copy the queries from Profiler to Management Studio and then run them manual to determine exactly why the query is failing.

Keep in mind for the owner of the object, if the owner of the object is not specified in the query, SQL server will search for dbo first.

If you have specific questions, please let us know.

Peter

dbo schema added when i rename table

i recorded a script for a change i need to make. actually 15 so far, i am getting ready to bring an access db with no pk or fk and only 1 relation over to ss05

my scripts are used to add the need pk fk to the tables and then move the data from the temptbl to the new one

1 thing i have been noticing is code like below will rename the table dbo.aMgmt.Employee and with that all the remaing lines of the script will fail.

DROP TABLE aMgmt.Employee
GO
EXECUTE sp_rename N'dbo.Tmp_Employee', N'aMgmt.Employee', 'OBJECT'
GO

any help?Unclear what is happening or what you are trying to do.

What did you use to record the script? What was the original ownership on the tables? What were you logged in as when you scripted the objects?

Who do you WANT to have ownership of the objects? I assume dbo...|||It may be becoz u r trying to copy/create the data using a dbo user.

DBO Query

Is there a query that can be executed to check if the currently logged in
user has dbo access to the current database?
I need to know this prior to adding tables.
Thanks in advance.
Hi Isaac,
Yes, use is_member() like in
if is_member('db_owner') = 1
-- do something
You may also want to take a look at is_srvrolemember.
Hope this helps,
Ben Nevarez
"Isaac Alexander" wrote:

> Is there a query that can be executed to check if the currently logged in
> user has dbo access to the current database?
> I need to know this prior to adding tables.
> Thanks in advance.
>
>
|||To get the logged in user a member of the fixed database role, look up
IS_MEMBER() function. If you want to know if a particular user belongs to a
particular database role, try the check:
IF EXISTS ( SELECT *
FROM sysmembers s1
JOIN sysusers s2 ON s2.uid = s1.memberuid
JOIN sysusers s3 ON s3.uid = s1.groupuid AND s3.issqlrole = 1
WHERE s3.name = @.role AND s2.name = @.user )
Anith
|||> if is_member('db_owner') = 1
> -- do something
That worked perfectly. Thanks.

DBO Query

Is there a query that can be executed to check if the currently logged in
user has dbo access to the current database?
I need to know this prior to adding tables.
Thanks in advance.Hi Isaac,
Yes, use is_member() like in
if is_member('db_owner') = 1
-- do something
You may also want to take a look at is_srvrolemember.
Hope this helps,
Ben Nevarez
"Isaac Alexander" wrote:
> Is there a query that can be executed to check if the currently logged in
> user has dbo access to the current database?
> I need to know this prior to adding tables.
> Thanks in advance.
>
>|||To get the logged in user a member of the fixed database role, look up
IS_MEMBER() function. If you want to know if a particular user belongs to a
particular database role, try the check:
IF EXISTS ( SELECT *
FROM sysmembers s1
JOIN sysusers s2 ON s2.uid = s1.memberuid
JOIN sysusers s3 ON s3.uid = s1.groupuid AND s3.issqlrole = 1
WHERE s3.name = @.role AND s2.name = @.user )
--
Anith|||> if is_member('db_owner') = 1
> -- do something
That worked perfectly. Thanks.

dbo qualified name error w Access 2003

I am the owner of a database with other users. I have given all users
db_owner role. My server is MSDE 2000.
I use the following code to import a spreadsheet.
docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel97,
"dbo.DL_Funding_Data", "theFilename.xls", True
When I use an adp from Access 2000, this code executes successfully for all
users. When I use an adp from access 2003, this code gets error 3078 and
will only work if I remove the "dbo." from the name of the table.
thanks for any help,
Jerry
As I recall (and it's been a few years) the syntax required for
specifying SQL Server objects changed between A2000 and 2003. In 2000
you had to use the full syntax, ownername.objectname and 2003 is more
lenient. Since many people didn't bother implementing security
correctly, things ended up being owned by dbo, which is *not*
synonymous with db_owner, but is mapped to sysadmin. Something you
definitely don't want users being. FWIW, I definitely wouldn't use
DoCmd for DDL or DML, ever. It's just not a robust way of handling
things.
One tool you should use is SQL Profiler. Creating a trace allows you
to see the calls between Access and SQL Server, and is indispensable
for troubleshooting these things.
--Mary
On Mon, 22 Aug 2005 15:17:03 -0700, JerryWendell
<JerryWendell@.discussions.microsoft.com> wrote:

>I am the owner of a database with other users. I have given all users
>db_owner role. My server is MSDE 2000.
>I use the following code to import a spreadsheet.
>docmd.transferspreadsheet acImport, acSpreadsheetTypeExcel97,
>"dbo.DL_Funding_Data", "theFilename.xls", True
>When I use an adp from Access 2000, this code executes successfully for all
>users. When I use an adp from access 2003, this code gets error 3078 and
>will only work if I remove the "dbo." from the name of the table.
>thanks for any help,
>Jerry
|||Thanks for the recommendations Mary. I'll look into removing DoCmd and
obtaining the SQL Profiler.
I'm waiting to get my users all at Access 2003 to minimize the version
problems.
Thanks!
"Mary Chipman [MSFT]" wrote:

> As I recall (and it's been a few years) the syntax required for
> specifying SQL Server objects changed between A2000 and 2003. In 2000
> you had to use the full syntax, ownername.objectname and 2003 is more
> lenient. Since many people didn't bother implementing security
> correctly, things ended up being owned by dbo, which is *not*
> synonymous with db_owner, but is mapped to sysadmin. Something you
> definitely don't want users being. FWIW, I definitely wouldn't use
> DoCmd for DDL or DML, ever. It's just not a robust way of handling
> things.
> One tool you should use is SQL Profiler. Creating a trace allows you
> to see the calls between Access and SQL Server, and is indispensable
> for troubleshooting these things.
> --Mary
> On Mon, 22 Aug 2005 15:17:03 -0700, JerryWendell
> <JerryWendell@.discussions.microsoft.com> wrote:
>
>

Dbo access does not work.

I 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.lewallenHi 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
>|||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 right
s
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|||I 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?|||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?|||Hello Kalen,
db_owner role is the group the domain account has been assigned access to.
Sorry for the confusion there, in the sql circles I've been in over the
last 10 years, 'dbo rights' have always been understood as the db_owner grou
p.
No windows groups other than BUILTIN\Administrators have been given any expl
icit
rights, and the admins have sa rights.
The value of user_name is DOM\User1 when the user connects.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen
[vbcol=seagreen]
> 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?
> "Raymond Lewallen" <rlewallen@.gmail.com> wrote in message
> news:fffd68f614ddba8c861dae82718bc@.news.microsoft.com...
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>|||Hello Tracy,
No windows groups have been given any rights, whether access or deny, to
the sql server or any of its databases. The only windows group on the entir
e
server is BUILTIN\Administrators, which has sa rights.
Raymond Lewallen
http://www.codebetter.com/blogs/raymond.lewallen

> Raymond Lewallen wrote:
>
> Have you explicitly DENIED access to any particular domain groups?
> Does DOM\User1 belong to one of those groups?
>

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?
>

dbo access

Hi,
We have a sql 2000 machine that we use for our internal
intranet. It houses about 10 applications. Each db need
a user that an application can use. Is it advisable to
make those particular users dbo as they need privs to
create tables, sproc, views etc...
What is the best policy that other dba are using
ThanksPlease review this:
Microsoft SQL Server 2000 SP3 Security Features and Best Practices
http://www.microsoft.com/technet/tr...chnet/prodtechn
ol/sql/maintain/security/sp3sec/default.asp
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?docsql wrote:
> On our dev server the app developers have been granted dbo access to
> their individual databases. They do not have sa rights on the Dev
> SQL Server. The problem is that when the app developers create new
> objects, they are the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects
> are qualified with the owner name in the code. For example
> user1.table.
> But when we rollout the changes in production, since the dba creates
> the objects the owner is the dbo and the application stops working. How
> can i reoslve this problem without giving sa access to the
> developer on the dev db servers?
Yes, by always fully qualifying object names:
Create Table dbo.MyTable(...)
David Gugick
Quest Software

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?
Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>