Security

Recently, we were trying to setup a web application which uses Windows authentication. We want to pass the user's credentials through to the SQL Server because the database security is dependent on who the user is. However, we weren't able to make the con

In many organizations there is a need to segment what DBAs can do in certain environments. By default most DBAs are made sysadmins, but this gives them full control over the SQL Sever instance. To meet certain regulatory guidelines this is not allowed, b

We ran into a case recently where we had the logins and users scripted out on my SQL Server instances, but we didn't have the fixed database roles for a critical database. As a result, our recovery efforts were only partially successful. We ended up tryin

What are the different ways to secure the SA login? Everyone is aware of the SA login and its administrator rights, so it is very risky if some unwanted users try to use the SA account or hack the SA account. It is also not advisable to use the SA account

Many times developers want to put logic into their code or SSIS/DTS package to check the SQL Server authentication mode. In this tip we take a look at a couple of ways that this can be done.

I've been tasked to put together a row-level security model for a database where users are connecting with their Windows user accounts. I'm not sure how to go about this. In this tip we walk through the steps on how to put this together with security mapp

I am trying to understand how SQL Server communicates on the network, because I'm having to tell my networking team what ports to open up on the firewall for an edge web server to communicate back to the SQL Server on the inside. What do I need to know?

I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. Read this tip about Implicit Permissions Due to Ownership Chaining or Scopes in SQL Server for the solut

I have audited for permissions on my databases because users seem to be accessing the tables, but I don't see permissions which give them such rights. I've gone through every Windows group that has access to my SQL Server and into the database, but with n

I'm needing to audit the permissions in my databases, but I want to script them out so I have something to run in case of a recovery situation. I've got the logins, roles, and users handled, but it's the permissions that I want to extract. In this tip we

Check out this tip to learn how to configure a proxy account to allow non-sysadmin users to be able to use xp_cmdshell.

Every once in a while you may run into an issue where a user cannot login to SQL Server, because the logins default database may not be available. This could be for several reason such as that database was dropped, the database may be corrupt, the databa

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately so that not everyone can select the data? In a previous tip, Filtering Columns in SQL Server Using Views we looked at using Views. In

I have a table where some of the columns should not be queryable by all users. How can I filter the data appropriately, so that not everyone can select the data? In this tip we show how this can be done using views.

The company I work for is looking for additional methods to employ so that our applications and data are secure. A new method ships with SQL Server 2008 R2 called Extended Protection which is covered in this tip.

I am attempting to nest roles in creating my database security model, but I'm getting an error when I use sp_addrolemember. The error is this: Msg 15413, Level 11, State 1, Procedure sp_addrolemember, Line 92 Cannot make a role a member of itself. I'm not

I have been tasked with auditing security on my SQL Server. I understand that logins allow you to connect to SQL Server, but I'm not quite understanding how to determine whether a login has access to a database or not. For instance, I know that all logins

I need to run something from the command-line, but based on best practices xp_cmdshell has been disabled. The task that needs to run is an internal process that will originate from within SQL Server. Is there a way to do this without using xp_cmdshell?

Ideally your SQL instance would be configured to only allow for Windows Authentication. There may be times when mixed mode authentication is necessary at which point you will should configure a method to rotate the ‘sa’ password on a regular basis. You wa

We have all faced this issue: a cursory review of a SQL Server instance we manage appears to have logins assigned to multiple server roles whose rights supercede or overlap one-another. I am specifically referring to the situation where a login is a memb

I know that in SQL Server 2000 and below, you could assign permissions against objects like tables, views, and stored procedures. I'm hearing in SQL Server 2005 and 2008 there's a new security model called securables which allow for nestable permissions.

I know in SQL Server you can nest user-defined database roles within the database, but is that a good idea? Do they work the same as Windows groups when they nest? What about how they interact with the SQL Server provided fixed-database roles? In this ti

Learn how to configure the Windows Firewall to make sure SQL Server can work correctly and the necessary ports are configured correctly.

In this article we cover how to use password policies and expiration dates for SQL Server logins.