We use a dozen or so standard database roles - often added to the model database to save recreating in each new database, examples are:

LOG_WRITER

USER_READ

USER_WRITE

ADMIN_READ

ADMIN_WRITE

USER_EXECUTE

ADMIN_EXECUTE

NO_ACCESS

AS these roles are standard and appear in all databases (dev, test & live) - All stored procedure and table scripts in SourceSafe inlude the granting of relevant permissions to all standard roles.

So if I'm creating a sproc called GetRecentTrades I will grant execute permissions to USER_EXECUTE and possibly ADMIN_EXECUTE. I don't care which users will be given permission to use that sproc later - that can be handled in production (ultimately). This means that I can manage permissions at the initial coding stage rather than in implementation to each successive environment - avoiding the risk that permissions for some action might be missed in live.

The NO_ACCESS role is standard and all permissions are always denied to every object (sprocs, tables, views etc.) - with CASCADE. This makes decommissioning selected user accounts easier. I'm sure many of you have experienced the problem where a user account may have more than one use - but not necessarily documented. I can add this user to NO_ACCESS in one or more databases and if something unexpectedly breaks it's relatively simple to take the user out of NO_ACCESS in the relevant database(s) - especially when compared to how you'd recover after deleting that user.

It's always interesting to know what fellow professionals think of how one does things so please feel free to comment.