SQL 2000 to SQL 2005 migration: temporary tables reuse issue.

During the same project the next issue was found: temporary table reuse. Here is an example on “how to reproduce”: USE tempdb GO   CREATE PROCEDURE dbo.usp_step_one AS BEGIN                 CREATE TABLE #temp (ID INT NOT NULL, NUM INT)                 ALTER TABLE #temp ADD CONSTRAINT PK_ID PRIMARY KEY (ID)                 INSERT INTO #temp SELECT 1,…

5

SQL 2000 to 2005 migration: execution plan change issue.

Last week I was engaged on SQL 2000 to SQL 2005 migration project, and we had identified some issues related to significant changes in execution plans.  Before migration we tested the application with using Upgrade Advisor (test passed without any caution), but hopefully we also had made an additional “trace replace” test. During this stage…

12

Switching context with using EXECUTE AS … not always switch it in fact as you wish.

In this demo i will try to explain why SET TRUSTWORTHY ON on some databases may make the sysadmin job unsafe. In previous posts i explained how db owner (or any developer) can try to implemet simplest luring attack against server sysadmin. According to BOL to avoid this problem sysadmin should switch to the context of the…

1

Workaround for “Unable to set Default Schema for a group”.

Let assume you are using Windows Authentication with SQL Server 2005,  you’ve added a new server account for domain group and would like to give it a default schema.  The properties window is the same for users and groups but the default schema field is enabled only for user entities.  As a result you cannot add a default…

1

SQL Server 2005: building security model based on DDL triggers.

Last month I’m working with a client to create something like non-standard security model. He asked for the following features:   –          server–level management only for sa (server admin). –          database–level management for dbo (one or more dbo per database). Dbo is responsible for database’s user management, update / refresh structure etc.; but dbo should…

2

db_securityadmin is very powerfull and … dangerous.

Few days ago I worked for one client. He uses the following business model: –          dbo usually responsible for high level database design and maintenance; –          all database users organized in additional security groups for security purposes; –          dedicated person is responsible for user’s security maintenance, (s)he is able to assign user to explicit (one…

5