I'm trying to put triggers on an existing database, but I am having a hard time understanding how to use the inserted and deleted tables. They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update? And do they contain a s
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
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
We ran into a case recently where we had the logins scripted out on my SQL Server instances, but we didn't have the fixed server roles. As a result, our recovery efforts were only partially successful. We ended up trying to figure out what the server role
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
I have been tasked with auditing security on my SQL Server. I'm trying to audit permissions within the database itself. This needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditor
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.
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
Audtiting SQL Server logins is important, because it reveals who can connect to the SQL Server and what permissions they have at the server level. However, the data is at the database level and your auditing should include determining what logins can acce
We have a fairly active development SQL Server with databases getting created or restored quite frequently. There are certain settings we wish to enforce, but none of us want to walk through each database by hand to determine if the databases are meeting
When I do a SQLCMD -L or OSQL -L or I hit the drop down on the server list to connect using the GUI tools (like SQL Server Management Studio), I only see some of the SQL Servers in my environment. I don't see them all. Why is that?
In this tip learn how to query SQL Server to audit permissions granted for SQL Server roles.
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 have been tasked with auditing security on my SQL Server. However, this needs to be a somewhat automated process as I don't want to have to rely on taking screenshots every month to satisfy our auditors. In this tip we cover the tables and/or views that
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?
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
In this article we cover how to use password policies and expiration dates for SQL Server logins.
I want to use user-definable parameters to provide filtering in my reports for SSRS, but I'm not sure how to proceed. In this tip we walk through the steps to implement basic report filtering in SSRS.