Denying Reality

So I moved a database from an instance to another server.  Everything seemed to go right, until the next morning, when the developer who manages that system couldn't create a table.

He was in the db_owner role.  The db_onwer role can do pretty much anything they want to a database.  Except he couldn't.

I manually granted him "create table" permissions, and it still didn't work.

I used

 select IS_ROLEMEMBER('db_owner','UserName');

and I verified that he was in the db_owner role.  I then ran

 execute as user ='UserName' SELECT * FROM fn_my_permissions (NULL, 'DATABASE') order by permission_name ;  

That listed every permission he had, and Create Table was not one of them

SELECT p.[name] [User], s.state_desc, s.permission_name 

FROM [sys].[database_permissions] s 

  JOIN [sys].[database_principals] p 

    ON s.grantee_principal_id = p.principal_id AND

   s.class = 0 

ORDER BY [User], s.state_desc ,s.permission_name ;

I found that someone (probably me around 13 years ago) had sent explicit deny permissions on the public role.

A deny is saying "I don't ever want anyone in this role/group to ever access this."  He never noticed it before on the instance because he was a sysAdmin..

The solution was to revoke the permissions.  A Revoke is a removal of a permission record.  The permission record can be a grant (allows access) or a deny (prevents access).  If no permission record exists, access is denied.

to revoke, you use the form

revoke CREATE TABLE to public