Drop Failed for User in SQL server

Sometimes its irritating getting the same error again and again, where we feel we cant anything more than that.

When ever trying to delete a user from the database, you will get to see this error below.

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

The error itself is the meaning. It can't be dropped as it owns a schema.

Solution : -- Simple.. :)

Go to the security under the Database where you wanted to delete the user from,

goto users --> Right Click on the username--> select Properties

Now you can see clearly in General Tab

                                  Schemas Owned by this user:

          So please note the selected 'schemas' here, It doesn't matter if it is greay/inactive.

Now exit out from that window. 

(OR)
Simple Query : 
                         SELECT *, 'UserName' as ownerName
                         FROM sys.schemas s
                         WHERE s.principal_id = USER_ID('UserName')

Now you have to do the following :

Go to Schamas under security, select the schamaName which you have noted down above or from the query result. Right Click on it and select the properties. Now you can see the that same username placed as Schema Owner in General Tab

                                                Replace that username with dbo and click OK button.

Do the same for all the schema's you have noted above.

Now you can be able to delete the user.

You have other processes to do this, This is just one of them.

Please give me your feed back. New ideas most welcome.

SmartScreen Filter

This probably not related to sql.
Some times when you are trying to download a file from trusted site, where you have been downloading from the site for a while.
Suddenly you kick of with the following message.

I have got the same and I went for the details of the error. I have been told SmartScreen Filter is stopping that download.

All you have to do is make sure the site/file is not harmful and disable the SmartScreen Filter from your browser. Which started delivering from 'Internet Explorer 9' to provide more security.

Open Internet Explorer --> Tools --> SmartScreen Filter --> Turn Off SmartScreen Filter...
Please look at below...

Fig: FromTools

Fig: TurnOff

Latest Backup and Restoration Dates on Databases

These are the small scripts which we can use to find when did your database got backup lastly and When did  your database got restored.

--The following script will bring all the databases in the server when they got last back 'd up.


SELECT B.name as Database_Name, ISNULL(STR(ABS(DATEDIFF(day, GetDate(), 
MAX(Backup_finish_date)))), 'NEVER') as DaysSinceLastBackup,
ISNULL(Convert(char(10), MAX(backup_finish_date), 101), 'NEVER') as LastBackupDate
FROM master.dbo.sysdatabases B LEFT OUTER JOIN msdb.dbo.backupset A 
ON A.database_name = B.name AND A.type = 'D' GROUP BY B.Name ORDER BY B.name

--  The following script will give you little bit more information about its restoration.
          It will give you the restoration Date, where it is restored from and also importantly the backup start date and End date.


SELECT RH.destination_database_name, RH.restore_date, BS.backup_start_date, 
  BS.backup_finish_date, BS.database_name as source_database_name, 
  BMF.physical_device_name as backup_file_used_for_restore
FROM msdb..restorehistory RH
 INNER JOIN msdb..backupset bs ON RH.backup_set_id = BS.backup_set_id
 INNER JOIN msdb..backupmediafamily BMF  ON BS.media_set_id = BMF.media_set_id
ORDER BY RH.restore_date DESC