I needed a few random rows from one of our tables with at least a million rows.
I used the following query:
ROW_NUMBER() OVER (ORDER BY myID) AS RowNumber
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.
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)
3: DECLARE UserListCrs CURSOR FOR
9: issqluser = 1
10: AND SUSER_SNAME(sid) IS NULL
11: AND [name] <> 'guest'
12: -- add additional exceptions here if needed
14: OPEN UserListCrs
16: FETCH NEXT FROM UserListCrs
17: INTO @UserName
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
24: EXEC sp_change_users_login 'update_one', @UserName, @UserName
26: FETCH NEXT FROM UserListCrs
27: INTO @UserName
30: CLOSE UserListCrs
31: DEALLOCATE UserListCrs
SQL Server 2005 onwards (using ALTER LOGIN, replacement for sp_change_users_login):
2: DECLARE @UserName varchar(35)
3: DECLARE @SQLString varchar(500)
5: SET @SQLString = ''
7: DECLARE UserListCrs CURSOR FOR
13: issqluser = 1
14: AND SUSER_SNAME(sid) IS NULL
15: AND [name] <> 'guest'
16: -- add additional exceptions here if needed
18: OPEN UserListCrs
20: FETCH NEXT FROM UserListCrs
21: INTO @UserName
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
28: SET @SQLString = ' ALTER USER ' + @UserName +
29: ' WITH LOGIN = ' + @UserName
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)
36: FETCH NEXT FROM UserListCrs
37: INTO @UserName
40: CLOSE UserListCrs
41: DEALLOCATE UserListCrs
43: -- Sample Output
44: -- ALTER USER belle WITH LOGIN = belle
Here is a good reference:
Understanding and dealing with orphaned users in a SQL Server database
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:
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’
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.
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
Follow Up Post:
Follow up on Issue: SQL Server Principal Is Not Able to Access the Database Under the Current Security Context
In SQL Server 2005, we can execute batches multiple times using the following syntax following the GO delimiter
— Sample Script
1: DECLARE @id int
3: SELECT @id = MAX(id)
4: FROM #tmp
6: INSERT INTO #tmp
7: VALUES (@id + 1)
9: SELECT MAX(id)
10: FROM #tmp
12: GO 5