A few years back (wow time flies) I had you Work with Security. It’s been a while so if you aren’t completely comfortable with how SQL Server security works you might take a look and run through that particular homework. This month it’s going to be related.

This month I want you to put some thought into a security standards document. Who is allowed to do what work in which environment (dev, test, prod for example)? What type of connections are allowed, which aren’t?

Note: This is an open book assignment. Feel free to use the web to find other examples of this, if you have a security standards document at work feel free to use it is a reference. That said, no copying. I want this to be an original document.

Things to think about:

• Can a developer have sysadmin in an development environment? DBO? What permissions need to be specifically excluded? How about your QA team? What permissions do they need to do their job? What environments are they going to connect to to do this work? What other groups do you have in your company and what permissions do they need to get their job done?
• SQL Server Ids vs Active Directory?
• Naming standards for service accounts? Do you care? You could follow the standards the AD team is using?
• Do passwords (for SQL Server Ids) expire? Again, you could follow the standards of the AD team?
• Speaking of passwords, who is responsible for them? You? The owner of the Id? How do you document who owns the Id?
• Is there an exception process? I’ve actually seen a product that did, in fact, require sysadmin for their product to work. And it was Microsoft’s fault. Most of the time you don’t want to give a service account sysadmin so if that’s the case what type of documentation is required to get around the standards, if any?
• How about approvals and documentation for regular permissions requests?
• What else can you think of?