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

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Finance | Project Operations, Human Resources, ...
Suggested Answer

Add SL User w/o ADMINISTRATORS Group

(0) ShareShare
ReportReport
Posted on by 6,451

SL 2015 CU2, Windows Authentication.  Client wants IT Dept to create new SL Users, but not be part of the ADMINISTRATORS Group.  Auditors do not want IT Dept to have access to Financial modules.  Created an ADMIN group with rights to only System Manager screens, including User Maintenance (95.260.00).  User was also assigned sysadmin SQL role directly in SQL Server.

When IT user attempts to create a new SL User, the message received is below.  How can we set up the ability for IT to add new SL Users w/out granting ADMINISTRATOR group?

pastedimage1573520055299v1.png

I have the same question (0)
  • Suggested answer
    CFROTON Profile Picture
    4,710 on at

    Hello,

    Have you tried running synchronize ownership and security in the SL database maintenance screen?

    Best Regards,

    Jana Macdonald

  • Mark E Profile Picture
    6,451 on at

    This has been done in the past with no effect on the results. I have looked at what SQL privileges are assigned to the user within SQL, but cannot see major differences.  Only a few QQ views are granted to users assigned to the ADMINISTRATORS group.  Using SQL Profiler, I can see where the errors occur, but am unable to locate where to make the changes in SQL.

  • Mark E Profile Picture
    6,451 on at

    I also tried this on a different environment with the similar results.  The user is granted rights to the User Maintenance screen in SL, and also granted sysadmin server role privileges in SQL, with .  When creating new SL Users, the first message when saving is:

    pastedimage1573643321492v1.png

    The only way I am finding around this is to add the User to the ADMINISTRATORS Group in SL, then manually delete the record from the UserGrp table in SQL.  So, looking for help in how to mimic the additional SQL privileges that are granted within SQL when the User is added to the ADMINISTRATORS Group.

  • Suggested answer
    CFROTON Profile Picture
    4,710 on at

    Hello

    I created a user ITADMIN, created a Group ITADMIN, assigned only the Administrator module to that group (9xxx screens)

    Created a local user in windows, created that user in SL, Assigned to DemoRole1

    Saved, I got an error , but it saved, and now I go in and I can add groups to my new users and existing users with no errors...

    This user only "role" in sql server is the MSDynamicsSL role, this role is assigned as DBO

    You will probably need to create a case in support and have one of the environment specialist look at your problem

    Thank you,

    Jana MacDonald

  • Erich Strelow F Profile Picture
    16 on at

    Sorry to say it's very unlikely for this to work.

    The SP_SETAPPROLE is a cornerstone in the Dynamics SL security design. Every time the SL client launch, a call to sp_setapprole would be issued early on, except for those within the ADMINISTRATORS group. Since you already granted SQL's SYSADMIN role, in your case there's no need for the sp_setapprole call, but the SL client doesn't know this. And I bet the ADMINISTRATORS waiver is hard coded.

    Since SQL's Application Roles are database-confined, it make sense that changing databases won't be allowed after the sp_setapprole.

    Yo may try to build a set of scripts to create and assign users. If you use integrated security, you can build a set of powershell script that combines Active Directory provisioning with SL user creation. Probably, the auditors will be appalled to learn you can script the hell out of SL.

    I work in a stock-listed company myself. The auditing firm probably seeks a separation between business-owner and system-owner. Dynamics SL just doesn't come with that. What we do is scan all accounting records looking for the crtd_user and go hunting TI profiles.

  • CFROTON Profile Picture
    4,710 on at

    Hello,

    I did get another suggestion from our Environment group

    As far as I know adding the IT user to the SQL SYSADMIN role in SQL  usually allows the needed permissions, but without looking at how they have things configured it is hard to tell. I would suggest they add this IT user to the SQL securityadmin role. From the looks of the error the IT user is still not setup to have the level of SQL rights as he needs. If that still does not give him what he can do is to select all SQL roles for this IT user and test adding a user. If it works start peeling off permissions till it works.

    Best Regards,

    Jana MacDonald

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…

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 592 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 478 Super User 2025 Season 2

#3
BillurSamdancioglu Profile Picture

BillurSamdancioglu 305 Most Valuable Professional

Last 30 days Overall leaderboard

Product updates

Dynamics 365 release plans