I recently had a client call me out of the blue because he happened to be getting an error while trying to add a user to a database role. The error he was getting was “Cannot use the special principal ‘dbo’.”
This error has probably cropped up on me more than a few times. And on more than a few occasions, I have forgotten about the previous experiences. Some of that is because the fix is rather easy and after a few times seeing it, muscle memory takes over and you just fix it without thinking about it too much.
Until you get to that muscle memory moment though, you may flounder a bit trying this and that and failing then proceeding on to a level of frustration that has you losing precious hair.
As luck would have it, this is an article about security and principals and is similar in nature to some other articles I wrote about some fundamental misconceptions about permissions here and here.
I do hope that there is something you will be able to learn from this basics article. If you are curious, there are more basics articles on my blog – here.
Meet Prince Apole and Rolle…
Adding a user to the db_datareader database fixed role is a pretty simple task. Most of us can likely do that in our sleep. Even using the GUI is usually pretty reliable to do that. Every now again though, somebody has decided to get tricky on us. Maybe a mistake was made somewhere in a setting on the server and nobody has caught it because nothing was “broken” – until it was.
In the aforementioned case, I was asked to help resolve the issue and I found that there was a problem in how the database owner was set. Not only was it a problem in the current database but in 12 other databases on the same server. The systems admin was at wits end. He was dealing with something that was just not in his knowledge-base yet. I remember being in the same boat – so no big deal there. We talked about some of the things he had tried and how none of it was working. I am going to recreate the same basic scenario along with some of the attempted fixes in this article.
First, we need to create a database (best to break a database designed to be broken instead of an existing one used for something else already).
CREATE DATABASE [DummyDB] GO USE [master] GO ALTER DATABASE [DummyDB] SET RECOVERY SIMPLE WITH NO_WAIT GO USE [master] GO CREATE LOGIN [mydomainsvc_dummy] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO
That is pretty straight forward – the database will be created with the data files in the default directories on your SQL Server instance. In addition, a login called mydomainsvc_dummy will be created as a windows login.
Now let’s try to set the owner of the database and then add the svc_dummy account to the datareader role.
USE [DummyDB] GO /*set db owner to the dummy account */ALTER AUTHORIZATION ON DATABASE::[DummyDB] TO [saeopssql01svc_dummy] GO USE [DummyDB] GO ALTER ROLE [db_datareader] ADD MEMBER [dbo] GO
There is a point of interest here. I said I was going to add svc_dummy to the datareader role – not dbo. Well, I set the database owner in the preceding step to svc_dummy so it basically became dbo. When I try to perform the role addition in the GUI and then script the change, this is the script that is produced. I will show why in a few moments.
The execution of the second part of the script results in the following:
Msg 15405, Level 16, State 1, Line 18
Cannot use the special principal ‘dbo’.
That is obviously not going to work. Let’s try fixing the script and add the svc_dummy principal instead of dbo.
USE [DummyDB] GO ALTER ROLE [db_datareader] ADD MEMBER [mydomainsvc_dummy] GO
Unfortunately, this results in the following:
Msg 15151, Level 16, State 1, Line 22
Cannot add the principal ‘mydomainsvc_dummy’, because it does not exist or you do not have permission.
Well, maybe the problem is because the user doesn’t exist then? Let’s try to create the user and see what happens.
USE [DummyDB] GO CREATE USER [mydomainsvc_dummy] FOR LOGIN [mydomainsvc_dummy] GO
Now we should see this message:
Msg 15063, Level 16, State 1, Line 32
The login already has an account under a different user name.
Oy vey. We seem to be going in circles. Nothing is working. The user is there but not really there. Let’s try to drop the user and just try to clean things up and start over.
USE [DummyDB] GO /* GUI Generated - the user dbo is mapped to the dummy login */DROP USER [dbo] GO
I hope you see the problem with this one. Trying to drop dbo. I dunno but we should see an error here – and we do get an error.
Msg 15150, Level 16, State 1, Line 27
Cannot drop the user ‘dbo’.
Let’s fix the user then and try to drop the svc_dummy user instead of dbo.
USE [DummyDB] GO /* let's fix it to the correct user */DROP USER [mydomainsvc_dummy] GO
Which in turn generates yet another error.
Msg 15151, Level 16, State 1, Line 52
Cannot drop the user ‘mydomainsvc_dummy’, because it does not exist or you do not have permission.
If I can’t resolve the problem by changing the user in the database, maybe I can just blow it out of the water by dropping the server login.
USE [master] GO DROP LOGIN [mydomainsvc_dummy] GO
Yet another failure message will ensue. This time the message is:
Msg 15174, Level 16, State 1, Line 55
Login ‘mydomainsvc_dummy’ owns one or more database(s). Change the owner of the database(s) before dropping the login.
So far we have been able to skirt around the problem and generate six different error messages. The last one kind of gives us the best information on what we could do to resolve the issue. The login owns a database and therefore, we need to undo that ownership. Before we do that, let’s take a look at the database principal ‘dbo’.
We already know that svc_dummy is mapped to a user in the DummyDB database. We also know that we cannot add the svc_dummy user because of that prior mapping. We have also learned that when scripting the permissions change from the gui on the svc_dummy login and then generate the script it scripts out the user ‘dbo’. Due to this, let’s look in the sys.database_principals view at the dbo user and see what it tells us.
select dp.name, dp.type_desc, dp.owning_principal_id, dp.authentication_type_desc From sys.database_principals dp where name = 'dbo';
See how the dbo database principal says it is mapped to a windows account type? With this in mind, let’s join to the sys.server_principals and see what windows account is mapped to the dbo database user.
select dp.name as DBUser, sp.name as ServerLogin, dp.type_desc, dp.owning_principal_id, dp.authentication_type_desc From sys.database_principals dp inner join sys.server_principals sp on dp.sid = sp.sid where dp.name = 'dbo';
Now we see a bit more clearly. Combined with the error messages and the principal information for both the login and the user, we have a better view of the puzzle now. Changing the database owner indeed mapped the windows account to dbo for us and is now restricting us to certain activities when trying to manage permissions for the windows login in the database. From here, we can easily fix the issue by changing the database owner, creating a user mapped to the windows login and then adding that principal to the datareader role.
--fix USE [DummyDB] GO /*set db owner to the sa account */ALTER AUTHORIZATION ON DATABASE::[DummyDB] TO [sa] GO USE [DummyDB] GO CREATE USER [mydomainsvc_dummy] FOR LOGIN [mydomainsvc_dummy] GO USE [DummyDB] GO ALTER ROLE [db_datareader] ADD MEMBER [mydomainsvc_dummy] GO
And if we run that script for svc_dummy we will see a successful execution as shown here.
The Wrap
In this article I took a rather long route to a simple fix. It’s easy to try each of the steps I showed in this article thinking it will help. It isn’t illogical to try some of those steps. They just don’t work unfortunately. In the end, getting to know the settings in the database and what the errors are really trying to get at is most helpful. Sometimes, it just takes a few more steps to get to the real meaning of the error.
This has been another post in the back to basics series. Other topics in the series include (but are not limited to): Backups, backup history and user logins.
