Unable to change the schema owner

Please tell me how can we change the schema owner using T-SQL and perform delete action on orphaned users in database.

For the first part

ALTER SCHEMA NewSchema TRANSFER OldSchema.TableName

For the second part, you’ll need to run something like this in each database instance, then call exec sp_revokedbaccess for each user. A cursor would work to let you loop it programatically…

SELECT u.name 
  FROM master..syslogins l 
 RIGHT JOIN sysusers u ON l.sid = u.sid 
 WHERE l.sid IS NULL 
   AND issqlrole <> 1 
   AND isapprole <> 1   
   AND u.name NOT IN ('INFORMATION_SCHEMA', 'guest', 'system_function_schema')
1 Like

I ran this query is this right way to perform delete action.

USE schema_name;
SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID(‘schema_name’);

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.