Return to Snippet

Revision: 15475
at July 7, 2009 06:09 by d020


Initial Code
Change owner on sql server db.

Restore database from .bak file

create login
give dbo role.


Run the script:


use db
go

SELECT ‘EXEC(”sp_changeobjectowner @objname = ””’+
ltrim(u.name) + ‘.’ + ltrim(s.name) + ”””
+ ‘, @newowner = NEWOWNER”)’
FROM sysobjects s,
sysusers u
WHERE s.uid = u.uid
AND u.name ‘NEWOWNER’
AND xtype in (‘V’, ‘P’, ‘U’, ‘FN’)
AND u.name not like ‘INFORMATION%’
order by s.name

copy paste result to query analyzer

go

Delete old user

exec sp_changedbowner ‘newowner’

Change login name for dbo to newowner (pwd = same)
bmssa/bmssa

Initial URL


Initial Description


Initial Title
Change owner on sql server db.

Initial Tags
sql, server, security

Initial Language
SQL