Sql-server – Cannot drop the user because it does not exist or you do not have permission

permissionssql server

I am getting this message for some users when executing sp_revokedbaccess procedure for database users. Users I am trying to remove are selected from sys.database_principals with type = S, W or G (sql server or windows users) and are not system users (principal_id > 4).

I checked if the user I can't remove owns any schema and it is not the case. The user is not an orphan user also. I am comparing a user that was removed and a user that gave me this error and can't find any differences. I do see the user in the database security folder so it does exist and has a corresponding server login (no sid mismatch).

Also, I am a sysadmin on a server level and should have permissions. I have been stuck with this for the last couple of days and can't solve it. I am not a DBA, just a developer and maybe there is something else I am missing. Please help.

Here is a screenshot of sys.database_principals. No problem with the first user (principal id = 7) but getting "Cannot drop the user…." with the second one (principal_id = 8):

enter image description here

Best Answer

I don’t have an answer, just a handful of suggestions, each possibly less useful than the last.

DB created on server A, moved.copied to server B, and database user does not “synch up” with instance Login. Happens with SQL Authentication. I reall don’t think this is it, but it’s worth a mention.

Poorly chosen name (user “Table”), or oddball characters (“My Login”)? Also extremely unlikely.

Try running this, then run the generated script? Rival DBAs may have created a login with a trailing space?

SELECT 'DROP USER [' + name + ']'
 from sys.database_principals
 where principal_id = XX  --  Target Id goes here

Similarly, go through SSMS, Object Explorer, drill down to Security, Users, right click the problem entry, select “Script User as”, “DROP to”, send to a new window, try that script?

Check if you do have sysAdmin rights? The following should return 1:

PRINT is_srvrolemember('sysadmin')

Might be tied in with something subtle and stealthy like replication, or its the database owner, or something equally obtuse and obscure

Run DBCC CHECKDB, see if any internals are out of whack?

The irritating thing is this is vaguely familiar, but I can’t remember where I came across it before. Please post if/when you figure it out!

If all else fails, nuke it from orbit (drop and recreate database from scratch). It’s the only way to be sure.

Related Question