An implied (not implying) permissions function

I’d always wondered why they didn’t make the “ImplyingPermissions” function (code is part of the books online) part of the product. I originally thought it was because the function was subtly (or not so subtly) misnamed. What the function does, is “given a permission in a permission class, return a table of which built-in permissions […]

I’m speaking on SQL Server service accounts next week

Just to let you know. The Portland (Oregon) SQL Server User Group has invited me to speak at the meeting on Thursday, Jan 24 at 6:30pm. I’ll be speaking on SQL Server service accounts, entitled “Who’s running my SQL Server services?”. I’ll be covering the evolution of service account choices and management¬†in later versions of […]

Security Best Practices Whitepaper for SQL Server 2012 is Live.

I recently completed another update to the whitepaper, now entitled "SQL Server 2012 Security Best Practices – Operational and Administrative Tasks" to cover best usage of the security features in SQL Server 2012. And, concurrently with the launch of SQL Server 2012, it was available on the web a few days ago. Many thanks to the […]

Auditing database-level objects in SQL Express 2012

… Continued from previous blog entry …  The point of using a Server Audit Specification with database events in SQL Server 2012 is this. In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. In SQL Server 2012, one of the new features (to quote BOL) is: "Support […]

Using filtering and server audit specs to audit DB objects in SQL Server 2012

I've always been pretty "standard" in my approach to SQL Server's auditing feature. That is, Server Audit Specifications are for auditing server-level objects and Database Audit Specifications are for auditing database-level objects. There have always been a few "Audit Action Groups" that pertain to database objects that could be specified in Server Audit Specifications. An […]

Local Windows Groups for Service Accounts (almost) gone in SQL Server 2012

When I installed CTP3 of SQL Server 2012 (on Windows Server 2008 R2 OS), I noticed that the "Service SID account" (known as the Managed Service Account) was directly available in the setup dropdown box, selected it, wrote a blog entry mentioning it, and went on. Lately, I've been looking at the local Windows groups (or […]

A row-level and label security offering for SQL Server

Two of the security features that folks have asked me about, especially since around 2002, is row-level and label security. Row-level security was present in one of the early betas (beta 1, IIRC) of SQL Server 2005, but it was pulled almost right after that beta release, never to return. Row-level security is a self-descriptive […]

Some idiosyncrasies in SQL Server service and service user group names

In doing research for the Service SID posting, I encountered some odd things about service names, service user group names and services. I said I'd write about these; this is the followup post. Most of the information about Windows Services, Service Accounts, and user groups that are created for SQL Server services (for security purposes) […]

About SQL Server’s usage of Service SIDs

A couple of weeks ago at DevDays Netherlands, I struggled a bit with a demo that was relatively straightforward in SQL Server 2008 running under Windows Server 2003 R2, but has an interesting twist in SQL Server running under Windows Server 2008. The interesting twist is based around the fact that SQL Server 2008 and above use […]

SQL Server Security Best Practices whitepaper update is live

I've recently completed an update to the SQL Server 2005 Security Best Practices whitepaper. It's available on the security and compliance website under whitepapers or the direct link is here. Unsurprisingly, its called "SQL Server 2008 R2 Security Best Practices – Operational and Administrative Tasks". Thanks to all the folks who reviewed it. Enjoy. @bobbeauch

SQL Server security precon at TechEd 2010.

This blog posting is meant to bring attention to the fact that I'm doing a preconference talk, "A Day of SQL Server Security" at TechEd 2010 in New Orleans in June. OK, the TechEd folks asked me to publicize it. I'm also doing two breakout sessions, one on "Entity Framework and LINQ2SQL vs. Stored Procedures", […]

At the user group…auditing and sys.fn_get_audit_file

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 […]

At the Portland SQL Server User Group this month

This month I'll be presenting a session for the Portland SQL Server User Group. I'll be discussing and demonstrating the new security features in SQL Server 2008 with a post-talk Q&A about SQL Server security in general. I've also got some swag to raffle off. See you on the fourth Thursday!

SQL Server Compliance Portal is now live

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 […]

My SQL Server security best practices whitepaper is available

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!

SQL Server service accounts and privileges

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 […]

Technet Webcast Series For the ITPro – Part 2

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 […]

What does EXECUTE on an XML SCHEMA COLLECTION allow?

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 […]

Ownership chains and .NET procedure data access code

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 […]

Service broker and database master keys

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 […]

Security in SQL Server 2005 – unsafe assemblies in Sept CTP

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 […]

Security in SQL Server 2005 – “non-traditional” logins and users

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 […]

Security in SQL Server 2005 – execute as…with cookie

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 […]

Security in SQL Server 2005 – unsetapprole

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 […]

Security in SQL Server 2005 – Encryption

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 […]

Security in SQL Server 2005 – Logins

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 […]

Why my cat can’t use a SQLCLR proc to read files

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, […]

Changing SQL Login password with the utilities

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 […]

How DO you change your SQL Login password?

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 […]

How to make a DBA smile

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 […]

Password policies on object passwords

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 […]

How do I see all the tables again?

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 […]

Schemas, Users, and Objects – III

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 […]

Schemas, Users, and Objects – II

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 […]

More on ownership chains

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 […]

Users, Schemas, Objects, and Owners

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 […]

Least privilege and HTTP endpoints

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 […]