Posted By

d020 on 07/07/09


Tagged

sql server security ax


Versions (?)

Change owner on sql server db.


 / Published in: SQL
 

  1. CHANGE owner ON sql server db.
  2.  
  3. Restore DATABASE FROM .bak file
  4.  
  5. CREATE login
  6. give dbo role.
  7.  
  8.  
  9. Run the script:
  10.  
  11.  
  12. USE db
  13. go
  14.  
  15. SELECT ‘EXEC(”sp_changeobjectowner @objname = ””’+
  16. ltrim(u.name) +.+ ltrim(s.name) + ”””
  17. +, @newowner = NEWOWNER”)
  18. FROM sysobjects s,
  19. sysusers u
  20. WHERE s.uid = u.uid
  21. AND u.name ‘NEWOWNER’
  22. AND xtype IN (‘V’, ‘P’, ‘U’, ‘FN’)
  23. AND u.name NOT LIKE ‘INFORMATION%’
  24. ORDER BY s.name
  25.  
  26. copy paste result TO query analyzer
  27.  
  28. go
  29.  
  30. DELETE old user
  31.  
  32. exec sp_changedbowner ‘newowner’
  33.  
  34. CHANGE login name FOR dbo TO newowner (pwd = same)
  35. bmssa/bmssa

Report this snippet  

You need to login to post a comment.