dbo problem
-
For good or bad, our developers used to have sysadmin role in our development server. Every object they created had the owner of dbo.
Now, they no longer have sysadmin and every object they create makes their shared sql login the owner ('devl').
The problem is that it is a real pain for the DBAs to change the object owner every time they have a table or stored proc to copy to the test or production server.
Is there any way for them to create objects that are owned by 'dbo'?
Or to change the owner during the copy procedure? We use the DTS transfer wizard to copy objects (I don't know any other way).
Thanks for any help you can provide.
Randy.
-
In order for them to create objects owned by dbo they need to be in the db_owners group and prefix the names of all objects with dbo.
DTS won't change ownership of objects, but you could script them out and then do a search/replace on the script file(s). This wouldn't be my first choice as it is very prone to error.
Frankly the best option that I have ever come across (and the one I use now) is to not allow developers to create objects anyway. That is a job for DBA's who should know enough to prefix the names and grant the appropriate permissions etc...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
you can also script
sp_changeobjectowner
'objectname', 'dbo'
OR
if you are willing to give the developers that kind of control use
sp_addalias @loginame = 'developerLogin', @name_in_db = 'dbo'
after that, all objects will be created by them will have dbo
DON'T FORGET TO USE sp_dropalias OR AT LEAST DOCUMENT WHO WAS ALIASED
hth
* Noel -
A method I've used previously that works well is to have the developers use a SQL Server instance installed on their workstation which they have sysadmin privelages to. This stops the whining as they can do whatever they like but they can't touch any other environments.
Then all subsequent production/test/qa changes a run through a central "merge" point where the DBA reviews prior to executing on the target environment.
--------------------
Colt 45 - the original point and click interface
-
1. Require them to prefix all objects dbo.
2. MY DBAs have a nightly job that checks for any objects created other than dbo and automatically mails the culprit about it. Sorry, I don't have the script - I leave that as an exercise for the reader!
-
use datbase name
update sysobjects set uid = 1 where uid = 6
This is writing straight to the system catalog which works perfectly but isn't advised by Microsoft.
-
...and will not work with SQL 2005...
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
Oh no! Sounds like my nasty-and-illadvised-but-very-useful fixes to system tables may not work under SQL 2005!
A slightly more elegant and safe option than updating sysobjects is to use it to generate a script full of sp_changeobject commands:
select 'exec sp_changeobjectowner ''' + name +''', dbo'
from sysobjects
where user_name(uid) <> 'dbo'
You could add a check on type or xtype for tables, views, procedures etc.
IMHO objects owned by users other than dbo are a real pain, I can't see the point at all. There's a very good article about it somewhere on SQLServerCentral by Steve Jones.
-
Is he using 2005 ?
-
Thanks for all the great responses.
This has been one Frantic Friday so I haven't had a chance to test them yet.
I am using SQL2000.
-
I assume that they are using 2000, however, with 2005 right around the corner, it is prudent to think about it and plan accordingly.
/*****************
If most people are not willing to see the difficulty, this is mainly because, consciously or unconsciously, they assume that it will be they who will settle these questions for the others, and because they are convinced of their own capacity to do this. -Friedrich August von Hayek
*****************/
-
I finally got back to fixing the ownership.
Basically, I am having the developers create the procedure with dbo as the owner.
CREATE PROCEDURE [dbo].[spRAB_test] AS
SELECT TOP 100 * FROM cntrl_Client
GO
Then I am cleaning up by scripting all the stored procs that aren't owned by dbo and dropping and creating them with dbo.procname.
Thanks for all your suggestions.
Randy
-
You can delete your post if you click the edit button. / then delete
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply