Changing object owner

• icester

  Ten Centuries

  Points: 1093

  More actions

  January 16, 2017 at 11:06 am

  #325471

  Hello SSC,

  Does changing ownership of an object require an instance reset, or can it be done live without causing any problems? (maintenance plans, databases, agent jobs, etc.)  I want to change the owner domain\user to 'sa' on hundreds of servers but need to make sure it won't cause any problems.  These would be SQL Server 2008 and up.

  Thanks,
  Dan

• Lowell

  SSC Guru

  Points: 323504

  More actions

  January 16, 2017 at 12:12 pm

  #1923040

  changing the owner for object or jobs are just  object level schema lock; no instance wide affects. if someone is connecting or updating , you might have to wait for a transaction to complete, but it's just a wait.

  i use ALTER SCHEMA {schemaname} TRANSFER dbo.TableName all the time, in a loop, as i move data in and out of schemas.

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• Lowell

  SSC Guru

  Points: 323504

  More actions

  January 16, 2017 at 12:15 pm

  #1923041

  ifilter - Monday, January 16, 2017 11:06 AM

  Hello SSC,

  Does changing ownership of an object require an instance reset, or can it be done live without causing any problems? (maintenance plans, databases, agent jobs, etc.)  I want to change the owner domain\user to 'sa' on hundreds of servers but need to make sure it won't cause any problems.  These would be SQL Server 2008 and up.

  Thanks,
  Dan

  also i wrote this snippet to identify potential offenders:
  

  --check all databases
select
 'Database Not owned by sa' As Issue,
  name,
  suser_sname(dbz.owner_sid) As TheOwner ,
  CASE
  WHEN suser_sname(dbz.owner_sid) <> 'sa'
  THEN 'ALTER AUTHORIZATION ON DATABASE::' + name + ' to sa;'
  ELSE ''
  END As cmd
from sys.databases dbz
WHERE suser_sname(dbz.owner_sid) <> 'sa'
order by dbz.name;

  IF OBJECT_ID('tempdb.[dbo].[#tmp]') IS NOT NULL
DROP TABLE [dbo].[#tmp]
CREATE TABLE [dbo].[#tmp] (
[DatabaseName] NVARCHAR(128)        NULL,
[AsObjectName] SYSNAME         NOT NULL,
[ObjectType]  NVARCHAR(60)         NULL)

  INSERT INTO #tmp([DatabaseName],[AsObjectName],[ObjectType])
EXEC sp_msForEachDb 'SELECT ''[?]'' As DatabaseName, name AsObjectName, type_desc AS ObjectType FROM [?].sys.objects WHERE principal_id IS NOT NULL'

  SELECT 'object not owned by sa' AS Issue,* FROM #tmp

  SELECT
  'objects not in dbo schema' As issue,
  sch.[name] AS schema_name,
  objz.[name] AS table_name,
  'ALTER SCHEMA dbo TRANSFER ' + quotename(sch.[name]) + '.' + quotename(objz.[name]) + ';' AS cmd
FROM
  sys.objects AS objz JOIN
  sys.schemas AS sch
   ON objz.[schema_id] = sch.[schema_id]
WHERE sch.schema_id > 4


  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• icester

  Ten Centuries

  Points: 1093

  More actions

  January 16, 2017 at 1:49 pm

  #1923054

  This is a great script, very handy! Thanks for the help Lowell.

  - Dan