db_owner role missing Create/Alter permissions

Question

I've never seen this before and after an extensive search through a lot of sites, I've yet to figure out what is happening and why.

I restored a recent production backup to one of our QA servers a few days ago.  In the process of the restore, I run some scripts to delete some data and the compact the database for the Development team. Outside of that, I do not touch anything with the DB.

When I look at my user permissions for my non-SA account on the production database, I have the normal create/alter/etc. permissions that someone granted the db_owner role should have.

When I assign my user the same role on the QA database that I restored, the CREATE/ALTER permissions that are normally assigned to the db_owner role user are gone and this is all I have:

BACKUP DATABASE
BACKUP LOG
CONNECT
CONNECT REPLICATION
CHECKPOINT
SUBSCRIBE QUERY NOTIFICATIONS
AUTHENTICATE
SHOWPLAN
SELECT
INSERT
UPDATE
DELETE
REFERENCES
EXECUTE
VIEW DATABASE STATE
VIEW DEFINITION
TAKE OWNERSHIP
CONTROL

I've looked to see if there were any denies for Public or any Groups that might be added to this database, but there are none.

I've removed and re-added the user.  That did not work.

I've re-restored the DB.  That has not worked either.

Any thoughts or ideas as to what might be causing these permissions to disappear?

Answer 1

Ended up figuring this out on my own.

Ran the following SQL statement with a test account in it to figure out what would cause it:

SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
  JOIN [sys].[database_principals] prin
    ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
and prin.name = 'domain\userid'
ORDER BY [User], [Permission]; 

Found that a non-essential orphaned group had DENY CREATE/DENY ALTER permissions.  Once removed, these fixed the problem.