SQL SERVER 916 ERROR

(0) Share

Report

Posted on by Cynthia Audain

1,348

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

Call SqlFree(c1)

End Sub

*This post is locked for comments

I have the same question (0)

All responses (7)

Answers (1)

Ajit Kannan 1,246 on at

Like (0)

Report
Hi,

Below are the steps to resolve the issue

Go to SSMS

Drill down the view folder and find the view (vs_userrec) under Application DB (SLDemoApp60)

Right click the view (vs_userrec) -> Click properties -> Click the permissions tab

Click Search button -> Enter the object name to select as “public” and Click ok

Select the user role “Public” and check the select permission below screen shot for your reference

Thanks,

Ajit kannan S R

Was this reply helpful? Yes No
Cynthia Audain 1,348 on at

Like (0)

Report

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 ?

Was this reply helpful? Yes No
Erich Strelow F 16 on at

Like (0)

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

Was this reply helpful? Yes No
Cynthia Audain 1,348 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cynthia Audain 1,348 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

Erich Strelow F 16 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Cynthia Audain 1,348 on at

Like (0)

Report

Thanks much Erich!!

Was this reply helpful? Yes No