How to reset the 'role settings' in SQL Server 2008?

Question

After customizing the role settings (changed the 'public' role, just tick everything to Deny, then I set it back), all of my users cannot login, except to give them the sysadmin permission. So, I want to reset the role settings, is there any way to do that?

Answer 1

At the database level, you'd drop and recreate the user

At the server level: un-deny everyone or drop/recreate logins

You can also use something like to create another query to fix everything

SELECT
    'REVOKE DENY ' + name + 'Stuff'
FROM
    sys.server_principals
...

However, I've never managed to lock all my users out like this so you'll have to play with this query to get it to do what you want.