A Brief Note on Database Security

Where I’m working now we’re going through a pretty large upgrade, from SQL 2008R2 SP3 to SQL 2014 and as a direct result of that upgrade we’re revisiting the security model.  Developers always enjoyed carte blanche into the development database via a SQL login, which they used to make all types of havoc for our database development team to contend.  Moving forward, they will be put into AD groups and only allowed access via their AD login and then via the group.

In moving to a model around the AD group, the database server login (how everyone gets into the server itself) was created as the AD group, let’s say <%DomainX\Developers%>.  The “Developers” group is given access to each database and specific database roles within each.  DbDataWriter and DBDataReader for example, but not DDL Admin.  In order to allow them to see the DDL statements, the database user (which is the group) is given “View Definition” permissions.  In the end, the standard code developers can write T-SQL queries, write new data they need to test and even view the DDL.  As members of the group are hired and attrition is accounted, the AD managers are expiring and creating new, hopefully cloned, logins.  Management of logins becomes very easy,and nobody waits for database access.

Here’s the added bonus of using AD logins, in our current environment anyway.  The development databases have been spread over an availability group.  As such, syncing the SQL SID’s would be a DBA task for every person needing database access coming in or out.  Now using AD groups, a fail over for any reason is a non-event.  Access works all the time, every time,.. or at least when the AD Controllers are up 😉

Another distinction to make is that our shop has T-SQL developers and C# developers. The referenced “developers” above are the C# folks.  And it isn’t that they aren’t capable of getting it (T-SQL development) done but the structure provides a clean separation of duties.  In theory, that should speed development and have fewer bugs.

Keep in mind, if your shop uses Dynamics GP (aka Great Plains) the you’ll likely be managing SQL Logins.  I’d recommend shared storage and a traditional FCI instead of an availability group for HA.  Why?  Again, management is easier and the fail over is more seamless.

Keeping database access in mind when designing new systems that will run box software and managing those logins can be easier, even while providing HA.

Happy Databases!!

Data Chef out,..