March 17, 2003 at 4:31 pm
When our developers create stored procs they become the owners of the proc. I use sp_changedbobjectowner to make the 'dbo' the owner. Is there a script I can use to do this automatically?
TIA,
Bill
March 17, 2003 at 5:46 pm
something like
declare @n varchar(128)
while exists (select * from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA <> 'dbo')
begin
select @n = ROUTINE_SCHEMA + '.' + ROUTINE_NAME from (select top 1 ROUTINE_SCHEMA, ROUTINE_NAME from INFORMATION_SCHEMA.ROUTINES where ROUTINE_SCHEMA <> 'dbo') as a
select 'changed owner ' + @n
exec sp_changeobjectowner @n, 'dbo'
end
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
March 18, 2003 at 2:11 am
quote:
When our developers create stored procs they become the owners of the proc.
If your developers have the necessary permissions I would tell them to create their procedures with dbo as owner.
CREATE PROC dbo.someproc ...
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 18, 2003 at 10:51 am
You could make the developers owners of your development DB and eliminate this maintenance need for the future.
March 19, 2003 at 1:32 am
quote:
You could make the developers owners of your development DB and eliminate this maintenance need for the future.
Making them members of db_owner actually won't help, objects will still be created with their own user as the owner. It does however let them specify dbo as owner when they create objects. You might of course have meant specifying their login account as owner of the database (not adding their user account to db_owner role as I first thought), but if there are many developers with different login accounts that won't work.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 19, 2003 at 4:03 am
Hi,
You can also alias your users dbo of the database thanks to the following script:
use <<My_database>>
exec sp_grantdbaccess '<<login_name>>'
exec sp_addalias '<<login_name>>', 'dbo'
So, your user will create stored procedures as DBO. A login can only have ONE alias in a database. But the DBO user can be aliased by several users.
This solution is not supported by Microsoft and can't be easily viewed by SQL EM, but it works.
Tom
March 19, 2003 at 5:30 am
Tom, very nice. Didn't think of that at all. Remember though that sp_addalias is only there for backward-compatibility so it might be removed.
--
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
March 19, 2003 at 6:02 am
Doesn't help if you want the developers to own their SPs during development and only change the owner later though.
Cursors never.
DTS - only when needed and never to control.
Cursors never.
DTS - only when needed and never to control.
March 19, 2003 at 7:12 am
sp_addalias will perfectly suit your requirement. I am managing all my DEV Servers with this for a long time without any issues. In DEV all the developers will be aliased to dbo. But I wanted to get away with this very shortly, because of the following pitfalls:
1. A dbo can even DROP the database. Don't be surprised if you don't see a database one fine morning. Be very well equipped with a good Backup strategy.
2. Developers are more tempted to create Temporary tables (for taking backup of the table with a temporary name), when they do any work on any table. But they normally don't bother cleaning after the work is completed. This will unneccassarily fill the space.
3. They can clear the transaction log thenselves....
etc..... They will be able to do everything on a database as an owner. So be VERY CAREFUL of using alias.
Please let me know your thoughts!!!!
Regards,
Murali Damera.
.
March 19, 2003 at 7:46 am
In the other hand, you can take another way to benefit of a "two-tier" database:
1- Developpers create their own objects, they are the owner of. They can practice tests on their objects without influencing the others. If an object doesn't exist in the user's schema, SQL try to find the DBO's version.
2- In the DBO's schema you have a schema similar to the production one. It helps to validate the developpers objects. You create a user without its own objects and it will try to use the DBO's objects. So you have a quality environment.
The DBA's job consists in validating scripts to change their owner from the user to the DBO. Or You recompile the script of the object with the DBO profile. it ca,n be done thanks to a batch.
Tom
March 20, 2003 at 12:13 pm
Thanks everyone for your reply! The first reply was exactly what I needed!
thanks
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply