Johnny Ed (2/18/2014)
We have several environments that have distinct AD login principles for each environment (e.g. COMPANY\user_basic_dev or COMPANY\user_basic_prod).
I order to simplify post release database comparisons between environments and refreshing development environments, I am proposing the following;
1. Move all explicit object permissions to role(s)
2. Generalise database users names so they are consistent between environments (e.g. user_basic) and then link this database user to the login principles (i.e. in the dev enviornment, the login COMPANY\user_basic_dev links to database user "user_basic".) Database users do not have associated schemas.
I have read around regarding this second point, looking for best practices but cannot find anything to suggest this is poor form. Historically, I have always assocated the login with a database user of the same name.
Has anyone got any experience to advise against this?
Ta in advance.
Your #1 is a widely accepted best practice.
Your #2 is wonderful -- I love it! We do substantially that at my place of work, with domain group users like:
DOMAIN\dbname_RWX (db_datareader, db_datawriter, db_executor [a user defined role with SP execute permissions and RO])
This makes it extremely easy to know what domain users have what permissions. We even have a query to create dsadd commands to create the domain groups we can pass on to the network admins and also to set up the user defined role and the GRANTS.
When a domain login needs permissions to a database, the security requests merely join the login to the AD group.
1. You eliminate the need to set up specific user accounts as database logins, and even better, the need to clean up when a AD login is dropped for things like employee terminations.
2. Auditing becomes much easier.