Archive for June 12th, 2008

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)
`