database principal owns a schema in the database, and cannot be dropped

Hi ALL,

Some times we need to drop the users on Database, during the phase we may encounter the below situtation like:

The database principal owns a schema in the database, and cannot be dropped. (Microsoft SQL Server, Error: 15138)

Check if any schmes are pointed user (unable to delete)

select * from sys.schemas where principal_id = user_id('USERNAME')

if you find any thing: do the transfer those dbo by executing the below command.

SELECT N'ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;'
FROM sys.schemas
WHERE principal_id = USER_ID('USERNAME');

NOTE: copy the above output and execute...

After doing this, you are able to DROP the desrired users.... :)

Thanks,
Satish Kumar.

Finding Database with MDF and LDF file sizes

Hi All,

Some times we will notify the requests like databases sizes with MDF and LDF files with locations. At the case please use the beloe script:

SELECT DB_NAME(mf.database_id) AS databaseName,
mf.physical_name,
size_on_disk_bytes / 1024 / 1024 AS size_in_MB
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS divfs
JOIN sys.master_files AS mf ON mf.database_id = divfs.database_id
AND mf.file_id = divfs.file_id
ORDER BY databaseName

Note***: If the value is in 0 (zero) means - the size is less than 1 MB because the sizes are calculated in MB’s.

Thanks,
Satish Kumar.