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 GP (Archived) / Excel report permissions
Microsoft Dynamics GP (Archived)

Excel report permissions

(0) ShareShare
ReportReport
Posted on by Chuck Evans 1,175

Are there any user friendly tools to add windows logins to SQL and windows users to rpt roles. Doing this in SQL Management Studio is a little cumberson with a large number of companies and users.

Thanks
Chuck

*This post is locked for comments

I have the same question (0)
  • Verified answer
    Community Member Profile Picture
    Community Member on at

    The best way I've found is to have groups already defined in Active Directory and grant rights to those groups in SQL.  When a new windows user is added to those groups they automatically get the appropriate permissions in SQL.

  • GPDavid Profile Picture
    GPDavid 2,250 on at

    Hi Chuck,

    One thing that can potentially speed this up for you is to consider creating Windows groups for various groups of report users.  You can then assign your users to these groups, and, in turn, assign these groups to the appropriate reporting roles.  You still have to work through the SQL Management Studio windows, but you will, in theory, have far fewer groups to add to the roles than you would users.

    Additionally, have you looked into using T-SQL to accomplish what you need?  For example, you could create a list in Microsoft Excel and, in the first two columns, list the user name and the report role to which they should be assigned.  Then, in the third column, use Excel concatenation to "build out" a SQL query using the sp_addrolemember query to look something like this:

    sp_addrolemember 'rpt_accounting manager',':: user or group goes here ::'

    msdn.microsoft.com/.../ms187750(v=sql.105).aspx

    Autofill this down for every row you have created listing each user/group assignment.  This will allow you to rapidly create a set of T-SQL queries that can then be executed against a particular GP database to grant this role assignment.

    Please test this out in a test environment before utilizing on Production.

    David

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 GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans