Things your company doesn’t want to do with SQL Server
I used to work for a government agency, and not just a regular one, but a military one. So, as you can imagine security is a big issue. The same goes for financial and medical databases. For some people downtime is not an option, for others the integrity and liability of the data is the most important.
SQL Server helps you on this. It’s followed the path Microsoft took a while ago to make their products ‘secure by default’. This means the Windows Firewall is now enabled in the latest server releases, instead of disabled or absent in earlier releases. The same goes for SQL Server. No more Builtin Administrators in the SYSADMIN serverrole. And xp_cmdshell is disabled by default. As is the guest user in user databases.
I’ve seen a lot of developers turn pale white when their glorious application didn’t work in production, simply because it needed SYSADMIN. Well, it didn’t need SYSADMIN but they forgot to specify what permissions were needed and did not provide a databaserole with the proper permissions. If you do not have a strong organization it’s most likely you will give the application account SYSADMIN permissions. You might believe that some day you will change this to a databaserole but that day will never come.
Sometimes, and only sometimes and in small companies, they place the SQL service account in the Domain Admin group (yes, people who do this exist on this planet). And to be honest, if you are a DBA,this is the best thing that can happen to you. Because now you enable xp_cmdshell and with a simple ‘xp_cmdshell ‘dsadd user cn=backdoor,cn=users,dc=contoso,dc=com -samid adm‘’ you can add users to AD. But of course you can grant yourself Domain Admin permissions. And you don’t have to impersonate the account because xp_cmdshell always runs in the context of the SQL service account.
And if there are people who believe the SQL account has to be Domain Admin to be able to register a SPN at startup; Read this: http://msmvps.com/blogs/ad/archive/2010/07/15/how-to-delegate-the-right-to-delegate-kerberos-constrained-delegation.aspx
And any domain administrators who goes along in this and grant Domain Admin permissions to a service account should URALT (Update Resume And Leave Town).