I really enjoyed speaking at the Portland SQL Server User Group meeting last night about SQL Server security...and I have an update.

We were talking about the supposed inability of auditing to audit usage of sys.fn_get_audit_file, the system function that reads an audit log. Raul Garcia of the SQL Server team had the answer. "For the particular scenario in this bug (sys.fn_get_audit_file), the permission being exercised is SELECT, not EXECUTE, hence the apparent failure to audit usage."

An database audit specification in the master database for SELECT ON OBJECT::sys.fn_get_audit_file BY PUBLIC will audit it, regardless of the "current database" when the function is issued.

Thanks Raul. 

Today I came across the new SQL Server 2008 Compliance Portal. This portal has information and links to the new Compliance whitepaper and compliance scripts (the "sample files" at the bottom of the main page on the compliance portal). New features for ensuring compliance in SQL Server 2008 include Policy-Based Management, Auditing, and TDE, to name just some of the ones that come to mind.

This information fits in well with some of the talks I'll be doing at SQLConnections in Orlando in March. I'm speaking about "Practical SQL Server 2008 Security for Developers and Architects" and "Programming PBM and Data Collection with PowerShell". I'll also be doing a day-long preconference talk on spatial data/location aware apps, and a talk on programming filestreams (unrelated to compliance). Hope to see you there.

Enjoy the compliance portal!

For folks that have been asking...my latest whitepaper "SQL Server 2005 Security Best Practices - Operational and Administrative Tasks" was posted on the Technet website this week. It also covers the nuances of security when using SQL Server SP2 and Vista. Enjoy!

When installing previous versions of SQL Server, I'd always keep a list of the exact privileges that a SQL Server service account would need. I'd make up a new user and give them exactly these rights. The SQL Server installation program would grant the appropriate permissions during the install, and I'd be set. Principle of least privilege, run SQL Server and associated as the minimum privilege account needed. If I thought about changing the service account, I'd call up knowledge base article Q283811 and sigh at the list of work items. And make sure that I used Enterprise Manager, as the article recommends.

