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

On a recent audit of a database I noticed that there were a lot of logins for a database that were not required.  I set about removing the logins one by one, but soon hit the following error:

Error: 15138 The database principal owns a schema in the database, and cannot be dropped.

Well this threw me a bit, and I could not figure out what was going on and why, this one particular login could not be removed.  As the message indicates, this account owns a schema on the DB. So I opened the user properties from SSMS and sure enough, the user  in question owned three schemas: db_datareader, db_datawriter, db_owner.  However, SSMS will not simply allow you to unselect the checkboxes here, so it is still not so easy to remove this link and allow the user to be deleted!

The schemas owned by the user can also be displayed by running the following query, within the context of the database under scrutiny:

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

For a more complete picture of all schemas and users associated with the database:

SELECT *, USER_NAME(principal_id) as username
FROM sys.schemas;


In order to remove this user we have to first transfer ownership of these schemas to a different user.  Typically this would be dbo.  This has to be done once for each schema owned, using the ALTER AUTHORISATION ON SCHEMA command:

 ALTER AUTHORISATION ON SCHEMA::db_datareader TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_datawriter TO dbo
 ALTER AUTHORISATION ON SCHEMA::db_owner TO dbo

(note the double colon!)

Re-run the previous scripts to ensure that the user in question no longer owns any schemas.  Now if we again try the delete again, this user will be removed successfully.

SQL Server Global Variables: @@Rowcount, @@Error and more

There are lots of SQL Server global variables (prefixed with @@), but I thought I'd list a few that I have found particularly useful in the past.  I often find I need to make use of them to gather data when I first go on client sites:

@@ROWCOUNT

Stores the number of rows affected by the last command.  I find myself using this variable all the time for logging.  For example, if I have a stored procedure inserting/updating/deleting data from a table, I use this variable to store the results of each of those commands in a log table.  This makes debugging much easier and any spikes or drops in records can be captured and easily viewed.  A word of warning though - this variable only stores the rowcount for the last command.  If you find that you are not storing the correct number, it is likely you have another line of code executing before you are reading this variable.

@@ERROR

Stores the error code for the immediately previous command.  I use this most often in conjunction with the @@ROWCOUNT variable, and store them together for logging/debugging purposes.  Note that if you want to log both of them for the same line of code, you need to make sure to write them both to the output in a single command. If you don't do this, then whichever variable's value is written second, will contain the result from the writing of the first variable, and not the actual insert/update/delete that is of concern.

@@SPID

Stores the session ID.  Again, my advice would be to log it with the previous two values.  If you have a number of sessions performing a range of activities, the log table will be confusing without being able to identify which sessions are doing what.

@@TRANCOUNT

Stores the number of currently open transactions.  I often perform a check using this variable in my CATCH block to determine if we have hit an error inside a transaction and left it open : IF (@@TRANCOUNT > 0) ROLLBACK

@@CPU_BUSY, @@IDLE, @@IO_BUSY

These ones are useful for gathering stats on the SQL box.  They store the number of "ticks" that the CPU has been busy doing SQL Server activities, the amount of ticks SQL Server has been idle and the number of ticks SQL Server has spent doing IO.  All are measured since the last time SQL Server was started.

@@TIMETICKS

The number of microseconds per tick - this helps to transform the previous stats into understandable time :)

There are many other global variables available in SQL Server, but this is simply meant to be a list of the ones I find myself using most often.

SSAS 2008R2 - Scope and Assignments - "A set has been encountered that cannot contain calculated members."

In my previous post I discussed using the SCOPE statement in a cube.   I use it quite extensively in my designs and last week I came accross an unexpected issue with it.

I have a cube containing many measures for which I require different functionality under certain conditions.  Those conditions can actually be described in subcubes, and therefore the SCOPE statement addresses this requirement.

The functionality I want is the same for several measures and therefore I put them together in the same SCOPE block:

 SCOPE([Measures].[Area1],[Measures].[Area2]); THIS=(0); END SCOPE;

Where Area1 and Area2 are both measures defined in the cube.  The above scope would set them both to zero, but this is just for illustration.

The issue arises if the 2 measures above, Area1 and Area2, are actually calculated measures. If that is the case then when we deploy this MDX we get the following error:

 "A set has been encountered that cannot contain calculated members."

This can be confusing as the error message does not actually identify the SCOPE as the source of the issue.  But you can be fairly sure that that is where the problem is.

For some reason using more than one calculated measure in a single scope statment is a problem for SSAS.  If you have very few relevant measures then you could split them into individual scope statments, but this would be laborious if you have several. The solution is to use an assignment script instead. This is as simple as:

({[Measures].[Area1],[Measures].[Area2]}) = 0;

Type this in the calculation script and, hey presto, the error disappears and the MDX works as expected.

Chris Webb also covers this here.