September 16, 2003 at 4:00 pm
Hi All,
This may sound trivial but it's driving my crazy that I can't get it to work.
As the DBA, I have sysadmin rights so when I script out stored procedures from my dev server (using enterprise manager) and run it on my production server, all objects are created with dbo as the owner. I log into query analyzer as myself not sa and run the script.
But when one of my developers (I know it's bad practice but our department is skrinking rapidly and I need someone else that can do this) tries to do the same, all objects are created with his account as the owner not dbo. I have him set up as part of the db_owner role as well as permission to create procedures.
Can any one explain this to me? I don't understand what else I need for it to work the same. Or, how can I set the scripting from enterprise manager to specify dbo on the create statements. It does it on the drops but not the creates. When we do large push ups using EM is the easiest way to ensure we don't miss anything.
Any ideas or suggestions are most welcome.
Running SQL 2000 sp3 on advanced server using integrated security.
Thanks
K
September 16, 2003 at 4:57 pm
The best practice here is to make sure that all his scripts to create the objects use the dbo.objectname. That way you will always know they are created correctly.
I use Visual Studio as my dev environment and I have a template for each of the object types I want to create. This way I can easily create a script that has my comment block, a drop statement, and the create statement all set up. I just add the object name in the appropriate places and add the specific code I want to add.
I do all my updates to production via scripts that have been checked out very carefully. I feel this is by far the safest way.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 16, 2003 at 5:03 pm
That does work and I canimplement this as part of our standards.
But I still don't understand why it creates them as dbo for my NT account but no others.
September 16, 2003 at 7:49 pm
Any object created by any member of the sysadmin fixed server role belongs to dbo automatically. Because you are the member of 'sysadmin' so objects created by you will belong to DBO.
But your developer has only DBO right in the database, He can create objects that belong to DBO by using qualify owner name like dbo.table1. If he does not specify qualify name DBO, objects will be created and owned by himself.
September 17, 2003 at 7:09 am
There's no way I can give him sysadmin due to some of the other data on the server so I'll just have to ensure the developers use use dbo. when creating any procedures.
Thanks for clarifying.
K
September 17, 2003 at 9:00 am
You can also alias him to DBO and avoid the issue that way.
Andy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply