web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Please help...SQL Serv...
Microsoft Dynamics SL (Archived)

Please help...SQL Server Message 916 - Permission Issue

(0) ShareShare
ReportReport
Posted on by FATSN00 47

Hi

Can someone please assist me with the following error I am getting in sql:
SQL Server Message 916 - The server principal "domain\username" is not able to access the database "DBNAME" under the current security context. 

I am trying to select user credentials from my SYS db and usergrp table from out off my APP db..

For example:

SELECT * FROM PROCSYS..usergrp where GroupID='ProjectStatus1' and UserID= 'username' 


I have granted select permission everywhere I can think of for the user in question but I still receive the same error when trying to execute my script from the dynamics screen.


Can someone please advise me on this?

Thank you in advance

*This post is locked for comments

I have the same question (0)
  • Brian_IL Profile Picture
    Brian_IL 719 on at

    The initial troubleshooting step is to run the Synchronize Security scenario from the SL DB Maintenance program.

    If that doesn't resolve the permissions error, try turning on detailed event logging in SL or running a SQL Profiler trace while running your report or SL program in another window. Look through the log file created to find where the error is occurring. You might try running the SQL statement reporting an error in a separate SSMS window.

    I had one instance where the error reported a problem with one stored procedure. I looked at the SQL expected by the procedure and found it was attempting to select from a System DB view that had somehow been dropped when updating to a new SL Service Pack. Recreating the missing view using the syntax from another working database resolved the issue.

    Also, your SQL statement doesn't contain any reference to an Application DB table. Is there another part of the command that is causing this error?

  • RayD Profile Picture
    RayD 239 on at

    Dynamics connects to db using application security, did you give permissions to the Application user or to the windows user

    RayD

  • Coe-Ane Erasmus Profile Picture
    Coe-Ane Erasmus 455 on at

    Hi, thanks for the responses. 

    I am working with Lee'Roy on this issue for a client - we actually have this set of code on the project maintenance screen - it is supposed to extract the logged in user details so that we can use it as part of field level enable/ disable customization.  It does not reference the system database in the code, however it references the system database as part of the error message.  We have run synchronize ownership multiple times as well as updating views. 

    example of code:

    Dim strSQL As String

    SecurityVal1 = False
    SecurityVal2 = False

    strSQL = "SELECT COUNT(*) FROM vs_usergrp where GroupID='ProjectStatus1' and UserID=" & SParm(parUserID)
    Call SqlFetch1(c2, strSQL, bUserSec, LenB(bUserSec))
    If bUserSec.Cnt > 0 Then
    SecurityVal1 = True
    End If

    strSQL = "SELECT COUNT(*) FROM vs_usergrp where GroupID='ProjectStatus2' and UserID=" & SParm(parUserID)
    Call SqlFetch1(c3, strSQL, bUserSec, LenB(bUserSec))
    If bUserSec.Cnt > 0 Then
    SecurityVal2 = True
    End If
    End Sub

  • Coe-Ane Erasmus Profile Picture
    Coe-Ane Erasmus 455 on at

    to further comment... I have run SQL profiler and it did not highlight anything other than the user (active directory account linked to the SL user) trying to run the statement above. (SELECT COUNT(*) FROM vs_usergrp where GroupID='ProjectStatus1' and UserID=).  Access has been given in SQl to the Active Directory account linked to the test user. Access to the dbs have also been granted to the "07xx" and "E8xxx" accounts. I have tried making the test user and those users sysadmin on SQL just to test and make sure.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics SL (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans