SQL Server

SQL Server Error database principal owns schema cannot be dropped

Recently i was working on one of my SQL Server database project and i got this error message

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

I spent good couple of hours to dig the problem and finally find the solution. The reason for error is quite clear from the error message as there were schema associated with the user and that needs to be transferred to another user.

Lets suppose we have a database 'SharePoint2010' and we are trying to delete user 'uahmed'

Run following query against SharePoint2010 database

USE SharePoint2010;

SELECT s.name
FROM sys.schemas s
WHERE s.principal_id = USER_ID('uahmed');

As a result i am getting these two schema

SQL Server Error database principal owns schema cannot be dropped

Now we need to alter authorization of this schema to other schema so that we could drop database owner. In our scenario we have to run alter authorization query twice.

ALTER AUTHORIZATION ON SCHEMA::db_denydatareader TO dbo;

ALTER AUTHORIZATION ON SCHEMA::db_denydatawriter TO dbo;

Now if you will try to drop database owner it will not throw error message.

I hope this post will help you to solve this error message quickly without wasting any time. Please post your comments thanks

Read more...

Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Latin1_General_CI_AS”

I got this error when creating tables from one datase to another SQL Server database today.

How to solve it:
  1. Change to single-user mode in your database
Command to change  Single-User mode or Mutilple-User mode in sql server
database
ALTER DATABASE [Works] SET MULTI_USER WITH
NO_WAIT
ALTER DATABASE [Works] SET SINGLE_USER WITH
NO_WAIT
     2.  Change the collation of from Latin1_General_CI_AS to SQL_Latin1_General_CP1_CI_AS
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS"

SQL Server Database Properties

   3.  Drop and re-create those tables which are still using collation Latin1_General_CI_AS in their columns.
This step is necessary the table level is still sticking to the previous collation
     4.  Restore the data of those tables.
Read more...

Save (Not Permitted) Dialog Box

Save (Not Permitted) Dialog Box  SQL Server 2008 R2
The Save (Not Permitted) dialog box warns you that
saving changes is not permitted because the changes you have made require the
listed tables to be dropped and re-created.
The following actions might require a table to be re-created:
  • Adding a new column to the middle of the table
  • Dropping a column
  • Changing column nullability
  • Changing the order of the columns
  • Changing the data type of a column
To change this option, In sql sever management studio on the Tools menu, click Options, expand
Designers, and then click Table and
Database Designers. Select or clear the Prevent saving
changes that require the table to be re-created check box.
Read more...