CODEWITHSUNDEEP
sql-serversql-server-2008
user2062811
user2062811

Reputation: 1

how to remove schema ownerships from orphaned users when schema doesn't exist

I have a situation where a database was restored from one instance into a new one without bringing over the master db. Consequently all security information is lost. Oddly client can still access data via trusted connections but when attempting to do userid administration, you can not delete an account because it says it is owner of a schema. In the user databases you can get a long list of schemas for just about every user account of the same name. A dump of sys.schema shows that none of these user schemas exist (obviously they would have been in the original instance's master database but not the new one). Since the schema doesn't exist how do you make the userid disown a non-existing entity so that it can in turn be deleted?

I have to this for 132 users so I am looking for a repetitive script as well. Per research on this site I saw script to change owner to dbo but in that situation the schema existed. In my situation it doesn't. Any help is much appreciated.

Upvotes: 0

Views: 1243

Answers (1)

Mike Fal
Mike Fal

Reputation: 1196

One of two things might be going on:

  1. You might be checking sys.schemas in a different database. The schemas might exist in the target database, but if you're selecting from sys.schemas in master, you'll get different results.
  2. Schemas do not have to have the same name as the database user that owns them.

To clean this up, I would use the following code/approach:

Create a .txt file of your user names, each user on a different line.

Use the following SQL code:

use [copied_db];

CREATE TABLE #users
(uname varchar(100));

BULK INSERT #users FROM 'C:\test\users.txt';

SELECT 'DROP SCHEMA ' + QUOTENAME(s.name,'[') + ';'
FROM #users u
    JOIN sys.database_principals dp ON (u.uname = dp.name)
    JOIN sys.schemas s ON (dp.principal_id = s.principal_id);

SELECT 'DROP USER ' + QUOTENAME(dp.name,'[') + ';'
FROM #users u
    JOIN sys.database_principals dp ON (u.uname = dp.name);

This code will provide you with the drop statements for the individual actions so you can run them at your discretion.

Upvotes: 1

Related Questions