SQL Server – Remove db_denydatawriter and db_denydatareader Role from Admin User

If I had to guess you have them added to the db_owner role as well. Either that or sysadmin (although less likely). Administrative roles, specifically DBO and SYSADMIN can not be blocked from access to anything they have control over (dbo - database level, sysadmin - instance level). I would go back and check first the role that you created and make sure that it is not a member of any other roles that might cause an issue. db_datareader for instance shouldn't be a big deal. Second I would check public and make sure IT isn't a member of any other roles. Then I would check any other roles that the users might be a member of.

All that being said it's also possible you made a mistake when you set up your role and revoked instead of denyed (I've done that type of thing before).

Last but not least, you should change how you are doing this anyway. Remove all access from public. Create a role that has just the access you want them to have and add them to that role. It is generally considered a bad idea to grant access to public. Or for that matter to grant lots of access and then deny it to certain people. It's far better to accidently forget to grant someone access to something, than to forget to deny it.

If I follow what you're saying correctly. You have db users in test, which are not in prod. When you restore from prod, all these users lose access. At the moment you have a static script which puts all the access back after the restore. You need to do this more dynamically. What I would do is:

1. Before restoring the prod backup to test, run a script which dumps user access, look at the code example below.
2. Perform your restore & other scripts as per normal.
3. Run the script output created in step 1. to put the access back for test.

Try to structure user access around roles if you haven't done so already. This will simply the process.

Here's a script I use:

print'use ' + DB_NAME()
print 'go'
declare @output nvarchar(max)
set @output = '' 
select @output= @output + 
'CREATE USER ' + QUOTENAME(mp.name) + ' FOR LOGIN ' + QUOTENAME(sp.name)
+CHAR(13)
--, is_a_member_of_role_name=rpn.name
from        sys.database_principals sp 
            inner join sys.database_principals mp on (mp.principal_id = sp.principal_id) AND mp.type <> 'R'
            left outer join sys.database_role_members rm on (rm.member_principal_id = mp.principal_id) 
            left outer join sys.database_principals rpn on (rm.role_principal_id = rpn.principal_id) AND rpn.type = 'R'
            inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
            where mp.name not IN ('sys','dbo','guest')
            group by mp.name, sp.name
print @output            
print 'go'            
set @output = '' 
select @output= @output + 
'EXEC sp_addrolemember ''' + rpn.name + ''',''' + mp.name + ''''
+CHAR(13)
--, is_a_member_of_role_name=rpn.name
from        sys.database_principals sp 
            inner join sys.database_principals mp on (mp.principal_id = sp.principal_id) AND mp.type <> 'R'
            left outer join sys.database_role_members rm on (rm.member_principal_id = mp.principal_id) 
            left outer join sys.database_principals rpn on (rm.role_principal_id = rpn.principal_id) AND rpn.type = 'R'
            inner join sys.database_principals rp on rm.role_principal_id = rp.principal_id
            where mp.name not IN ('sys','dbo','guest')
            group by mp.name, sp.name, rpn.name
            print @output
print 'go'