Things are a lot simpler in SQL Server 2005. When you install SQL Server, the process creates Windows groups with exactly the minimum privlege that you need for the appropriate services. The user you specify during the install (which should be a "normal", low privilege user and NOT administrator or LocalSystem, is plunked into the appropriate group. There is a Windows Group for each SQL Server-related service, all nicely decked out with least privileges (file system DACLs, security policies, registry key access, etc). NO more privilege lists to keep around, although they are doc'd in BOL should you need to refer to them.

If you should need to change the service account, things are even better. Just put your new Service Account user in the appropriate Windows groups, and voila, no long list of tasks. Very cool. Or use Configuration Manager.

Twas' not all sweetness and light, however. an FAQ during the early days was "why does SQL Server setup "clutter" my directory with groups?" That's why. First time you need to change service accounts, you'll appreciate the "clutter".

Today was the first of my two presentations as part of the TechNet Webcast Series for the ITPro, about SQL Server 2005 security. The session went a bit long, as there are so many new security-related features to cover, wouldn't you agree? I got some really great feedback on the key management portions of the talk, some folks suggested that the key management features alone would make a good follow-up talk. Agreed..but I didn't want to leave out the rest.

If you've received your link to this blog through email and really want the key management scripts (and password changing program), they're here, the link is at the end of this entry. If you haven't visited this blog before there are quite a number of security-related entires here for your enjoyment, and almost all the entries are SQL Server 2005-related.

In case didn't get them from the slides, the blogs on SQL Server 2005 security by security team members Laurentiu Christofor and Raul Garcia are excellent.

I'll be doing one more talk in the series, on Service Broker operational considerations. I think Service Broker is one of the most interesting (and important) features in SQL Server 2005. See you there.

I've always been slightly puzzled by the permission "EXECUTE" on an XML SCHEMA COLLECTION. Say I have an XML SCHEMA COLLECTION named MySchemas and a table that uses it:

CREATE TABLE MyDocuments (
  id INT PRIMARY KEY IDENTITY,
  thexml XML (MySchemas)
)

And suppose I have a user named FRED that I grant access to the TABLE:

GRANT SELECT, INSERT, UPDATE, DELETE on MyDocuments to FRED

Now FRED can DELETE and SELECT from the table, but because he does not have EXECUTE permission on the XML SCHEMA COLLECTION, he can't INSERT or UPDATE the column that uses it with a schema-valid document. He also can't use typed parameters or variables. UNLESS the value of the XML column, variable, or parameter is NULL. So this works for FRED...

INSERT MyDocuments VALUES(NULL) -- INSERTing a non-NULL would fail.

So does this:

DECLARE @x XML (MySchemas)

But assignment of a document that corresponds to the XML SCHEMA COLLECTION to the variable @x fails unless FRED has EXECUTE on the XML SCHEMA COLLECTION. You grant the permission like this:

GRANT EXECUTE ON XML SCHEMA COLLECTION::MySchemas TO FRED

I can only guess that the reason for this privilege is to keep folks from "probing" the XML SCHEMA COLLECTION (perhaps there is sensitive information in the restrictions) by seeing what works on a column insert or variable assignment and what doesn't. And following the validation error messages. Perhaps.

BTW, the Books Online (I have the December update) gets it right except for SELECT. It says:

Denying the EXECUTE permission denies the principal the ability to insert or update the values in columns, variables, and parameters that are typed or constrained by the XML schema collection. It also denies the principal the ability to query the values in those same xml type columns and variables.

Dynamic SQL executed in a stored procedure executes by default using the security context of the CALLER of the procedure rather than the OWNER. That's the way SQL Server has always worked, and although SQL Server 2005 lets you EXECUTE AS OWNER (among other choices), EXECUTE AS CALLER is still the default.

So how does this relate to SQL statements execute in a .NET stored procedure, function, or trigger? Do these execute as caller or owner? Turns out it depends on what statement you are executing. Executing an "ordinary" SQL statement like this:

public static void GetAuthorsNET {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("select * from dbo.authors", conn)
{
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}
}

GRANT someuser EXECUTE on dbo.GetAuthorsNET

EXECUTE AS USER='someuser'
EXEC dbo.GetAuthorsNET
GO
REVERT

executes the SELECT statement in the .NET code as CALLER and throws a permission denied error if the caller doesn't have direct SELECT access to the authors table. The same contruct in a T-SQL procedure:

CREATE PROCEDURE dbo.GetAuthorsSQL
AS
SELECT * FROM dbo.authors

GRANT someuser EXECUTE on dbo.GetAuthorsSQL

would execute the SELECT as the OWNER of the stored procedure, not the caller and the SELECT succeeds.

OK. How about the following .NET code? Does it execute dbo.byroyalty as the OWNER of the stored procedure?

public static void ExecByRoyalty {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("dbo.byroyalty", conn)
{
  cmd.CommandType = CommandType.StoredProcedure;
  cmd.Parameters.AddWithValue("@percentage", 50);
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}
}

GRANT someuser EXECUTE on dbo.ExecByRoyalty -- grant on calling proc (owner by dbo)
DENY someuser EXECUTE on dbo.ByRoyalty -- deny on called proc

This executes the ByRoyalty proc AS the OWNER of the ExecByRoyalty proc, ownership chain intact. Even if someuser executes ExecByRoyalty. Oh.

Time for a tiebreaker. How about this one?

public static void ExecByRoyaltyAsString {
using (SqlConnection conn = new SqlConnection("context connection=true"))
using (SqlCommand cmd = new SqlCommand("exec dbo.byroyalty @perc", conn)
{
  cmd.CommandType = CommandType.Text; // not a sproc, a textual execute statement, does it matter?
  cmd.Parameters.AddWithValue("@perc", 50);
  conn.Open();
  SqlContext.Pipe.ExecuteAndSend(cmd);
}
}

This executes the ByRoyalty proc AS the OWNER of the ExecByRoyaltyAsString proc, ownership chain intact. The sproc works even when 'someuser' executes it. Interesting.

Service Broker security is the subject of confusion even among people who think they know how it works. Some of the confusion occurs because security was tightened up in the last few CTPs.

I've read in two different places that Service Broker conversations always need to have a master key in the database(s) where the services run. Not so. You don't need a database master key (and this is in RTM) if:

1. Both services (initiator and target) live in the same database
2. You begin the conversation using ENCRYPTION = OFF in the BEGIN DIALOG statement

ENCRYPTED = ON is the default, and you do need a database master key in this case, hence the confusion.

After writing about a lot of new security features that were added since we published our "First Look at SQL Server 2005 book" its nice to report on one that we had in there (at beta2), but never appeared in the product. Until now.

In the September CTP version on SQL Server 2005 (I think its probably the last CTP), you need special permissions to CREATE an ASSEMBLY with UNSAFE permission set. You must have either one of the following:

1. DBO has UNSAFE ASSEMBLY permission and database has TRUSTWORTHY property on.
or
2. ASSEMBLY is signed with an asymmetric key or cert that has a LOGIN with UNSAFE ASSEMBLY permission.

We'd written about the second choice. Here's one of the combinations that works:

1. Create a strong named key in c:\temp\assm.snk
2. Sign the assembly unsafe1.dll with this strong named key
3. Make a SQL Server LOGIN for the key.
4. Give LOGIN the appropriate permissions
5. Catalog the unsafe assembly

In code, it looks like this:

-- master key in master database
USE master
go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPassword1'
go

-- keyfile generated by VS or .NET command line utilities
CREATE ASYMMETRIC KEY assm FROM FILE='c:\temp\assm.snk'
go

CREATE LOGIN snk FROM ASYMMETRIC KEY assm
go

GRANT UNSAFE ASSEMBLY TO snk
GO

USE somedb
GO

CREATE ASSEMBLY unsafeassemblyex FROM 'c:\temp\unsafe1.dll'
  WITH permission_set = unsafe
GO

That's only one variation of it. You can also use the key stored in the assembly (CREATE ASYMMETRIC KEY FROM EXECUTABLE FILE=...) or an assembly already cataloged inside the database (CREATE ASYMMETRIC KEY FROM ASSEMBLY...). You can do the same thing with certificates.

So one of the SQLCLR security features we wrote about over a year ago has come to pass.

This one's been around for a while, but don't think we'd mentioned it in the book. In addition to Windows logins and SQL logins, you can have SQL logins mapped to a certificate or to an asymmetric key. Same with database users. One of the reasons you might want to do this is to implement Service Broker transport security. That is, create a certificate in master, then create a login mapped to the cert. Now use the cert in the CREATE ENDPOINT ... FOR SERVICE_BROKER.

To allow your business partners to authenticate with your endpoint (in their implementation of transport security) you BACKUP the cert to a file (the default is to BACKUP public key portion only) and then send the cert to your business partner. They install it in master to use to authenticate your endpoint.

There a variation of CREATE USER foo FROM CERTIFICATE that's caused a lot of discussion on one of the newsgroups lately. That is "CREATE USER ... WITHOUT LOGIN". This is used with Service Broker too, but with full dialog security, rather than transport security. This USER has only the permissions it needs (usually only SEND on the SERVICE/ or RECEIVE on the QUEUE in question). So this is used for the following scenario:

1. Create a user without a login
2. Create a cert owned by the user
3. Backup the cert (public key only) to a file, send to business partner
4. They create a user without login
5. They install your cert from a file
6. They GRANT the user the appropriate access
7. Your BEGIN DIALOG uses full dialog security (ENCRYPTION = ON is the default).

Folks on newsgroups have said it (USER WITHOUT LOGIN) is undoc'd, but I remembered seeing this before. Then I remembered where. It's in Neils' remote Service Broker examples here.

You've probably heard by now of the usage of the EXECUTE AS clause with procedural code. As in "CREATE PROCEDURE foo WITH EXECUTE AS OWNER". But EXECUTE AS can also be used at a session level,like this:

EXECUTE AS USER='fred'
-- some T-SQL here
REVERT

This is meant to replace the SETUSER verb because you might want things executed as a specfic login as well as user. So there's also EXECUTE AS LOGIN='freds_login'. You can aribtrarily nest EXECUTE AS blocks and there's subject to the nice, granular, IMPERSONATE privilege. Originally, I'd heard there was going to be a "REVERT ALL", but there's security mischief that might happen with this, so it never happened.

BUT.
There are two variations of EXECUTE AS that are interesting, based on security considerations. First one is "EXECUTE AS USER='fred' WITH NO REVERT". Exactly what it sounds like.

Next one is EXECUTE AS WITH COOKIE. The idea is similar to what I described in sp_unsetapprole. You don't want just any ol' level of impersonation to do REVERT. So EXECUTE..WITH COOKIE gives you a cookie to use with REVERT.

One of the things folks would always ask during the Ascend program was "anything new for application roles? do they support connection pooling yet?". Well, it the most recent CTP (June, July?) there is.

You can unset application roles now in addition to setting them. To unset you need to create a cookie, using an alternate form of sp_setapprole. It looks like this:

sp_addapprole 'myapp', 'StrongPW1'
GO

DECLARE @theCookie varbinary(256)
EXEC sp_setapprole 'myapp', 'StrongPW1',
     @fCreateCookie = true, @cookie = @theCookie OUTPUT
-- Check user, should be myapp
SELECT USER_NAME()
-- now, unset it
EXEC sp_unsetapprole @theCookie
-- Check user should be original user
SELECT USER_NAME()
GO

So does it now support connection pooling with ADO.NET or OLE DB/ODBC? I'd think the capability exists, but because this is a recent feature addition and they'd have to store the cookie in the pooling code somewhere, to use with sp_reset_connection, I don't think its built in yet. But, if you remember to store the cookie and unset it yourself...

In July 2004, encryption built-ins and key management had just been introduced in SQL Server 2005. Now its old news. They'll be a good-sized section about it in the book revision. You're probably heard of:

EncryptBy (Key/Certificate/PassPhrase) and
DecryptBy (Key/Certificate/PassPhrase)

But there's also the less well-known:

SignByAsymKey/SignByCert
VerifySignedByAsymKey/VerifySignedByCert
HashBytes

and also the aptly-named: DecryptByKeyAutoCert and DecryptByKeyAutoAsymKey

The "auto" versions are for convenience. When you're doing encryption or decryption, you have to remember to open the keys that have been used to encrypt the data you want to read. And to close them when you're done. The "Auto" versions do this for you, when you're decrypting, so you can provide a decrypting VIEW, for example. I'll have more to say about this soon.

While working on the new edition of our book for after RTM, I've been looking around for security features that I missed or that didn't exist when we wrote it (May 2004 and before). Here's one:

In SQL Server 2005, you can DISABLE a LOGIN by using ALTER LOGIN. You can also change the name of any LOGIN (including 'sa'), making things more like Windows security. You would change the name of the sa account to give hackers one more item to guess. Here's how.

ALTER LOGIN sa WITH NAME = fred
go

-- fred (sa) on vacation
ALTER LOGIN fred DISABLE
go

-- When the sa returns from vacation
ALTER LOGIN fred ENABLE
go

I'm known for my vivid imagination when making up test/exposition examples. I have a cat named Sam. So, once upon a time, I wrote:

CREATE CREDENTIAL myuser
 WITH IDENTITY = 'mydomain\myuser', SECRET = 'some56*Z'
GO

CREATE LOGIN sam WITH PASSWORD = 'meowPw!a3'
GO

ALTER LOGIN sam WITH CREDENTIAL = myuser
GO

The DDL works. Now, I'd hoped to use this alternate credential so that Sam (a SQL Server login) could use the credential to use an external_access SQLCLR procedure that reads a file on the file system. This would require (since we have a nice NTFS file system with ACLs), that the SQLCLR procedure use the WindowsIdentity property on SqlPipe and do the impersonation. Works with Windows users, now Sam could do it too. I thought.

Just lately I found out that the alternate credential will not be useable with SQLCLR. WindowsIdentity will return null for Sam, regardless. This credential is useable with SQL Agent, something folks have always wanted for SQL Agent.

So no file system access for Sam, at least through SQLCLR and CREDENTIAL object. Unless the SQL Server service account has access to it and I don't do impersonation. He'll have to walk on the keyboard until he opens the file. As usual.

After writing a blog entry on "How DO you change your SQL Login Password" and complaining that "neither" SSMS nor SQLCMD allows you to change this" I was using this feature on the June CTP and, lo and behold, both of these utilities allow changing your password.

SQLCMD -? shows
-z new password
-Z new password and exit

And in SSMS, if you use a SQL login whose password has expired (or is designated "must change on first login") you get a nice, GUI-based “old password/new password/re-enter new password” prompt.

Don't know how I missed this before, sorry for complaining. If you use SQL Logins with login policies, code like this really should be part of your application. Perhaps a way to change password before it expires as well, like SQLCMD does. I'm still surprised that SSMS doesn't have this option on the login screen.

SQL Server 2005 will, by default on Windows Server 2003 systems, enforce password policies for SQL Server logins as well as Windows logins. Nice feature, but this means that your SQL Server login password can expire. So how do you change it? Well certainly the DBA can change it as (s)he always has, but you'd hate to bother your DBA every 42 days. Never mind what the DBA would think of that... And the user interface programs, SSMS and SQLCMD don't yet provide that feature. Neither does Visual Studio 2005 Server Explorer.

The functionality does exist in the supported database APIs. That is ADO.NET, OLE DB, and ODBC. With ADO.NET you have to be using 2.0, with OLE DB and ODBC the new SNAC providers are required. In ODBC, there is a new connection option SQL_COPT_SS_OLDPWD. In OLE DB there's an Old Password connection string parameter. In ADO.NET 2.0 SqlClient it's a static method on SqlConnection called (amazingly enough) ChangePassword. It takes to strings as input and here's how it works.

You change your connection code to use a loop, like while conn.ConnectionState == ConnectionState.Closed. Loop as many times as you like, most folks will probably loop twice. Bracket your calls to Open with a try-catch block. In the catch block, look for the following error codes:

18487 - Password Expired
18488 - Must change password on first login

If you get one of these call ChangePassword. You'd think that the parameters are "old password, new password". They are not. The first parameter must have enough information to connect to the server, including at minimum server name, your userid and your old password. The second parameter is just your new password. This changes your password, now change your connection string and Open again.

There are a couple of repercussions/refinements to this:

1. You obviously shouldn't even think about keeping password in the program, if you ever did this before. Check out my MSDN article for the built-in place to keep connection strings now.
2. This can only be used to change passwords on a SQL Server 2005 server. And only SQL Login passwords, naturally. It requires that the server and client be using the new network libraries.
3. There is no standard "New Password", "Old Password" GUI box. You need to make one yourself. Standard cavaets for passwords in GUIs apply.
4. There is no way to currently tell with standard SQL Server calls, how soon your password will expire. Think "Your password will expire in N days" message we all know and love.

So that's it. I have a "rough and ready" code example (that I wrote on a bet with Larry Chestnut at an Ascend gig a while ago) I'll probably clean up and post on my website eventually but this gives you the basic idea. And BTW, this isn't meant to push SQL Logins on anyone. If you can use only Windows logins in SQL Server (any release) and forgo SQL Logins entirely, PLEASE DO.

Speaking of SQL Profiler brought this to mind. The number one feature that brings a smile to every DBA's face:

GRANT ALTER TRACE TO [somedev]

No longer do you have to listen to developers ask “make me SA so I can run the trace”. Actually, brings a smile to devs too, no longer do they have to beg for it. Just brings the gate over to a more granular permission level. You still do have to ask for ALTER TRACE now...

A new feature of SQL Server 2005 that has been fairly well publicized is the ability, on Windows 2003 operating systems, to enforce password stregth, expiration, and lockout policies on SQL Server logins, as the operating system enforces them on Windows logins. The way that this works is that SQL Server calls NetValidatePasswordPolicy, a Win32 function available on Windows 2003. So if I have a machine policy (either standalone or more likely inherited from a domain policy) that a password must be at least 8 characters long, the following DDL will fail:

CREATE LOGIN bob WITH PASSWORD = 'bob'

you need:

CREATE LOGIN bob WITH PASSWORD = 'bob000000'

However, did you realize that password on other secrets will follow policies as well? For example:

CREATE APPLICATION ROLE somerolename WITH PASSWORD = 'aaa'
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'aaa'
CREATE CERTIFICATE foo WITH SUBJECT = 'foo', ENCRYPTION_PASSWORD = 'aaa'
CREATE SYMMETRIC KEY skey WITH ALGORITHM = DES ENCRYPTION BY PASSWORD = 'aaa'

will all fail for the same policy reasons. The lone straggler, at least as of Dec CTP is ASYMMETRIC KEY. This works...

CREATE ASYMMETRIC KEY akey WITH ALGORITHM = RSA_512
 ENCRYPTION BY PASSWORD = 'a'

I've been lying pretty low lately, doing research on some topics in a slightly different space (but at least obliquely SQL Server 2005 related). Came across a list of interesting student comments today, and I was back trying out (picking at?) one of my favorite spare-time topics to answer a question about. SQL Server 2005 metadata. The question was whether or not the INFORMATION_SCHEMA views were security-sensitive, and...of course they are. There are now (at least) 4 different ways to list the user tables in a database (all subject to security) and they are:

select * from sysobjects where type = 'U'
select * from sys.objects where type = 'U'
select * from sys.tables -- I always wanted a systables in earlier versions
select * from INFORMATION_SCHEMA.tables where TABLE_TYPE = 'BASE_TABLE'

The Dec CTP closed the last loophole; using system procedures like sp_help is now permission sensitive.

But what if you WANT a user to be able to list all of the tables in the database? The “smallest“ permission you can give a user is VIEW DEFINITION privilege on a specific object. As an example, if I had a user named 'fred' that I wanted to 'see' the authors table, I would give:

grant view definition on object::authors to 'fred'

A specific resource is called a securable. Securables exist in a hierarchy. So although there's no straightforward way to give VIEW DEFINITION on all of the tables (that I can see) in a specific schema or database in a single statement, I could give a user access to all of the objects (Table, View, Function, Procedure, Queue, Type, Rule, Default, Synonym, Aggregate, XML Schema Collection) in a particular schema with this one statement.

grant view definition on schema::dbo to 'fred' -- all in dbo schema
grant view definition on database::dbo to 'fred' -- all in the database

Note the VIEW DEFINITION is the “smallest“ permission; it gives a user permission to see that an object exists without being able to see the data. For fred's case, “select * from authors“ would still fail. Giving SELECT also gives view definition privilege, so if you can SELECT against an object, it also shows up in your metadata list. Makes perfect sense. You can tell I'm liking the new metadata views...and the new “all permissions grantable“ concept. Wait until the first user calls up and asks “where did all of the tables in my dropdown list go?“.

Now, back to our regularly scheduled technical content. About schemas, users, and owners.

Although Ed originally created the table, since Fred is the schema owner, Fred owns to table. Ed can get ownership of the table in either of two ways.

1. Someone with authority can alter the table's owner
2. Someone with authority can give Ed "take ownership" permission on the table

Until Ed has "take ownership" permission, he does not and cannot "own" the table he just created.

There are two ways to tell who owns a table. If you know who the schema owner is, "select * from sys.tables" produces a column named principal_id. If the principal_id is NULL, then the table is owned by the schema owner. If the principal_id is not NULL, the table has a specific owner. The other way is to use the OBJECTPROPERTY function and look for the property 'OwnerId'. This gives the exact owner, whether or not it's the schema owner.

This matters because, if you change the schema owner, the owner of the tables with NULL in prinipal_id changes. The owner of "specific-owner" tables does NOT change. So if the schema owner changes, say, to DBO, then DBO owns all the tables in the schema. BUT does not own Ed's table.

This whole thing is made possible because of the interesting meld that had to happen between a SQL Server-specific feature (ownership chains) and a new SQL2003-compliant feature (separation of users and schemas). Is this clear as a bell, now?

Just in case you don't believe it, code below (picks up where other code left off):

-- snip (when I left off, I was Ed)
-- ed cannot get ownership of table
-- this fails
alter authorization on object::fredstuff.edtab to ed
go

-- back to dbo
setuser
go

-- dbo can give the table to ed
-- alter authorization on object::fredstuff.edtab to ed
-- go

-- or dbo can give ed 'take ownership' permission
grant take ownership on fredstuff.edtab to ed
go

setuser 'ed'
go

-- now this works for ed, because he has 'take ownership'
alter authorization on object::fredstuff.edtab to ed
go

-- now ed can SELECT the table
select * from fredstuff.edtab
go

-- ed creates another table in the schema
create table fredstuff.table1 (id int)
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.database_principals
select * from sys.tables
go

-- owned by 'fred' (schema owner)
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed'
select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

setuser 'fred'
go

-- so can fred SELECT both tables
-- because fred is the schema owner
select * from fredstuff.edtab
select * from fredstuff.table1
go

setuser
go

alter authorization on schema::fredstuff to dbo
go

setuser 'fred'
go
-- no access for fred on this table
select * from fredstuff.edtab
-- access for fred on this table
select * from fredstuff.table1
go

setuser
go
setuser 'ed'
go
-- access for ed, he's still the owner
select * from fredstuff.edtab
-- never had access to this table
select * from fredstuff.table1
go

setuser
go

-- note that edtab has a principal_id (ed's)
-- note that table1 (owned by schema owner) has NULL principal_id
select * from sys.tables
go

select * from sys.database_principals
-- owned by 'dbo' (schema owner), this changed
select objectproperty(object_id('fredstuff.table1'), 'OwnerId')
-- owned by 'ed', this did not change

select objectproperty(object_id('fredstuff.edtab'), 'OwnerId')
go

-- snip --

The code for the answer is below. Greg Low is, of course, correct. Fred owns the table, but he owns it by virtue of being the schema owner. Now here's part 2. Do the following:

1. Alter the authorization on Ed's table so that it is owned by Ed.
   (Interesting aside, can Ed do this himself?)
2. Create another table (using either Fred or Ed) in the schema.
   Call it "fredstuff.table1" (my creativity for making sample names is legendary)
3. Alter the schema so that it's owned by another user (say, dbo)

Who owns each table now? How can you tell?

BTW, why does this matter? Because ownership chains go by object *owners*, not by schemas.

-- snip --
create database test
go

use test
go

-- make two users
-- fred has a default schema, ed does not
create user fred for login fred with default_schema = fredstuff
create user ed for login ed
go

-- create the schema for fred
create schema fredstuff authorization fred
go

-- fred and ed can create tables
-- ed can only create tables in fred's schema
grant create table to fred,ed
grant alter on schema::fredstuff to ed
go

setuser 'ed'
go

-- ed creates a table in fred's schema
-- who is the owner?
create table fredstuff.edtab (id int)
go

-- fred (schema owner) is the owner. not ed.
-- ed cannot even SELECT against the table he just created, this fails
select * from fredstuff.edtab
go
-- snip --

People always ask...if ownership chains work the way they do, why do they not work with dynamic SQL? And how about .NET procedures and ownership chains?

Dynamic SQL is supported in nearly every database I've run across, but bad dynamic SQL has "issues", to put it mildly. If you create your dynamic SQL via string concatenation rather then parameterization (there are parts of the SQL statement that cannot be parameterized) and your strings come from user input, you will run up against a security problem known as SQL injection. Users can enter strings in applications, that, when used with concatenation, do things that you never intended your statement to do. You need to be VERY aware of the hazards of SQL injection before even *thinking* about dynamic SQL.

SQL Server procedural code (stored procedures, UDFs, and triggers) runs as the caller of the procedure. Because dynamic SQL can be dangerous, it doesn't go by the ownership chaining rule. Access to database objects in dynamic SQL is always checked. Against the original caller's permissions. In SQL Server 2005, the EXECUTE AS clause can allow procedural code to run as a principal other than the caller, which permits a way to address this behavior (other than the usual way, which was to yell “don't use dynamic SQL“ loudly).

When .NET procedural code uses the SqlServer provider to issue SQL statements, these are *dynamic* SQL to the engine. Ownership chains do not apply. I've had difficulty using EXECUTE AS with .NET code in betas, hoping that the new betas fix this. This mostly matters for procedures and triggers. You usually don't do data access in UDFs and user-defined aggregates don't have an EXECUTE AS clause at all; you shouldn't be doing data access in UDAggs anyway.

Separation of users and schemas is another cool SQL Server 2005 feature, but it has some interesting behaviors that folks may have to get used to. One is database object resolution, another is ownership chains. Say I have a user FRED.

FRED is the owner of a schema named FRED
FRED is a memeber of the role PAYROLL
FRED's default schema is PAYROLL

Even though FRED is the owner of a schema named after him, resolving a one-part object name like some_table uses a simple_algorithm: look in default schema first, then look in dbo schema. Even if you own another schema, only your default schema is used to resolve a 1-part name. BTW, the "sys" metadata schema complicates this a little bit, but I'm ignoring that for now. So for FRED, if the following tables exist:

fred.some_table
dbo.some_table
payroll.some_table

the statement "select * from some_table" executed by FRED, selects payroll.some_table. If payroll.some_table is dropped, it selects dbo.some_table. If FRED leaves the payroll department (and is removed from the role), it still selects dbo.some_table. Only when you do:

ALTER USER FRED WITH DEFAULT_SCHEMA = FRED

will it even attempt to resolve the 1-part name to fred.some_table.

I'd always wondered about how this affected ownership chains, too. A simplistic explanation of these is: authorization of a database object is only checked when an ownership chain is broken. So if procedure A uses table B, authorization is only checked if the owner of procedure A is different from the owner of table B.

So does user-schema separation change this? Is "ownership" defined as the user who owns the object or as the schema the object lives in? This is an easy one also...owner is still not object's owner, NOT the schema the object lives in.

This can have some interesting twists because you can GRANT other users the right to create objects in a schema you own:

GRANT CREATE TABLE TO ALICE
GRANT ALTER ON SCHEMA::FRED to ALICE

means ALICE can create tables in the FRED schema. But that's a subject for another day....

A few weeks ago, I was surprised by an error message when attempting to create an HTTP endpoint with CREATE ENDPOINT. The error was "You do not have permission to perform this operation". The reason I was surprised was that SQL Server 2005 was running in a domain environment and I was logged on as domain administrator at the time. This meant I had sa-level database privledges, and privileges on the OS as well. Hmmm...

The reason for this turned out to be pretty straightforward. I was running the SQL Server process as a relatively unprivileged account, principal of least privilege and all that. When you create an HTTP endpoint, SQL Server issues a "namespace reservation" for part of the HTTP namespace. The reservation is used when other applications (like IIS 6.0) use the HTTP.sys implementation at the same time. It attempts to issue the reservation *using the identity of the principal this is running the SQL Server process*, not as your currently logged on user.

The way to accomplish the reservation under these conditions is to use a system stored procedure, sp_reserve_http_namespace.
It looks like this:

sp_reserve_http_namespace N'http://mymachine.mydomain.com:80/mydir'

Note that in order for this procedure to work, you must be logged in to SQL Server as a Windows login that has OS admin privileges. And so I was, and it worked. So did CREATE ENDPOINT... FOR HTTP. However I noticed that, in my CREATE ENDPOINT DDL statement I had to use the exact machine domain name for the SITE operand, rather than the default ('*' which means "use all machine names not otherwise reserved"). Oh.

That had everything to do with my input to sp_reserve_http_namespace. Using:

sp_reserve_http_namespace N'http://*:80/mydir'

instead, set things up so that I could use '*' as a SITE operand. Whew.