Security

I know there are fixed database roles that come with SQL Server. How do I best use them within my installations? In this tip we will cover each of the database roles and recommendations on when to and when not to use them.

I know there are fixed server roles that come with SQL Server. How do I best use them within my installations and what should I watch out for? In this tip we cover each of the fixed server roles as well as give you pointers on what to use and when to use

I am required to restrict the access to some users to view the definition of specific SQL Server database objects. I am aware of the WITH ENCRYPTION clause that is used to encrypt the code for objects like views, stored procedures and functions. It sort o

I have tried to use ownership chaining like in a previous tip, but it doesn't seem to be working. In my case, I'm building a SQL string (dynamic SQL) to execute within the stored procedure and then executing using either EXEC or EXEC sp_executesql. Howeve

I have several objects, all in the same schema. Because of this, ownership chaining is working, as described in a previous tip. However, I do not want ownership chaining to be on, but I need the objects to remain in the same schema. How can I do this?

In a previous tip, Granting limited permissions to create views in another schema in SQL Server, I showed how to use the CREATE VIEW permission and the ALTER SCHEMA permission to allow users to create new views using the same schema as the tables. Howeve

Learn how to grant permissions to SQL Server database uses to allow them to create views.

I have a grasp on ownership chaining, but I'm wondering what cross-database ownership chaining is and how it works? How is the owner determined across databases if ownership is based on database users?

I have heard of something called ownership chaining within SQL Server, but I don't know what that is or how it works. I'd like to know if it's a security risk or a security feature and if it's a feature, how do I use it. If it's a risk, I'd like to know h

I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the database level. What are they and what do they give rights to perform?

I am new to SQL Server 2005/2008 having administered SQL Server 2000 and below. I have heard there are a lot of new permissions at the server level. What are they and what do they give rights to perform?

The February 2009 Microsoft Security Bulletin included an updated Executive Summary (MS09-004) referring to a highlighted vulnerability with Microsoft SQL Server allowing an authenticated user to escalate privileges to those of SYSTEM, thereby taking cont

SQL Server has a lot of little features that are nice to use, but sometimes these things come back to get you. One such issue is the use of aliased users. This tip shows you how to find security holes when aliased users are setup in your databases and a

In a previous tip on Switching Execution Context using the REVERT clause, you have seen how to switch execution context to a more privileged user and revert back to the original context of the caller within a programmable object. The REVERT clause accepts

In SQL Server 2000 and below, I relied on sp_helprotect, sp_helprolemember, and sp_helpsrvrolemember to be able to report on all the permissions a given login or user had. However, I'm now supporting SQL Server 2005/2008 and I have read how sp_helprotect

After attaching or restoring a SQL Server database, orphaned users may emerge. Read this tip for help fixing them.

On a recent trip out west we were informed via radio that there were a series of earthquakes occurring off the coast of Vancouver Island. We happened to be sailing directly over the location of these earthquakes and never noticed anything, although the s

Gaining insight into my SQL Server standard and Windows logins has historically been a challenge in terms of determining password changes, failed login attempts, etc. I have noticed that you have experienced the same issue with your tip entitled 'When wa

I have a SQL Server 2005 instance with Mixed Security Mode, BUILTIN Administrators rights revoked and I have not set up a Dedicated Administrator Connection (DAC) in the SQL Server 2005 Surface Area Configuration tool. I have tasked my Junior DBA with set

At times I run into application issues when I am unable to perform particular functions in the application that some of my peers are able to complete. I need to be able to quickly and easily understand my permissions in SQL Server. Do you know of an eas

Learn how to determine service related privileges for the SQL Server service account.

As a DBA you have the responsibility to ensure the security for the application and users related to the SQL Servers that you manage. But what are the security principals that you follow for the administrators? Are there any hard and fast rules that mus

Within my SQL Server T-SQL stored procedures and scripts I need to find out the files in a specific directory. What are some approaches to do so? I need some flexibility to capture the file names and general information for subsequent processing. What

Every time I try to execute an executable (*.exe) file that I copied from a Windows 2003 File Server to my SQL Server running Windows 2003, I am unable to access the file. It works without a problem on the original Windows 2003 File Server and I am able