Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics SL forum
Suggested answer

Server Role/permissions Question

Posted on by 1,615

Hi there,

I have built a query into VBA in the customisation of the Journal Transaction screen.  I need to read a field in UserRec table in the system database.  What server role / permissions do I need to give the user/group as all I need to do is read the table so don't want to assign more control than i need to the user.  If i use Sysadmin Server role it works but that is giving the user too much control.  If I don't assign sysadmin, I get the error "The server principal xxxuser is not able to access the database SLSYSTEM under the current security context"

Does anyone have any suggestions?

  • Jo Wykerd Profile Picture
    Jo Wykerd 1,615 on at
    RE: Server Role/permissions Question

    It seems like adding anything in the VBA code or a stored proc doesn't seem to work.

    I am happy to change the users server roles.  What server role can i assign to the users in SQL so that they can only select/read data from SLSYSTEM database?  At the moment they only have Public which isn't doesn't give them any access.

  • Erich Strelow F Profile Picture
    Erich Strelow F 1,642 on at
    RE: Server Role/permissions Question

    Doing a query to another database is not well supported under SL using integrated authentication. There are tweaks with respect to the SYS database, like the E8F575915A2E4897A517779C0DD7CE and 07718158D19D4f5f9D23B55DBF5DF1 users.

    There is a vs_userrec created under the APP database, but I guess restrictions still apply.

    What I've done in the past is putting all inside a stored procedure issuing a WITH EXECUTE AS '07718158D19D4f5f9D23B55DBF5DF1'

  • Jo Wykerd Profile Picture
    Jo Wykerd 1,615 on at
    RE: Server Role/permissions Question

    Thanks for your reply Mark.

    I added the above code to the View I call from the VBA code but still get the same error.

    See view below:-

    CREATE VIEW [dbo].[IMMIX_userrec] AS

    SELECT * FROM SLSYSTEM..userrec

    GO

    GRANT SELECT ON [dbo].[IMMIX_userrec] TO [E8F575915A2E4897A517779C0DD7CE]

    GO

    GRANT CONTROL ON [dbo].[IMMIX_userrec] TO [MSDSL]

  • Suggested answer
    Mark E Profile Picture
    Mark E 6,405 on at
    RE: Server Role/permissions Question

    Assuming you are working with SL 2011 add these commands to the end of your views:

    GO

    GRANT SELECT ON [dbo].[view nameTO [E8F575915A2E4897A517779C0DD7CE]

    GO

    GRANT CONTROL ON [dbo].[view name] TO [MSDSL]

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

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans