Drop Database User who Owns Schema

Scenario :

UserName : GPxTestUser is owner of db_datawriter schema, and we wanted to drop the user

Once u made a user as schema owner (this is a common problem when we create Database user using SSMO, because by mistake everybody check the schema instead of Database Role) , then there is no way to revert back or change the owner of schema from "Database User" window. This can confuse a newbie. Though, I feel it should not be allowed to change the Owner of Schema from Database User Window and it should be done from "Schema Property Window" only. What i would say is this option of choosing schema owner should be removed form "Database User" window altogether to avoid confusion.

When we try to drop a database user who owns some schema from Database the system will throw the following Error :-

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

First you should see the Schemas and the curresponding owners

SELECT s.name SchemaName, d.name SchemaOwnerName FROM sys.schemas s INNER JOIN sys.database_principals d ON s.principal_id= d.principal_id


Drop USER gpxtestuser

Msg 15138, Level 16, State 1, Line 1
The database principal owns a schema in the database, and cannot be dropped.

Resolution :-

Alter the Schema Owner to any other Prinicpal; here I am changin owner of DB_Datawriter as DB_Datawriter (earlier it was GPxTestUser)

ALTER AUTHORIZATION ON SCHEMA::[db_datawriter] TO [db_datawriter]

Then the user can be droped

To Transfer/Change the ownership of a object to another Schema.

If you want to drop a Schema , first you should transfer the object owned by this schema to another schema otherwise, when you drop schema you will get a messge as mentioned below :-

Msg 3729, Level 16, State 1, Line 1
Cannot drop schema 'SCH1 ' because it is being referenced by object 'TAB1 '.

Now, before droping the schema SCH1 , the obeject(s) need to be transfered to another schema.

Syntax : ALTER SCHEMA Target_schema_name TRANSFER object_name

The below statement will transfer the Ownership of TAB1 from SCH1 to DBO

ALTER SCHEMA dbo TRANSFER SCH1.TAB1

in the same manner , transfer all the objects to whichever schema you want and then drop the schema.

Dynamic Management Views that can be used to monitor and troubleshoot performance issues in SQL Server.

Add Prefix Sys. to view

dm_exec_cached_plans : Provides information about the query execution plans that are cached by SQL Server for faster query execution.

dm_exec_connections :Provides information about the connections established to SQL Server on various endpoints.

dm_exec_sessions :Contains one row per authenticated session on the SQL Server instance.
dm_exec_query_stats :Provides aggregate performance statistics for cached query plans.

dm_exec_query_optimizer_info : Provides detailed statistics about the operation of the SQL Server query optimizer.

dm_io_pending_io_requests :Contains a row for each pending I/O in the system.

dm_os_loaded_modules : Contains a row for each module loaded into the server address space.

dm_os_memory_cache_counters :Provides a general snapshot of the health of the cache.

dm_os_memory_cache_entries : Enables you to view all entries in caches and their statistics.

dm_os_performance_counters :Lists SQL Server 2005 Performance Monitor counters and their current value.

dm_os_waiting_tasks :Provides information on the wait queue of tasks that are waiting on some resource.

dm_tran_locks :Returns one row for every active request to the lock manager that has either been granted or is waiting to be granted (that is, the request is blocked by an already granted request).

dm_tran_current_transaction :Returns a single row that displays state information of the transaction in the current session.

dm_tran_active_transactions :Provides information about active transactions.

dm_clr_properties :If the CLR is enabled, provides information such as CLR version, directory, state, and so on.

dm_clr_appdomains :Returns a row for each CLR application domain in the server.

dm_clr_loaded_assemblies :Contains a row for each CLR user assembly loaded into the server address space.