Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL (Archived) / Server Role/permission...
Microsoft Dynamics SL (Archived)

Server Role/permissions Question

(0) ShareShare
ReportReport
Posted on by Jo Wykerd 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?

*This post is locked for comments

  • 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 name] TO [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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans