Security

This will be no surprise to those who have been working with SQL Server for a long time, but it can be puzzling at first and actually I was a bit confused myself when I stumbled upon this behavior for the first time. SQL Server treats windows users in a special way, a way that could lead us to some interesting observations. First of all, we need a test database and a couple of windows principals to perform our tests: [Read More]

I’m writing this post as a reminder for myself and possibly to help out the poor souls that may suffer the same fate as me. There’s a software out there called “Digital Guardian” which is a data loss protection tool. Your computer may be running this software without you knowing: your system administrators may have installed it in order to prevent users from performing operations that don’t comply to corporate policies and may lead to data loss incidents. [Read More]

SQL Server logins can implement the same password policies found in Active Directory to make sure that strong passwords are being used. Unfortunately, especially for servers upgraded from previous versions, the password policies are often disabled and some logins have very weak passwords. In particular, some logins could have the password set as equal to the login name, which would by one of the first things I would try to hack a server. [Read More]

Looks like a silly question, doesn’t it? - Well, you would be surprised to know it’s not. Obviously, you don’t need to be a sysadmin to simply issue a BACKUP statement. If you look up the BACKUP statement on BOL you’ll see in the “Security” section that BACKUP DATABASE and BACKUP LOG permissions default to members of the sysadmin fixed server role and the db_owner and db_backupoperator fixed database roles. But there's more to it than just permissions on the database itself: in order to complete successfully, the backup device must be accessible: [. [Read More]

Personally, I hate having a dedicated administrative account, different from the one I normally use to log on to my laptop, read my email, write code and perform all the tasks that do not involve administering a server. A dedicated account means another password to remember, renew periodically and reset whenever I insist typing it wrong (happens quite frequently). I hate it, but I know I cannot avoid having it. Each user should be granted just the bare minimum privileges he needs, without creating dangerous overlaps, which end up avoiding small annoyances at the price of huge security breaches. [Read More]