Month: June 2008

TABLESAMPLE Gotcha (ah the stress)

belleLeave a comment

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.

Continue reading →

Fixing Orphaned Users

belleLeave a comment

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

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

belle2 Comments

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’

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

belle6 Comments

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

Executing Batches Multiple Times (SQL Server 2005 only)

belleLeave a comment

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

List all ASCII characters

belleLeave a comment 
   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: