Piotr Rodak : database design

Security – how to assign rights to database objects

Piotr Rodak — Sun, 10 Apr 2011 09:23:00 GMT

A few days ago my team mate asked me to check out why one of the logins doesn’t have right to execute to a few stored procedures. Allegedly it had the right before. After a few minutes of digging in the source control it turned out that these procedures were scripted with explicit EXECUTE rights to them. However, some later updates to the code did not contain the rights scripted. Developers rarely pay too much attention to the security design of the code they write. It is not common that the code is implemented with least possible access level in mind. A classic example of this is TRUNCATE TABLE. While this is very handy command and can be very useful in certain scenarios, developers don’t realize that the user account executing it has to have some elevated rights.

I have seen many databases which implement explicit rights to objects. This is to some degree a legacy issue, as in SQL Server 2000 and before security model was completely different and more difficult to manage. The common practice at the time was to script rights to objects, and to make things simple everyone who had right to connect to database could do everything in it. Even recently I saw a table with explicit rights like this:

 create table dbo.sometable
(
    column1 int,
    columnt2 char(1),
    column3 varchar(255)
)
go
 
grant select on dbo.sometable to [public]
grant delete on dbo.sometable to [public]
grant insert on dbo.sometable to [public]
grant update on dbo.sometable to [public]
 
go
 

This is definitely a bad practice.

Let’s examine slightly different scenario, the one that I mentioned at the beginning of this post. Let’s analyse what options you have when you need to give user rights to execute a stored procedure but do don’t want to give rights to public role:

Assign explicit rights for the user to the procedure.
Assign explicit rights to a role and assign user to that role
Create the procedure within a separate schema, assign user to specific role and assign rights for the schema to the role.

The above three options have their pros and cons. The first option is in most cases an aftermath of legacy design and while it is most granular, it is also hardest to manage and monitor.

Second option is also very granular, the only difference is after all that the rights are assigned to a database role. Main advantage of this solution is that you can assign more than one user to the database role and they will be automatically granted required access to the objects.

The third option requires some consideration regarding design of the database. You may need to change some code calling the procedures. You may still want to create a database role and assign rights for the schema to the role. This gives you best flexibility.

Let’s examine some of the effects each of these options brings in when implemented. I created script file that examines six scenarios you may come across in your database. This is definitely not a complete list, bear in mind.

First, let’s create test database and login that we will use to test the solutions. I will also create a sample table and stored procedure in the database.

 use master
go
create login RemoteLogin with password='Str()ngPwd', check_policy=off
go
create database testExecRights
go
use testExecRights
go
create user [RemoteUser] from login [RemoteLogin]
go
create table dbo.tTest(a int, b int)
go
create procedure dbo.pTestInsert(@a int, @b int)
as
begin
    insert dbo.tTest(a, b) values(@a, @b)
end
go
 

The first scenario shows what happens when you create an user in the database, but you don’t assign any specific rights for it.

 ---Scenario 1: user has public right to the database
execute as login='RemoteLogin'
select suser_name()
go
 
    begin try
        ---should fail
        exec dbo.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 1: Exception occured in ' + error_procedure() + 
            N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
 

This code throws an exception, as expected:

Msg 50000, Level 16, State 1, Line 10
 Scenario 1: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

Let’s have a look what we’ll get when we assign the right to execute the stored procedure explicitly:

 ---Scenario 2: user has explicit right to execute the procedure
---this is executed at sa/dbo level
grant execute on dbo.pTestInsert to [RemoteUser]
go
execute as login='RemoteLogin'
select suser_name()
go
    begin try
        ---should succeed
        exec dbo.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 2: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
go
revert
select suser_name()
--revoke granted right
revoke execute on dbo.pTestInsert to [RemoteUser]
go
 

In line 3 in the above snippet you see the command to grant execute right on the stored procedure to the RemoteUser. Please note that the user still has no other rights – he can’t insert rows to the tTest table directly, he even can’t select from the table. Since the above snippet is part of the bigger script, at the end of it, in line 22 you can see command 'REVOKE’ to – revoke the right granted in the line 3. So, how the above code works? Yes, it works OK. It doesn’t throw exception because the permissions are right.

As I mentioned before, there were procedures which the remote login mysteriously lost ability to call. In my case, the issue was caused by a release script, which did not re-establish appropriate rights to them. There are two ways you can modify a procedure during release. One is obviously ALTER PROCEDURE, which does not change the security configuration of the procedure, the other is a sequence of DROP and CREATE statements. Scenarios 3 and 4 are shown in the below scripts.

 ---Scenario 3: user has explicit right to execute the procedure but the stored procedure is ALTERED
--- in subsequent release
go
---this is executed at sa/dbo level
grant execute on dbo.pTestInsert to [RemoteUser]
go
go
---now ALTER the procedure
alter procedure dbo.pTestInsert (@a int, @b int)
as 
begin
    insert dbo.tTest(a, b) values (@a, @a * @b)
end
go
execute as login='RemoteLogin'
select suser_name()
go
    begin try
        ---should succeed
        exec dbo.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 3: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
go
revert
select suser_name()
 
go
--revoke granted right
revoke execute on dbo.pTestInsert to [RemoteUser]
go
 

 ---Scenario 4: user has explicit right to execute the procedure but the stored procedure is 
---DROPPED an CREATED in subsequent release
go
---this is executed at sa/dbo level
grant execute on dbo.pTestInsert to [RemoteUser]
go
go
---now DROP and CREATE the procedure
drop procedure dbo.pTestInsert 
go
create procedure dbo.pTestInsert (@a int, @b int)
as 
begin
    insert dbo.tTest(a, b) values (@a, @a * @b)
end
go
execute as login='RemoteLogin'
select suser_name()
go
    begin try
        ---should fail
        exec dbo.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 4: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
go
revert
select suser_name()
 

Scenario 4 throws an exception as expected:

Msg 50000, Level 16, State 1, Line 9
 Scenario 4: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

Obviously there is an issue here. The release process must take care of the proper assignment of the rights. The problem is if the script consists of smaller parts prepared by developers, it may be virtually impossible to verify if the rights are scripted correctly in the release script. One can script all explicit permissions to objects before the release and then reconcile them with the permissions existing after the release. This also can be tricky, as sometimes the rights have to change, sometimes new objects don’t have properly scripted permissions etc. What if you have may users that need to perform certain operations on tables and views? In general, it looks like the idea of assigning rights one by one for all users and objects is bad in most scenarios. Imagine the amount of failure points if you have 3000+ objects in database and you change, say, a few hundred of them within the release. What can you do about it?

First thing that you should do is to break explicit relationship of users and objects. You can do this by creating a database role which will serve as single point of security assignments. By having several roles in the database, you can tune the security fairly easily without having to rescript permissions for all users each time you need to make a change. Scenario 5 shows such database role example:

 ---Scenario 5: belongs to DATABASE ROLE with explicit right to execute the procedure 
---and the stored procedure is DROPPED an CREATED in subsequent release
go
---this is executed at sa/dbo level
create role [pTestExecRightRole]
go
grant execute on dbo.pTestInsert to [pTestExecRightRole]
go
exec sp_addrolemember 'pTestExecRightRole', 'RemoteUser'
go
---now DROP and CREATE the procedure
drop procedure dbo.pTestInsert 
go
create procedure dbo.pTestInsert (@a int, @b int)
as 
begin
    insert dbo.tTest(a, b) values (@a, @a * @b)
end
go
execute as login='RemoteLogin'
select suser_name()
go
    begin try
        ---should succeed
        exec dbo.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 5: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
go
revert
select suser_name()
go
exec sp_droprolemember 'pTestExecRightRole', 'RemoteUser'
go
drop role [pTestExecRightRole]
go
 

The issue with the above is that still, when an object is dropped and recreated, the rights assigned to it are lost. The script above throws the same exception again:

Msg 50000, Level 16, State 1, Line 9
 Scenario 5: Exception occured in pTestInsert: The EXECUTE permission was denied on the object 'pTestInsert', database 'testExecRights', schema 'dbo'. (229)

To solve this you have to group objects in a similar way you do it with users. While database role is used as a container for usSers, schema serves the same purpose for database objects. Scenario 6 shows this approach.

 ---Scenario 6: user belongs to DATABASE ROLE with explicit right to execute on the SCHEMA
---and the stored procedure is DROPPED an CREATED in subsequent release
go
---this is executed at sa/dbo level
create role [pTestExecRightRole]
go
create schema RemoteExec authorization dbo
go
grant execute on schema::RemoteExec to [pTestExecRightRole]
go
exec sp_addrolemember 'pTestExecRightRole', 'RemoteUser'
go
---now DROP and CREATE the procedure
drop procedure dbo.pTestInsert 
go
create procedure RemoteExec.pTestInsert (@a int, @b int)
as 
begin
    insert dbo.tTest(a, b) values (@a, @a * @b)
end
go
execute as login='RemoteLogin'
select suser_name()
go
    begin try
        ---should succeed
        exec RemoteExec.pTestInsert 1, 1
        return;
    end try
    begin catch
        declare @errormsg nvarchar(2000)
        set @errormsg = N'Scenario 6: Exception occured in ' + error_procedure() + N': ' + error_message() + N' (' + convert(nvarchar, error_number()) + N')'
        raiserror(@errormsg, 16, 1)
    end catch
go
revert
select suser_name()
 
go
 

As you see, I created schema RemoteExec in line 7. I granted explicit right to execute on all objects belonging to the schema to the database role pTestExecRightRole. Subsequently I simulate release process by dropping existing procedure dbo.pTestInsert and creating it in the schema RemoteExec. As you see, I don’t apply any explicit rights to the stored procedure. The fact that it belongs to the RemoteExec schema is sufficient for it to be callable by the user belonging to pTestExecRightRole. You can easily add and remove procedures from the RemoteExec schema and they will automatically inherit any rights you have chosen to assign to them, without any manual coding. More importantly, the rights are not tied to the objects, so you will not loose required functionality after making changes to the objects.

As I said before, this approach has its consequences. You may need to modify some of the code that calls the stored procedures to explicitly reference schema, in some cases you need to modify the code of the stored procedures. If you work with older code the most likely schema it references is an implicit dbo – object names are used without any schema part. This may be OK and resolve at runtime, but in some cases it won’t especially if you move the procedure to a different schema. It is a good practice to explicitly reference schemas in your code to avoid any confusion and problems.

You can download the test script with all scenarios and cleanup code from this location. Enjoy.

Technorati Tags: SQL Server 2008,T-SQL,programming,database design

BNF – how to read syntax?

Piotr Rodak — Tue, 07 Dec 2010 21:40:00 GMT

A few days ago I read post of Jen McCown (blog) about her idea of blogging about random articles from Books Online. I think this is a great idea, even if Jen says that it’s not exciting or sexy. I noticed that many of the questions that appear on forums and other media arise from pure fact that people asking questions didn’t bother to read and understand the manual – Books Online. Jen came up with a brilliant, concise acronym that describes very well the category of posts about Books Online – RTFM365. I take liberty of tagging this post with the same acronym.

I often come across questions of type – ‘Hey, i am trying to create a table, but I am getting an error’. The error often says that the syntax is invalid.

1 CREATE TABLE dbo.Employees
2     (guid uniqueidentifier CONSTRAINT DEFAULT Guid_Default NEWSEQUENTIALID() ROWGUIDCOL,
3     Employee_Name varchar(60)
4     CONSTRAINT Guid_PK PRIMARY KEY (guid) );
5

The answer is usually(1), ‘Ok, let me check it out.. Ah yes – you have to put name of the DEFAULT constraint before the type of constraint:

1 CREATE TABLE dbo.Employees
2     (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
3     Employee_Name varchar(60)
4     CONSTRAINT Guid_PK PRIMARY KEY (guid) );

Why many people stumble on syntax errors? Is the syntax poorly documented? No, the issue is, that correct syntax of the CREATE TABLE statement is documented very well in Books Online and is.. intimidating. Many people can be taken aback by the rather complex block of code that describes all intricacies of the statement.

However, I don’t know better way of defining syntax of the statement or command.

The notation that is used to describe syntax in Books Online is a form of Backus-Naur notatiion, called BNF for short sometimes. This is a notation that was invented around 50 years ago, and some say that even earlier, around 400 BC – would you believe? Originally it was used to define syntax of, rather ancient now, ALGOL programming language (in 1950’s, not in ancient India).

If you look closer at the definition of the BNF, it turns out that the principles of this syntax are pretty simple. Here are a few bullet points:

italic_text is a placeholder for your identifier
<italic_text_in_angle_brackets> is a definition which is described further.
[everything in square brackets] is optional
{everything in curly brackets} is obligatory
everything | separated | by | operator is an alternative
::= “assigns” definition to an identifier

Yes, it looks like these six simple points give you the key to understand even the most complicated syntax definitions in Books Online. Books Online contain an article about syntax conventions – have you ever read it?

Let’s have a look at fragment of the CREATE TABLE statement:

 1 CREATE TABLE 
 2     [ database_name . [ schema_name ] . | schema_name . ] table_name 
 3         ( { <column_definition> | <computed_column_definition> 
 4                 | <column_set_definition> }
 5         [ <table_constraint> ] [ ,...n ] ) 
 6     [ ON { partition_scheme_name ( partition_column_name ) | filegroup 
 7         | "default" } ] 
 8     [ { TEXTIMAGE_ON { filegroup | "default" } ] 
 9     [ FILESTREAM_ON { partition_scheme_name | filegroup 
10         | "default" } ]
11     [ WITH ( <table_option> [ ,...n ] ) ]
12 [ ; ]

Let’s look at line 2 of the above snippet: This line uses rules 3 and 5 from the list. So you know that you can create table which has specified one of the following.

just name – table will be created in default user schema
schema name and table name – table will be created in specified schema
database name, schema name and table name – table will be created in specified database, in specified schema
database name, .., table name – table will be created in specified database, in default schema of the user.

Note that this single line of the notation describes each of the naming schemes in deterministic way. The ‘optionality’ of the schema_name element is nested within database_name.. section. You can use either database_name and optional schema name, or just schema name – this is specified by the pipe character ‘|’.

The error that user gets with execution of the first script fragment in this post is as follows:

Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

Ok, let’s have a look how to find out the correct syntax. Line number 3 of the BNF fragment above contains reference to <column_definition>. Since column_definition is in angle brackets, we know that this is a reference to notion described further in the code. And indeed, the very next fragment of BNF contains syntax of the column definition.

 1 <column_definition> ::=
 2 column_name <data_type>
 3     [ FILESTREAM ]
 4     [ COLLATE collation_name ] 
 5     [ NULL | NOT NULL ]
 6     [ 
 7         [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
 8       | [ IDENTITY [ ( seed ,increment ) ] [ NOT FOR REPLICATION ] 
 9     ]
10     [ ROWGUIDCOL ] [ <column_constraint> [ ...n ] ] 
11     [ SPARSE ]

Look at line 7 in the above fragment. It says, that the column can have a DEFAULT constraint which, if you want to name it, has to be prepended with [CONSTRAINT constraint_name] sequence. The name of the constraint is optional, but I strongly recommend you to make the effort of coming up with some meaningful name yourself. So the correct syntax of the CREATE TABLE statement from the beginning of the article is like this:

1 CREATE TABLE dbo.Employees
2     (guid uniqueidentifier CONSTRAINT Guid_Default DEFAULT NEWSEQUENTIALID() ROWGUIDCOL,
3     Employee_Name varchar(60)
4     CONSTRAINT Guid_PK PRIMARY KEY (guid) );

That is practically everything you should know about BNF. I encourage you to study the syntax definitions for various statements and commands in Books Online, you can find really interesting things hidden there.

Technorati Tags: SQL Server,t-sql,BNF,syntax

(1) No, my answer usually is a question – ‘What error message? What does it say?’. You’d be surprised to know how many people think I can go through time and space and look at their screen at the moment they received the error.

Setting reporting database–what happens behind the scenes

Piotr Rodak — Sun, 28 Nov 2010 14:30:07 GMT

When you install reporting server, you have in general two options available – either you install the reporting server and configure its database at the same time, or install just reporting service and point it to an existing database later.

We had recently opportunity to configure server to point to existing reporting database. We encountered a security related issue, because the account we were using to connect to the SQL Server database engine was not ‘good enough’ to configure reporting service connectivity. Namely, the installation fails if you are not a sysadmin, security admin, or dbo on master and msdb databases.

So what is happening if you are configuring reporting server to point to existing database? This is how it seems to work in SQL Server 2008 R2:

First, new role called RSExecRole is added to master database if it doesn’t exist:

1 use master
2 go
3 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
4 BEGIN
5  EXEC sp_addrole 'RSExecRole'
6 END
7 
8 go

Then do the same in the msdb database:

1 use msdb
2 
3 go
4 
5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
6 BEGIN
7  EXEC sp_addrole 'RSExecRole'
8 END
9 go

And in reporting database and the temp database for reporting:

 1 USE [ReportServer$SQL1]
 2 
 3 go
 4 
 5 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
 6 BEGIN
 7  EXEC sp_addrole 'RSExecRole'
 8 END
 9 
10 go
11 
12 USE [ReportServer$SQL1TempDB]
13 go
14 
15 if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
16 BEGIN
17  EXEC sp_addrole 'RSExecRole'
18 END
19 
20 go
21

Next the reporting server configuration manager is mapping user to the login we want to use in each of the databases, interestingly in the order as below:

 1 
 2 USE [ReportServer$SQL1]
 3 
 4 go
 5 
 6 
 7 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
 8 BEGIN
 9     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
10     BEGIN
11         EXEC sp_grantdbaccess N'RSUser'
12     END
13 END
14 
15 go
16 
17 
18 USE [ReportServer$SQL1TempDB]
19 
20 go
21 
22 
23 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
24 BEGIN
25     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
26     BEGIN
27         EXEC sp_grantdbaccess N'RSUser'
28     END
29 END
30 
31 go
32 
33 
34 USE [msdb]
35 
36 go
37 
38 
39 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
40 BEGIN
41     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
42     BEGIN
43         EXEC sp_grantdbaccess N'RSUser'
44     END
45 END
46 
47 go
48 
49 USE [master]
50 go
51 
52 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin =1)
53 BEGIN
54     if not exists (select name from sysusers where name = N'RSUser' and issqluser = 1)
55     BEGIN
56         EXEC sp_grantdbaccess N'RSUser'
57     END
58 END
59 
60 GO
61

Eventually the user is added to the RSExecRole:

 1 
 2 USE [ReportServer$SQL1]
 3 go
 4 
 5 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
 6 BEGIN
 7     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
 8 END
 9 
10 go
11 
12 
13 USE [msdb]
14 go
15 
16 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
17 BEGIN
18     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
19 END
20 go
21 
22 USE [master]
23 go
24 
25 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
26 BEGIN
27     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
28 END
29 
30 go
31 
32 
33 USE [ReportServer$SQL1TempDB]
34 go
35 
36 if not exists (select name from master.dbo.syslogins where name = N'RSUser' and sysadmin = 1)
37 BEGIN
38     EXEC sp_addrolemember 'RSExecRole', N'RSUser'
39 END
40 
41

Ha. I was about to give a rant about implementation of sp_addrolemember system procedure, which in SQL 2005 is using sysusers and other obsolete objects. The version in SQL Server 2008 R2 has been rewritten from what I see, and is using sys.database_principals in its logic. Well done Microsoft .

Technorati Tags: SQL Server,t-sql,reporting services

DEFAULT constraints and sp_bindefault

Piotr Rodak — Mon, 20 Sep 2010 22:22:00 GMT

When you deal with old databases, with origins somewhere around second half of nineteen nineties, you can expect all kinds of oddities, like funny naming conventions, (lack of) referential integrity, absence of clustered indexes and things like rules and defaults created separately from tables DDL.

I just happen to work with such database. I see many tables which some time ago were migrated from a Sybase system that have their DEFAULT constraints defined with sp_bindefault stored procedure. You don’t have to memorize exact syntax of the invocation of this procedure because it is marked as obsolete and should not be used anymore:

This feature will be removed in a future version of Microsoft SQL Server. Do not use this feature in new development work, and modify applications that currently use this feature as soon as possible. We recommend that you create default definitions by using the DEFAULT keyword of the ALTER TABLE or CREATE TABLE statements instead. For more information, see Creating and Modifying DEFAULT Definitions.

Now, when I read the documentation, the sp_bindefault has some interesting capability. If you create default and user defined type (alias), and you bind this default to the type with sp_bindefault, you can inadvertently affect all columns of that type in all tables in the database if they don’t have any default or are using existing default of the alias type:

        1: create table dbo.t1

       2: (

       3: col1 int not null,

       4: col2 int not null constraint DF_t1col2 default(0)

       5: )

       6: go

       7: ---create default for the column

       8: create default dbo.DFValue_1 as 1

       9: go

---bind default to the column

exec sp_bindefault 'dbo.DFValue_1', 'dbo.t1.col1'

go

---create udt

create type ssn

from varchar(15) not null

go

--create default - empty string

create default dbo.DF_UDTEmpty as ''

go

--bind default to the udt

exec sp_bindefault 'dbo.DF_UDTEmpty', 'dbo.ssn'

go

--add new column to the table

alter table dbo.t1 add secnumber1 ssn

go

---insert into the table and select results

insert dbo.t1(col1, col2, secnumber1) default values

  

---secnumber1 is empty string

select * from dbo.t1

go

---create second table

create table dbo.t2

(

    col1 int constraint DF_t2_col1 default(10),

    secnumber ssn

)

go

create default dbo.DF_SSNNotEmpty as '<unknown ssn>'

go

---note here - no @futureonly param provided

exec sp_bindefault 'dbo.DF_SSNNotEmpty', 'dbo.ssn'

go

--insert into t1 and t2

insert dbo.t1(col1, col2, secnumber1) default values

insert dbo.t2(col1, secnumber) default values

go

---check contents of the tables

select * from dbo.t1 --what?

select * from dbo.t2

go

If you run the above script you will see that the default value of the column secnumber1 in table dbo.t1 has changed to ‘<unknown ssn>’! You can really hurt yourself if you make a mistake and omit the parameter @futureonly in the call to sp_bindefault. If you use @futureonly parameter, the above behavior will not occur. It’s pretty easy to make changes you don’t know about until user calls you at 3 am on Sunday.

It is good idea to change tables to use standard column DEFAULT constraints. This should be of course made with care, probably only when you change the tables anyway. But, if you were curious how many tables you have in your database that use the DEFAULT objects, you can run this script:

       1: ---all default objects with names of tables they are assigned to.

       2: select b.default_object_id, a.name default_object_name, 

       3: b.object_id table_id, object_schema_name(b.object_id) table_schema, 

       4: object_name(b.object_id) table_name, b.name column_name, b.column_id

       5: from sys.objects a inner join sys.all_columns b

       6: on a.object_id = b.default_object_id

       7: where 1=1

       8: and a.object_id not in (select object_id from sys.default_constraints)

Full testing script is attached to the post as usual, enjoy :).

Partitioned views

Piotr Rodak — Sat, 11 Sep 2010 00:27:00 GMT

I must say I am a big fan of views. They give flexibility of changing the logic of the data retrieval without affecting DAL layers in your application. I posted series of articles about updateable views and triggers on views, their application in SSIS for example.

Recently we finished development of system that loads data from several external sources, makes some calculations on the data and sends data over FTP to downstream systems. I designed the whole data interface to use views and stored procedures. This way, even though we used ORM to perform some of the activities, it was not accessing bare tables. This is good, because you can implement simpler and more flexible security model. The other advantage is that you if you use some simple techniques, you can load data to table and then expose the new data through a view without exposing the logic to the upper layers of your application.

It is quite common requirement to be able to maintain multiple versions of data within table. The requirement is also that data import should not affect other processes which read current version of data. With views it is really simple to achieve. There are many ways of doing this, but let’s have a look at partitioned views technique.

Partitioned view is a view that selects data from more than one table. These tables have to follow specific requirements. Amongst the other, there has to be so called partitioning column defined, which has trusted CHECK constraint defined on it. The tables have to be joined by UNION ALL operator. If you want the view to be updateable, the partitioning column has to be part of the primary key of all tables belonging to the view. And you have to select all columns from the table.

OK, but what is the purpose of creating partitioned views? The short answer is performance. Tables tend to grow bigger and bigger. It becomes more and more expensive to run index updates, backups, and queries against them. If you are able to partition your data and you do it in a right way, you can alleviate some of the issues related to big tables. Classic example would be partitioning by date – you can keep month worth of data in each table and span the view over 12 tables to cover the whole year. Nice thing about partitioned views is that you can index underlying tables in a different way, so for example history tables have more indexes allowing for faster data retrieval, while current month table can be optimized for faster inserts and updates.

Let’s have a look at this scenario. I will use as an example table Sales.CurrencyRate from AdventureWorks database. This table contains 13532 rows in my database, but let’s imagine we have 135 million rows.

When I run this query, I get 37 rows spanning years 2001 - 2004:

        1: select count(*) NumberOfRates, Year([CurrencyRateDate]) RateYear, Month([CurrencyRateDate]) RateMonth 

       2: from Sales.CurrencyRate    

       3: group by Year([CurrencyRateDate]), Month([CurrencyRateDate])   

       4: order by RateYear, RateMonth

We will create four tables, each for each year that is represented by CurrencyRateDate column.

A slightly abbreviated DDL for the Sales.CurrencyRate table is as follows:

       1: CREATE TABLE [Sales].[CurrencyRate](

       2:     [CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,

       3:     [CurrencyRateDate] [datetime] NOT NULL,

       4:     [FromCurrencyCode] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL,

       5:     [ToCurrencyCode] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL,

       6:     [AverageRate] [money] NOT NULL,

       7:     [EndOfDayRate] [money] NOT NULL,

       8:     [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyRate_ModifiedDate]  DEFAULT (getdate()),

       9:  CONSTRAINT [PK_CurrencyRate_CurrencyRateID] PRIMARY KEY CLUSTERED 

      10: (

      11:     [CurrencyRateID] ASC

      12: )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

      13: ) ON [PRIMARY]

If we want to have updateable partitioned view, we need to modify the constraint to include date part, which will allow the execution engine to identify partition that has to be modified during DML operation. The easiest is to add the CurrencyRateDate column to the primary key.

Let’s create four tables then, one for each year of the data. I will not place here whole DDL for all tables, you can find it in the attached file. This is however the first partition table:

       1: CREATE TABLE [Sales].[CurrencyRate_2001](

       2:     [CurrencyRateID] [int] IDENTITY(1,1) NOT NULL,

       3:     [CurrencyRateDate] [datetime] NOT NULL,

       4:     [FromCurrencyCode] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL,

       5:     [ToCurrencyCode] [nchar](3) COLLATE Latin1_General_CI_AS NOT NULL,

       6:     [AverageRate] [money] NOT NULL,

       7:     [EndOfDayRate] [money] NOT NULL,

       8:     [ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_CurrencyRate_2001_ModifiedDate]  DEFAULT (getdate()),

       9:     constraint CHK_CurrencyRateDate_2001 CHECK (CurrencyRateDate>= '20010101' and CurrencyRateDate < '20020101'),

      10:  CONSTRAINT [PK_CurrencyRate_2001_CurrencyRate_2001ID] PRIMARY KEY CLUSTERED 

      11: (

      12:     [CurrencyRateID] ASC,

      13:     [CurrencyRateDate] ASC

      14: )

      15: ) ON [PRIMARY]

As you see above, the CurrencyRateDate column has been included into the primary key of the table and I added constraint CHK_CurrencyRateDate_2001 that will take care of the range of dates that can exist in the table. You can use inequality constraint if you’d like to store rows for dates from 2001 or older. Tables for 2002, 2003 and 2004 contain constraints disallowing inserting data outside of the ranges, similar to the the constraint shown above.

I removed identity column from partition tables to make the view updateable – this is one of requirements.

The view is defined as follows:

       1: create view Sales.vCurrencyRate

       2: as

       3: select * from Sales.CurrencyRate_2001

       4: union all 

       5: select * from Sales.CurrencyRate_2002

       6: union all

       7: select * from Sales.CurrencyRate_2003

       8: union all

       9: select * from Sales.CurrencyRate_2004

Let’s insert some data to the view – that is, the underlying partition tables:

       1: insert Sales.vCurrencyRate

       2: (

       3:     [CurrencyRateID],

       4:     [CurrencyRateDate],

       5:     [FromCurrencyCode],

       6:     [ToCurrencyCode],

       7:     [AverageRate],

       8:     [EndOfDayRate],

       9:     [ModifiedDate]

      10: )

      11: select [CurrencyRateID],

      12:     [CurrencyRateDate],

      13:     [FromCurrencyCode],

      14:     [ToCurrencyCode],

      15:     [AverageRate],

      16:     [EndOfDayRate],

      17:     [ModifiedDate] from Sales.CurrencyRate

This is the execution plan created for the insert:

As you see, database engine split data amongst all partition tables according to the partitioning column constraints. This way, you can easily move data from one table to several smaller partitions. Of course, you wouldn’t want to run simple insert statement as above for 135 million rows, but you get the idea.

Partitioned views can be very useful, but apart from the conditions that I listed earlier in this article, there are some catches that might bite you and you have to carefully ponder on your design if you want to make them updateable.

These are some of these gotchas:

Tables referenced in updateable partitioned views cannot have identity column. If they have, you will get following error message:

Msg 4433, Level 16, State 4, Line 3
Cannot INSERT into partitioned view 'AdventureWorks.Sales.vCurrencyRate' because table '[AdventureWorks].[Sales].[CurrencyRate_2001]' has an IDENTITY constraint.

Tables cannot have computed columns if you want to have updateable partitioned view. If you try to insert rows into view on tables that have such columns, the error message is following:

Msg 4406, Level 16, State 1, Line 1
Update or insert of view or function 'Sales.vSalesOrderHeader' failed because it contains a derived or constant field.

The partitioning column has to be defined using equality or inequality operators. For example, you cannot use datepart(year, …) to defined date range for the column, you have to use either BETWEEN.. AND.. or >=.. < operator. Otherwise the error message will be as follows:

Msg 4436, Level 16, State 12, Line 3
UNION ALL view 'AdventureWorks.Sales.vCurrencyRate' is not updatable because a partitioning column was not found.

The partitioned views do not have to be updateable. You can design tables with computed columns, IDENTITY columns and still use partitioned views to offload certain queries. One of the scenarios is for example daily upload of data. You can create a staging table which will accept the data and after ETL is finished, you alter view definition to include new table and possibly exclude one with older data.

I attach the script for your convenience. You can run it on AdventureWorks database or use it as a template for your code.