Script to make sure a login is only a member of the 'public' role and cannot drop tables?

Question

I want to have a T-SQL script that I can rerun from time to time to ensure that a given server login is a member of only the public role and cannot drop tables. If someone on my team were to accidentally give this login the sysadmin privilege, I want to have a script running on a SQL job at some frequency to "reset" the role back to public membership only to mitigate the potential damage done by the change.

With regard to preventing tables from being dropped, from what I've read it seems as though it is easy as making sure that the login is part of the public role and no other role. Is this true? If so, then I just need the script to revoke membership to everything except for public.

This is what I've tried so far:

ALTER SERVER ROLE public ADD MEMBER HandheldServiceAcct;  
GO 

but I get two errors:

Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'public'

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'MEMBER'

Also, I don't know how to revoke memberships to all other roles.

Answer 1

public is a T-SQL reserved keyword so it needs to be enclosed in double-quotes or square brackets to be used as an identifier. However, there is no need to check public membership because all logins are implicitly members of public server role and members cannot be added/dropped explicitly.

For a given login, you can simply remove sysadmin server role membership if it's not needed.

The ability to drop tables is a database level permission so you will need to iterate over all databases the login can access and check each one for unwanted permissions or role memberships granted to the user. Not only will built-in database roles like db_owner and db_ddladmin allow a user to drop tables in a given database, explicit GRANT statements (e.g. GRANT CREATE TABLE TO SomeUser;) can also confer permissions. Simply checking role memberships is not enough to absolutely guarantee a user cannot drop a table but might be good enough for your use case. Otherwise, you will need to use impersonation (EXECUTE AS) and HAS_PERMS_BY_NAME or, alternatively, query sys.database_permissions.