SBX - Search With Button

SBX - Forum Post Title

SQL SERVER 916 ERROR

Microsoft Dynamics SL Forum

Lisa asked a question on 5 Jul 2018 7:18 PM
My Badges

Question Status

Verified

Hi

I have been trying to resolve an issue where on accessing a customized Purchase Order maintenance screen I am getting a SQL SERVER 916 error message which states:  " The server principal domain\administrator is not able to access the database XXXXXX under the current security context".

A Sql Profile trace was done and it is pointing to part in the code where the table UserRec from the system database is being reference. I viewed other sites that reference this issue but none of the suggestions so far was able to assist.  Has anyone here encountered this issue before and is willing to share how to fix it?   I will be very much appreciative.  Thanks in advance.

The code that I have on the screen that the trace is pointing to is as follows:

bxUserRec.UserInPOApprovalGroup = False

 SqlStr = "SELECT UserID, UserName,WindowsUserAcct,emailaddress,CustomGroup,User3,User4 "

SqlStr = SqlStr + " FROM  [PurSys].[dbo].[UserRec] "

SqlStr = SqlStr + "  WHERE rtrim(ltrim(UserID)) = '" + P_UserID + "'"

 serr1 = SqlFetch1(c1, SqlStr, bxUserRec, LenB(bxUserRec))

 If serr1 = NOTFOUND Then 'Initialize object

   bxUserRec.UserID = ""

   bxUserRec.UserName = ""

   bxUserRec.WindowsUserAccount = ""

   bxUserRec.emailaddress = ""

   bxUserRec.CustomGroup = ""

   bxUserRec.User3 = 0

   bxUserRec.User4 = 0

   bxUserRec.UserInPOApprovalGroup = False

Else ' Record Found

   If Trim(bxUserRec.CustomGroup) <> xPoAppr_glbPOApprovalGroup Then

      bxUserRec.UserInPOApprovalGroup = False

   Else

      bxUserRec.UserInPOApprovalGroup = True

   End If

End If

 Call SqlFree(c1)

 End Sub

Reply

Hi,

Below are the steps to resolve the issue

  1. Go to SSMS
  2. Drill down the view folder and find the view (vs_userrec) under Application DB (SLDemoApp60)
  3. Right click the view (vs_userrec) -> Click properties -> Click the permissions tab
  4. Click Search button -> Enter the object name to select as “public” and Click ok
  5. Select the user role “Public” and check the select permission below screen shot for your reference

       

Thanks,

Ajit kannan S R

Reply
Lisa responded on 6 Jul 2018 9:13 AM
My Badges

Hi Ajit,

Thank you so much for the above.  I tried it and it worked!!!  

However, I was wondering is there something peculiar in using the userrec table in a customization that you have to give particular permissions to ?

Reply
Erich Strelow responded on 6 Jul 2018 10:03 AM
My Badges

SqlFetch1 defaults to an APP database connection context. In the windows authenticated SL, a sql statement can't and isn't supposed to jump in between databases.

If you are going to query the SYS database, you should start by a:

Call SqlCursor(c1, SqlSystemDb )


Reply
Lisa responded on 6 Jul 2018 10:07 AM
My Badges

Thanks, but the funny thing about this issue is that some users on accessing the purchase maintenance screen got in without a problem while others were having problems.

However I will take that into consideration in the future.

Reply
Lisa responded on 6 Jul 2018 1:14 PM
My Badges

Ajit,

Unfortunately, after trying the first fixed, I checked just one user and it worked, but when I checked another user, they are still getting the SQL Message 916.  

Any other suggestion?

Erich, I will also adjust the code to see if that help.  From the code above, where will you suggest that I put the Call sqlCursor code?

Thanks for both of your assistance.

Reply
Erich Strelow responded on 9 Jul 2018 8:36 AM
My Badges
Verified Answer

The "by the book" approach is to issue the SqlCursor during the Form_Display, but you can just do it right before your code.

Note that users with the sysadmin will be able to jump between databases and for them there's no problem at all.

I know this because the same happens if you try to call some of the [msdb].[sp_*] procedures and one of my consultant's solution was to give sysadmin role to anything that moves.

Reply
Lisa responded on 13 Jul 2018 3:52 PM
My Badges

Thanks much Erich!!

Reply
Erich Strelow responded on 9 Jul 2018 8:36 AM
My Badges
Verified Answer

The "by the book" approach is to issue the SqlCursor during the Form_Display, but you can just do it right before your code.

Note that users with the sysadmin will be able to jump between databases and for them there's no problem at all.

I know this because the same happens if you try to call some of the [msdb].[sp_*] procedures and one of my consultant's solution was to give sysadmin role to anything that moves.

Reply

SBX - Two Col Forum

SBX - Migrated JS