Friday, September 14, 2012 7:20 PM
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:
SUBSCRIBE QUERY NOTIFICATIONS
VIEW DATABASE STATE
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?
Friday, September 14, 2012 8:29 PM
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.
- Marked As Answer by TI-Inf-DBA Friday, September 14, 2012 8:30 PM