Who Has db_owner Rights?

I wanted to see who in my database has db_owner rights. Here is the script that worked:

SELECT m.name
FROM sys.database_role_members AS rm
JOIN sys.database_principals AS p
  ON rm.role_principal_id = p.principal_id
JOIN sys.database_principals AS m
  ON rm.member_principal_id = m.principal_id
WHERE p.name = N'db_owner'
  AND m.name NOT IN (N'dbo', N'SQLServiceAct')
ORDER BY m.name

---

name
-----------
[result1]
[result2]

Based on a script pulled from an entry here by "The Scary DBA".

Using JOIN, NOLOCK, and Table (Index) Hints Together

Using JOIN, NOLOCK, or table hints by themselves is straightforward, but what if you need to use them all at once? What's the order?

I tried and failed with a number of different permutations. I finally figured out that the NOLOCK has an implied WITH*. That brought me to the solution.

SELECT ...
FROM xx AS x WITH (NOLOCK)
JOIN yy AS y WITH (NOLOCK, INDEX (yIdx))
ON x.col = y.col

* I always write NOLOCKs using the the WITH and "()", but I didn't write this query originally.

Here is an example that you can run:

IF OBJECT_ID('tempdb..#t1') IS NOT NULL
DROP TABLE #t1
IF OBJECT_ID('tempdb..#t2') IS NOT NULL
DROP TABLE #t2
GO
CREATE TABLE #t1 (ColID int IDENTITY, Col2 int)
CREATE TABLE #t2 (ColID int IDENTITY, Col2 int)
GO
CREATE INDEX t1Col2 ON #t2 (Col2)
GO
INSERT INTO #t1 (Col2) VALUES (1)
INSERT INTO #t1 (Col2) VALUES (2)
INSERT INTO #t2 (Col2) VALUES (3)
INSERT INTO #t2 (Col2) VALUES (4)
GO
SELECT t1.Col2, t2.Col2
FROM #t1 AS t1 WITH (NOLOCK)
JOIN #t2 AS t2 WITH (NOLOCK, INDEX (t1Col2))
ON t1.ColID = t2.ColID

---

Col2 Col2
---- ----
   1    3
   2    4

SSMS 2008 Uncomment Bug

In SQL Server Management Studio 2008, there is a bug in the way it uncomments lines of code.

If you start with this code:

--Code line 1
--Code line 2
--Code line 3
--Code line 4
Code line 5

and want to uncomment lines 1, 2, and 3, you would select the first three lines and click [ctl]K/U.

The problem is that in SSMS 2008, if the line immediately after the line or block you are trying to uncomment is also commented, that line will be uncommented.

I know that's hard to follow, but after selecting lines 1, 2, and 3 to uncomment, you will end up with lines 1, 2, 3, and 4 uncommented.

Instead of this:

Code line 1
Code line 2
Code line 3
--Code line 4
Code line 5

You'll see this:

Code line 1
Code line 2
Code line 3
Code line 4 -- whoops
Code line 5

I entered this issue at Microsoft Connect in February. They logged it as bug ID 418596.

Today, they replied and wrote that they would fix it (someday). This is great news.

Hello rgarrison, Thank you for reporting this problem. We understand the usability issue wrt uncommenting and will fix this issue in a future release. Thanks, -Karthik

Speaking at PASS Summit 2009

I received word over the weekend (in the middle of moving) that one of my abstracts had been accepted for PASS.

• Accepted: Working in Management Will Rot Your Brain - How to Stay Technical and Advance Your Career
• Alternate: Exploring Filtered Indexes: How They Work, When They are Used

I am very excited to be able to speak there. Now I just have to do all the work to get that talk ready.

CodeCamp and SQL Saturday

I will be speaking tomorrow at CodeCamp. My session is "Exploring SQL Server Column-Level Encryption".

Next Saturday I'll be speaking at SQL Saturday #12. I'll have two sessions there: "Understanding SQL Injection (DB Devs and Admins) and "Perf Testing 2008’s Transparent Data Encryption".

Should be fun. I'm still waiting to hear anything from PASS about speaking there. So far there have been zero updates to the speaker's resource page besides saying that the "call for speakers" has closed.

PASS 2009's "Call for Speakers"

Well, PASS's 2009 Summit "Call for Speakers" deadline got moved out two weeks, so I had a bit more time to get everything ready. I submitted four abstracts tonight. They are posted here.

Now ... I wait.

Using REPLICATE to Build Long Strings

REPLICATE is a very useful tool, but there are limitations to its "out of the box" functionality.

Problem
This code shows the problem:

SELECT LEN(REPLICATE('X',  1000))
SELECT LEN(REPLICATE('X',  5000))
SELECT LEN(REPLICATE('X', 10000))

The result is:

----
1000

----
5000

----
8000

REPLICATE will not, by default, create a string longer than 8,000 characters. Here is the test that shows that the longest string is exactly 8,000 characters:

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE('X', @ct)

  IF len(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', len(@s1) AS 'Len @s1'
    BREAK
  END
END

---

@ct   Len @s1
----  -------
8001     8000

Fix
How can you make it build a longer string? Use CAST.

DECLARE @ct  int
DECLARE @s1  varchar(max)

SET @ct = 0
WHILE @ct < 10000 BEGIN
  SET @ct = @ct + 1
  SET @s1 = REPLICATE(CAST('X' AS varchar(max)), @ct)

  IF LEN(@s1) <> @ct BEGIN
    SELECT @ct AS '@ct', LEN(@s1) AS 'LEN @s1'
    BREAK
  END
END
SELECT LEN(@s1) AS 'Final String Length'

---

Final String Length
-------------------
              10000