Archive for June, 2008

TABLESAMPLE Gotcha (ah the stress)

I needed a few random rows from one of our tables with at least a million rows.
I used the following query:

SELECT
  myID,
  ROW_NUMBER() OVER (ORDER BY myID) AS RowNumber
FROM
  AD
TABLESAMPLE (25 ROWS)

This worked awesome when I was testing it on my local machine. However, as soon as I deployed it to a dev machine, it just decided to stop working. I knew TABLESAMPLE was pretty random – it can return 15 or 30 or 100 even if I request for 25 rows. I just didnt realize it was very unreliable.

I found this post from Erland Sommarskog:
http://www.issociate.de/board/post/450002/TABLESAMPLE_question.html
Quote:

I saw the suggestion to use TABLESAMPLE, and I was not very happy with it, but I did not reply to at the time. TABLESAMPLE is fairly
approxamite in its nature. I ran

SELECT * FROM Orders TABLESAMPLE (1 ROWS)
SELECT * FROM Orders TABLESAMPLE (10 ROWS)
SELECT * FROM Orders TABLESAMPLE (100 ROWS)

in an inflated version of Northwind with 344000 orders. The first two
selects returned no rows at all, the last returned 86 rows. The first
time. The second time it returned no rows, and the last time it return 132 rows. Furthermore, the sample was not entirely random, but I got a couple of sequences of order ids. Presumably because TABLESAMPLE works on page level.

Possibly you could combine newid() and TABLESAMPLE:

SELECT TOP 1 * FROM (
select * from Orders TABLESAMPLE (100 ROWS)) AS d
ORDER BY newid()

You would need to make your sample size big enough so that you are
sure that it retrieves a row each time, but the bigger you make it,
the bigger the cost for the sorting.

If your table is only some few thousand of rows, it’s not likely to
be worth the pain.


Erland Sommarskog, SQL Server MVP, esquel [at] sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

I will go with his suggestion – NOT to use TABLESAMPLE.

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Awesome reference on SQL Server dates

Robyn Page’s SQL Server DATE/TIME Workbench
http://www.simple-talk.com/sql/learn-sql-server/robyn-pages-sql-server-datetime-workbench/

 

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Fixing Orphaned Users

This is a follow up posting on

Issue: Server principal is not able to access the database under the current security context

Here is a script to loop through all of the users and re-attach them to their logins. Note that the assumption here is the logins in the old server/instance exists in the new server/instance

SQL Server 2000/2005 (using sp_change_user_login, which is marked for deprecation):

   1: DECLARE @UserName varchar(35)
   2:  
   3: DECLARE UserListCrs CURSOR FOR
   4: SELECT 
   5:     [name]
   6: FROM 
   7:     sysusers
   8: WHERE 
   9:     issqluser = 1
  10:     AND SUSER_SNAME(sid) IS NULL
  11:     AND [name] <> 'guest'
  12:     -- add additional exceptions here if needed
  13:  
  14: OPEN UserListCrs
  15:  
  16: FETCH NEXT FROM UserListCrs
  17: INTO @UserName
  18:  
  19: -- loop through all users, and remap the user to the login
  20: -- assumption: username is the same as loginname
  21: WHILE @@FETCH_STATUS = 0
  22: BEGIN
  23:     
  24:     EXEC sp_change_users_login 'update_one', @UserName, @UserName  
  25:    
  26:     FETCH NEXT FROM UserListCrs
  27:    INTO @UserName
  28: END     
  29:  
  30: CLOSE UserListCrs
  31: DEALLOCATE UserListCrs

SQL Server 2005 onwards (using ALTER LOGIN, replacement for sp_change_users_login):

   1:  
   2: DECLARE @UserName varchar(35)
   3: DECLARE @SQLString varchar(500)
   4:  
   5: SET @SQLString = ''
   6:  
   7: DECLARE UserListCrs CURSOR FOR
   8: SELECT 
   9:     [name]
  10: FROM 
  11:     sysusers
  12: WHERE 
  13:     issqluser = 1
  14:     AND SUSER_SNAME(sid) IS NULL
  15:     AND [name] <> 'guest'
  16:     -- add additional exceptions here if needed
  17:  
  18: OPEN UserListCrs
  19:  
  20: FETCH NEXT FROM UserListCrs
  21: INTO @UserName
  22:  
  23: -- loop through all users, and remap the user to the login
  24: -- assumption: username is the same as loginname
  25: WHILE @@FETCH_STATUS = 0
  26: BEGIN
  27:     
  28:     SET @SQLString = ' ALTER USER ' + @UserName +
  29:                          ' WITH LOGIN = ' + @UserName
  30:     
  31:     PRINT @SQLString
  32:     -- uncomment below when you are ready to execute
  33:     -- you may also want to rewrite this dynamic SQL using sp_executesql
  34:     -- EXEC(@SQLString)
  35:    
  36:     FETCH NEXT FROM UserListCrs
  37:    INTO @UserName
  38: END     
  39:  
  40: CLOSE UserListCrs
  41: DEALLOCATE UserListCrs
  42:  
  43: -- Sample Output
  44: -- ALTER USER belle WITH LOGIN = belle
  45:  

 

Here is a good reference:

Understanding and dealing with orphaned users in a SQL Server database

http://www.mssqltips.com/tip.asp?tip=1590

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

SQL Server operates on the concepts of Logins and DB Users.

A login (server level) can map to a single database user. The name of the db user can be the same, or can be different. A login is mapped to a user via an SID (security ID)

Often, when databases are moved around, this login-user mapping gets lost because a different login in a different server or instance will have a different SID. And this causes grief not just to users but also to DBAs.

Couple ways to fix this:

 

1. sp_change_users_login

This is marked for deprecation, but you can still use this.

3 options for sp_change_users_login:

Auto_Fix, database user is mapped with same named SQL Server login. It may also create login, if not present

Report, it lists the orphaned users and their security identifiers (SID)

Update_One, it links the specified database user to an existing SQL Server login

EXEC sp_change_users_login @Action=’Report’

EXEC sp_change_users_login  @Action =  ‘Update_One’ 
   UserNamePattern = ‘dbusername’  
    @LoginName = ‘loginname’ 

When there are a lot of users, can use a cursor.

 

2. ALTER LOGIN – better way or recommended way

ALTER USER dbusername WITH LOGIN ‘loginname’

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Issue:
We backed up and restored one database from one server to another. Sounds simple, right? Should be. This isn’t one of those days :)

So we get this issue:

The server principal “myuser” is not able to access the database “mydb” under the current security context.

Resolution:
Thanks to the following forums, we were able to troubleshoot. Steps follow:

1. In order to ‘Map’ the Login, the Login must not already be as User on the database.

Go to ServerName -> Databases -> DatabaseName -> Security -> Users

2. Delete the Login from the list of database Users before mapping the Login to the database.

3. In Object Explorer -> Server -> Security -> Logins, right click and choose Properties

4. The ‘User Mapping’ page should list all databases on the server with a check mark on the databases that the Login has been mapped to. 

Map the Login by checking the box next to the database name.

You may also need to add some fixed database roles, just to get your stuff working first:

ie, db_datareader and public. 

5. Click OK
6. Rejoice

References:
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1638146&SiteID=17

http://www.sqlservercentral.com/Forums/Topic362018-359-1.aspx

Follow Up Post:
Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Security Context

VN:F [1.9.22_1171]
Rating: 5.1/10 (7 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

In SQL Server 2005, we can execute batches multiple times using the following syntax following the GO delimiter

GO n

— Sample Script

   1: DECLARE @id int
   2:  
   3: SELECT @id = MAX(id)
   4: FROM #tmp 
   5:  
   6: INSERT INTO #tmp
   7: VALUES (@id + 1)
   8:  
   9: SELECT MAX(id)
  10: FROM #tmp
  11:  
  12: GO 5

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

List all ASCII characters

   1: -- ---------------------------------------------------------------------
   2: -- displays ASCII characters, and identify which ones are printable
   3: -- ---------------------------------------------------------------------
   4: DECLARE @min int, @max int
   5: DECLARE @chars TABLE
   6: ( 
   7:     ASCIIValue       int,
   8:     ASCIIString      nvarchar(3),
   9:     IsPrintable      bit 
  10: )
  11: SET @min = 0
  12: SET @max = 300
  13:  
  14: -- Codes 33 to 126 are printable characters
  15: -- http://en.wikipedia.org/wiki/ASCII
  16: WHILE @min <= @max
  17: BEGIN
  18:     INSERT INTO @chars
  19:     SELECT 
  20:         @min, 
  21:         CHAR(@min),
  22:         CASE 
  23:             WHEN @min BETWEEN 33 AND 126 THEN 1
  24:             ELSE 0
  25:         END
  26:     SET @min = @min+1
  27: END
  28:  
  29: SELECT 
  30:     * 
  31: FROM 
  32:     @chars
  33: WHERE 
  34:     ASCIIString IS NOT NULL
  35:  
  36:  
  37:  

VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

DBAs are humans too …

Kalen Delaney’s post on DBA blunders. Not really for the faint of heart

http://sqlblog.com/blogs/kalen_delaney/archive/2008/05/15/dba-blunders.aspx

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
`