Object ownership of dbo members

Question

Object ownership of dbo members

alext

Valued Member

Points: 67
More actions
August 3, 2006 at 4:24 am

#169653

I am currently locking down the company wide use of the sa account and moving to windows authentication. I want to keep all objects that developers and users create to being owned by dbo. Even though these windows logins would map to users in the dbo database role any objects created would appear to default to being owned by their account and not dbo, unless explicitly stated in the create statement. Is there any way around this as historic upgrade scripts to our application would all need to be changed to account for this.
Many Thanks

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

One way round it is to have the objects created as owned by their creators, but then changing ownership afterwards. The script below will create a view in your database called ObjectsByOwner and then find all objects not owned by dbo and change them. Beware, though - if there are any objects that you don't want changed to dbo ownership when you run this script, you'll need to take steps to avoid it.

John

--This creates a view in your database called ObjectsByOwner.

--You only need to run this section of the script once

CREATE VIEW ObjectsByOwner AS

SELECT table_schema AS Owner, table_name AS ObjectName, table_type AS ObjectType

FROM information_schema.tables

UNION

SELECT routine_schema, routine_name, routine_type

FROM information_schema.routines

GO

--Check for the same object name with more than one owner and raise error if found

USE MyDB

IF (SELECT COUNT(ObjectName) FROM ObjectsByOwner

GROUP BY ObjectName

HAVING COUNT(*) > 1) > 0

BEGIN

SET nocount ON

RAISERROR ('One or more objects exist under more than one owner. Please correct before proceeding.', 16, 1)

PRINT ''

SELECT ObjectName FROM ObjectsByOwner

GROUP BY ObjectName

HAVING COUNT(*) > 1

SET nocount OFF

END

--If no objects not owned by dbo, nothing to do

ELSE IF (SELECT COUNT(*) FROM dbo.ObjectsByOwner WHERE Owner <> 'dbo') = 0

BEGIN

PRINT 'Nothing to do - all objects owned by dbo.'

END

--Change owner for those objects not owned by dbo

ELSE BEGIN

DECLARE @owner sysname

DECLARE @objname sysname

DECLARE @objtype VARCHAR(12)

DECLARE @fqname sysname

DECLARE cur_tables CURSOR FOR

SELECT Owner, ObjectName, ObjectType

FROM dbo.ObjectsByOwner

WHERE Owner <> 'dbo'

OPEN cur_tables

FETCH next FROM cur_tables

INTO @owner, @objname, @objtype

WHILE @@fetch_status = 0

BEGIN

PRINT 'Changing owner for ' + LOWER(@objtype) + ' from ' + @owner + ' to dbo...'

SET @fqname = @owner + '.' + @objname

EXEC sp_changeobjectowner @fqname, 'dbo'

FETCH next FROM cur_tables

INTO @owner, @objname, @objtype

END

CLOSE cur_tables

DEALLOCATE cur_tables

RAISERROR ('Ownership of one or more objects changed to dbo.', 16, 1)

END

alext Valued Member Points: 67 More actions · Answer 2

Thanks very much for the reply and script. Very usefull!

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 3

Beware though ... sp_change_objectowner does not work for SPs !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 4

Actually, it does... but you must include the object owner as the object name... in other words, you have to use the 2 part naming convention when changing the ownership of SPs.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 5

Who says you can't teach an old dog new tricks ... Thanks Jeff !

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."