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

Table Backup with native SQL, Process 4

-- Table Backup with native SQL, Process 4
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see Process 4 : We discussed other 3 ways in earlier posts..Process1, Process2 and Process3

So two steps first copy the skeleton/Structure/schema of the table and in next step load that table.

       Step 1:

SELECT * FROM OriginalTab_Backup FROM OriginalTab where 1 = 2
You can use any type of comparison in where condition, those should not equal.

It will just print the structure of the 'OriginalTab' table in 'OriginalTab_Backup'.
      
       Step 2:

Now we have to load the table, We can use few other ways to do so. But I am using the below.
INSERT INTO OriginalTab_Backup SELECT * FROM OriginalTab

You can also use Bulk Insert to load the table, which is most used one. I will post this in future.
By using this process, You can take the backup of full table or selected columns.
like below..

SELECT Col1,Col2,Col3 FROM OriginalTab_Backup FROM OriginalTab where 1 = 2

INSERT INTO OriginalTab_Backup SELECT  Col1,Col2,Col3 FROM OriginalTab.

Table Backup with native SQL, Process 3

-- Table Backup with native SQL, Process 3
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
 Original table, Insert into newtable select * from backtab)

Here we can see  Process 3 : Please click here for Process1 or Process2
You can do this same job by using a simple T-SQL line.

SELECT * INTO OriginalTable_Bak FROM OriginalTable

Note: By using this process you can backup the table data. You cant get keys, references and indexes directly.

Describe INTERSECT and EXCEPT

INTERSECT and EXCEPT

To better understanding please follow this.
Intersect :  It will give you mutual records from Left query and Right query of the Intersect.
Example: 1) A Intersect B or you can follow the below example queries.

Except : It will give you only the left query or Left table result. It means, wouldn't count the mutual records
Example: 1) A Except B -- All the rows from A, which avoids If any same rows from B.


Table A has 50 rows  10 of them are copied from Table B
Table B has 70 rows 20 of them are copied from Table A
                  So Now A INTERSECT B : 10 + 20 = 30
                   
                  So A EXCEPT B : 50 - 30 = 20
                  So B EXCEPT A : 70 - 30 = 40


Small example :

I have database named mydb, consists 5 tables. I got Full backup on 08/10/2010
2 weeks later I had to compare the database with the old backup.
I have installed the database on the same server with different name as mydb_Test.

Note: Of Course If you have the old database available on one of your test servers, which had configured linked server set up .. (No need to install the database again..) Just use the Fully Qualified Object Name as LinkedServerName.DatabaseName.sys.tables
Where,LinkedServerName : should be one of the row from the following query.
            select name as ServerName from sys.servers


Now, My job is to findout the new objects introduced in the current version of
the database as well the dropped objects. Such as Tables, Stored Procs, Triggers, Functions..

Of course, We definitely have couple of other procedures. But I have used this and gotten results fine.

----------------Tables

-- Find new tables

SELECT Name AS NewTables FROM mydb.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name FROM myDB_Test.sys.tables
ORDER BY name


-- Find Mutual tables

SELECT name AS MutualTables FROM myDB_Test.sys.tables 

INTERSECT
SELECT name  FROM mydb.sys.tables
ORDER BY name

-- Find Dropped tables

SELECT Name AS DroppedTables FROM myDB_Test.sys.tables
EXCEPT   -- Will give all the extra tables from mydb.
SELECT Name AS NewTables FROM mydb.sys.tables
ORDER BY name

This is the same procedure to find stored procedures and Triggers. Just replace tables with procedures and triggers in the code above.

For the functions, Please follow..


SELECT name AS NewFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS MutualFunctionName ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'
INTERSECT
SELECT name,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'

SELECT name AS DroppedFunction ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb_Test.sys.objects WHERE type_desc LIKE '%FUNCTION%'
EXCEPT
SELECT name ,SCHEMA_NAME(schema_id) AS schema_name,type_desc
FROM mydb.sys.objects WHERE type_desc LIKE '%FUNCTION%'

Table Backup with native SQL, Process 2

-- Table Backup with native SQL, Process 2
Version : SQL 2008 or later.

Table Backup.
You can get the result in 4 types...
 Process 1 : Generating scripts
 Process 2 : Import/Export Option (T-SQL/SSIS)
 Process 3 : Inserting into new table (Select * into newtab from baktab)
 Process 4 : Inserting into existing table (Create newtable with same structure as
Original table, Insert into newtable select * from backtab)

Here we can see Process 2 :
You have an option with Import/Export Wizard.
See below how it works..  

Right Click on the database Tasks -> and click Import Data... (OR) Export Data...as below

fig : 1 DBProperties_rightClick
both bring the same window as below...

fig : 1 a) Import and Export Wizard
Cleck next and Choose the source, where you wanted to retrive data from(SQL,EXCEL,Flat File and etc...) see below.

fig : 2 Select Source

Click next and Choose the destination, where you wanted to load the data(SQL,EXCEL,Flat File and etc...) see below..

fig : 3 Select Destination

Choose one of this as the following figure....

fig : 4 Type

Now you have to configure the destination file as bleow.. As I have selected my destination is Flat file, I just need to select Row Delimiter and Column delimiter from the drop down.

fig : 5 Configuring Destination

So, Now you have two options here, Select Run Immediately to have the import/export task done now.
Or you can check the other box as Save SSIS Package and will let you run whenever you want in future.
Please look at below.

                                           fig : 6 Save and Run

fig : 7 Finish

fig : 8 Result