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.

Blocked Process Threshold Option – A useful New Feature of SQL Server 2005

SQL Server 2005 introduces a new advanced system configuration (sp_configure) option called "blocked process threshold." We can use this option to proactively monitor blocking and deadlocking instances. You can use sp_configure to set the blocked process threshold to an integer value between 0 (the default) and 86400 (24 hours). If you have set the blocked process threshold configuration value to 15. Now if there is a blocking for over 15 seconds, SQL Server will raise an event that can be captured by using the SQL Server event notification mechanism to perform an action, and the event can also be seen in SQL Profiler. The event is raised every 15 seconds after that until the blocking is resolved.

SQL Server raises the BLOCKED_PROCESS_REPORT event when there is a blocking for 15 seconds and every 15 seconds after that until blocking is resolved. This event can be captured by using the event notification mechanism introduced in SQL Server 2005. The event notification mechanism captures the event and notifies a Service Broker service by placing a message in the queue


Reference : http://msdn2.microsoft.com/en-us/library/ms181150.aspx