January 28, 2010 at 7:20 am
Hello,
I’m trying to delete a user in a SQL 2005 database and I receive the following error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for User ‘user’. (Microsoft.SqlServer.Smo)
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=09.00.3042&EvtSrc=MSSQLServer&EvtID=15138&LinkId=20476
------------------------------
From what I search on the net I need to transfer the ownership to someone. The problem is who should that someone else be? And is there a default “some else” when the system was setup; for example the SA account? I don’t want to just move my problem to another user I want to put things back as they should be.
I have also read on the net about two ways to make this change. One is through the SQL Management Studio GUI and the other with a T-SQL statement from the studio as well.
The T-SQL code I found is:
ALTER SCHEMA schema_name
TRANSFER [ <entity_type> :: ] securable_name [;]
<entity_type> ::=
{Object | Type | XML Schema Collection}
Alter Schema “is the name of the schema db_datareader”
Transfer [ not sure what this means::] securable_name “is this the new owner? [;]
Again not sure what ENTIY_TYPE is ::= and what it’s suppose to equal.
If possible I would like to use the GUI so I can see what I’m doing. Then create the script so I can understand what’s going on here.
The user owns Schemas for db_datareader, db_denydatareader and db_denydatawriter. Who should own these schemas by default?
January 28, 2010 at 8:54 am
By default, those schemas are owned by themselves, so db_datareader is owned by the db_datareader role. I'd just use the schema properties in SSMS to change the owner, then drop the user.
Greg
January 28, 2010 at 11:59 am
Greg, Thanks for the reply that confirms what I found when looking at other databases. Here's what I did to correct my problem:
How to alter the owner of a Schema using the GUI.
1.Expand the list of available databases.
2.Click the Plus sign next to the database in question.
3.Click the Plus sign next to Security.
4.High light the Schema folder
5.On the right side of the split SQL window select (double click) the Schema you wish to change or alter the ownership of.
6.Change the “Schema owner:” to match the owner you want.
7.Click OK
For example:
I mistakenly altered the owner of the db_datareader schema from being db_datareader (the user) to another user (will call him Tom). So I used steps 2-7 to fix all the references of Tom being the owner of the db_datareader schema back to db_datareader (the user).
How to alter the Schema using T-SQL be:
USE [database name]
GO
ALTER AUTHORIZATION ON SCHEMA::[db_datareader] TO [db_datareader]
GO
If I miss spoke with any of the above please correct me. I don’t want to cause any more problems than a user may already have.
February 4, 2010 at 4:33 pm
Here is a script that I wrote for repairing schema ownerships before making bulk security changes. It saves lots of time and avoids having to do this manually. Our policy at work is that system schemas own themselves and all other schemas are owned by [dbo]. Of course, you can edit the WHERE clauses according to your policies and needs.
-- ==========================================================================
-- Repair Schema Owners
-- ==========================================================================
-- ==========================================================================
-- DBO-owned Schemas
-- ==========================================================================
DECLARE @DBOSchemaName SYSNAME;
DECLARE Schema_Cursor CURSOR FOR
SELECT NAME
FROM sys.schemas
WHERE (NAME NOT LIKE 'db_%')
AND (NAME NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys'))
AND (PRINCIPAL_ID != (SELECT SCHEMA_ID FROM sys.schemas WHERE NAME = 'dbo'));
OPEN Schema_Cursor;
FETCH NEXT FROM Schema_Cursor INTO @DBOSchemaName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER AUTHORIZATION ON SCHEMA::[' + @DBOSchemaName + '] TO [dbo]');
FETCH NEXT FROM Schema_Cursor INTO @DBOSchemaName;
END;
CLOSE Schema_Cursor;
DEALLOCATE Schema_Cursor;
-- ==========================================================================
-- Self-owned Schemas
-- ==========================================================================
DECLARE @SelfSchemaName SYSNAME;
DECLARE Schema_Cursor CURSOR FOR
SELECT NAME
FROM sys.schemas
WHERE ((NAME LIKE 'db_%')
OR (NAME IN ('dbo','guest','INFORMATION_SCHEMA','sys')))
AND SCHEMA_ID != PRINCIPAL_ID;
OPEN Schema_Cursor;
FETCH NEXT FROM Schema_Cursor INTO @SelfSchemaName;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC('ALTER AUTHORIZATION ON SCHEMA::[' + @SelfSchemaName + '] TO [' + @SelfSchemaName + ']');
FETCH NEXT FROM Schema_Cursor INTO @SelfSchemaName;
END;
CLOSE Schema_Cursor;
DEALLOCATE Schema_Cursor;
-- ==========================================================================
-- END
-- ==========================================================================
I hope it helps!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply