Smartlist cannot query data
Hello,
I was able to create a smartlist and make the smartlist available to non-poweruser roles. However, I can only successfully query when using an account with poweruser credentials. If a non-poweruser click on the smartlist and tries to query then nothing is returned.
I am guessing sql is rejecting the query to non-poweruser roles. I could be wrong.
Please provide a detailed answer for your solution because I am very new to GP. For example, telling me to go configure permission in a place is not enough. I need to know how to get there.
Thanks
I was able to create a smartlist and make the smartlist available to non-poweruser roles. However, I can only successfully query when using an account with poweruser credentials. If a non-poweruser click on the smartlist and tries to query then nothing is returned.
I am guessing sql is rejecting the query to non-poweruser roles. I could be wrong.
Please provide a detailed answer for your solution because I am very new to GP. For example, telling me to go configure permission in a place is not enough. I need to know how to get there.
Thanks
Abdulmalek_Hamsho
Are you talking about SmartList Builder, or the normal SmartList?
ASKER
Hi Abdulmalek_Hamsho, I was referring to the normal Smarlist. I already created it via the Smartlist Builder and set all the required security task, roles and sql security via the Smartlist Builder but non-poweruser cannot query using the Smartlist I created.
First of all, you're going to face issues when running Dynamics GP in restricted user mode, it's advised to give Power User privileges to the users running Dynamics GP.
My question now is: Are the rest of the smartlists showing correctly except the one you've created in SmartList Builder?
My question now is: Are the rest of the smartlists showing correctly except the one you've created in SmartList Builder?
ASKER
I understand and I do expect some issues when running Dynamic GP in restricted mode but giving everyone power user privileges is out of the question. We are just not comfortable in doing that with any ERP system.
There are other Smartlists (default smartlists) that are working properly for non-poweruser accounts.
There are other Smartlists (default smartlists) that are working properly for non-poweruser accounts.
Let me clarify this: You're not willing to give your users the "Power User" privileges in Windows OS, not in GP, am I correct?
ASKER
I am strictly talking about MS GP Power User privileges, all users already has power user privileges in Windows. All security settings I am referring to here refers to MS Dynamic GP 2010.
FYI, I was referring to Power User of Windows OS. You definitely cannot give your users the Power User role.
Back to your question: Could you try running Grant.sql in your SQL server (located in: C:\Program Files\Microsoft Dynamics\GP\SQL\Util\Grant
Back to your question: Could you try running Grant.sql in your SQL server (located in: C:\Program Files\Microsoft Dynamics\GP\SQL\Util\Grant
ASKER
I am not sure on how to run this from the folder. Will it change any permission settings or just show what's applied. I double clicked it and signed on to SQL. Shoud I highlight all and execute?
If when you double-click it, SQL Management Studio opens it then, highlight it all and execute it.
This code will ensure that GP users (DYNGRP) have the required access. Don't worry, this code must be executed whenever views, tables, or SP are added.
This code will ensure that GP users (DYNGRP) have the required access. Don't worry, this code must be executed whenever views, tables, or SP are added.
ASKER
I get "Msg 15151, Level 16, State 1, Line 1
Cannot find the user 'DYNGRP', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1"
Our GP database is called DYNAMICS, I assume that's the db you want to run this query against. Is that right?
Cannot find the user 'DYNGRP', because it does not exist or you do not have permission.
Msg 15151, Level 16, State 1, Line 1"
Our GP database is called DYNAMICS, I assume that's the db you want to run this query against. Is that right?
You need to run grant.sql against DYNAMICS and all the companies DBs.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Abdulmalek_Hamsho, I appreciate your helps and I will try to do as you suggested tomorrow at work. I would like to make a backup of the server first and get back to you. I assume under your instruction that once the utility is executed it will then apply the appropriate SQL permissions to GP users and I should be able to run the smartlist query successfully for regular users. Is there anything else you want me to try?
Nothing comes in to my mind as of now. grant.sql is very important at this stage.
Wayne,
Is the SmartList you created using a SQL view or table? If so, then the issue may be 2-fold:
1. you may not have given appropriate rights to the SQL view or table (in SQL)
2. you may not have enabled users to see SmartLists based on SQL tables/views (in GP)
Before we go down the road of instructions on fixing this, can you confirm that your SmartList is based on SQL views or tables. If you're not sure, you can enclose a screenshot of your SmartList (Tools | SmartList Builder | SmartList Builder, select your SmartList.) Below is what an example screenshot of a SmartList based on a SQL view would look like.
Also, can you confirm that you're using GP 2010? The permissions in GP vary depending on GP version.
Is the SmartList you created using a SQL view or table? If so, then the issue may be 2-fold:
1. you may not have given appropriate rights to the SQL view or table (in SQL)
2. you may not have enabled users to see SmartLists based on SQL tables/views (in GP)
Before we go down the road of instructions on fixing this, can you confirm that your SmartList is based on SQL views or tables. If you're not sure, you can enclose a screenshot of your SmartList (Tools | SmartList Builder | SmartList Builder, select your SmartList.) Below is what an example screenshot of a SmartList based on a SQL view would look like.
Also, can you confirm that you're using GP 2010? The permissions in GP vary depending on GP version.
ASKER
Hi victoria_y, the Smartlist was created using SQL table not view because I don't know how to do that. Here is the screenshot of the smartlist that I created.
pic.jpg
pic.jpg
Wayne,
Great, thanks. So you will need to do the following:
1. In SQL Server Management Studio:
2. In GP:
If any of the users are logged in, they may need to log out and log back in after you have made these changes to be able to see the data in the SmartList.
If you need some more detail on the GP security settings, take a look at this blog post: http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/
For more information on how to use views in SmartList Builder, take a look at this blog post: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/
or this video: http://www.youtube.com/watch?v=sAnz1gzZHnk
Great, thanks. So you will need to do the following:
1. In SQL Server Management Studio:
a) Click on New Query
b) Change the database at the very top to your GP company database
c) Run the following script:
b) Change the database at the very top to your GP company database
c) Run the following script:
GRANT SELECT ON QU00111 TO DYNGRP2. In GP:
a) Either create a new Security Task, or open an existing one (GP | Tools | Setup | System | Security Tasks) Note: if you're planning on creating additional SmartLists like this, it might be best/easiest to add this to the DEFAULTUSER Task, as that is typically granted to all users and you will still need an additional step to actually give permissions to specific SmartLists to users)
b) Fill out the Task Name and Category, then select the following:
- Product: SmartList Builder
- Type: SmartList Builder Permissions
- Series: SmartList Builder
c) Check the box next to View SmartLists with SQL Tables
d) Save the Task
e) If you created a new Task, make sure it is added to the Security Roles of the GP users that you want to see this SmartList (or if you add it to a new Security Role, make sure that you assign this Role to the users that need to see this SmartList)
b) Fill out the Task Name and Category, then select the following:
- Product: SmartList Builder
- Type: SmartList Builder Permissions
- Series: SmartList Builder
c) Check the box next to View SmartLists with SQL Tables
d) Save the Task
e) If you created a new Task, make sure it is added to the Security Roles of the GP users that you want to see this SmartList (or if you add it to a new Security Role, make sure that you assign this Role to the users that need to see this SmartList)
If any of the users are logged in, they may need to log out and log back in after you have made these changes to be able to see the data in the SmartList.
If you need some more detail on the GP security settings, take a look at this blog post: http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/
For more information on how to use views in SmartList Builder, take a look at this blog post: http://victoriayudin.com/2009/04/20/how-to-use-a-sql-view-in-smartlist-builder/
or this video: http://www.youtube.com/watch?v=sAnz1gzZHnk
ASKER
Hi Abdulmalek_Hamsho, I spoke to our ERP vendor and they don't recommend me running that utility unless specified by Microsoft. I appreciate your help but I am really new to GP and I am reluctant in executing many lines of codes that I have very little understanding of.
ASKER
Hi Victoria,
I did execute the "GRANT SELECT ON QU00111 TO DYNGRP" line and it went successfully. I had also created a new Security Task and assigned the role to the task but I am still not able to query using the smartlist.
The funny thing is this, yesterday I was able to query the smartlist after recreating the smartlist using my own account (poweruser) but as of today only sa can query not anyone else (including me).
I hope that can give you a little bit more clues.
Wayne
I did execute the "GRANT SELECT ON QU00111 TO DYNGRP" line and it went successfully. I had also created a new Security Task and assigned the role to the task but I am still not able to query using the smartlist.
The funny thing is this, yesterday I was able to query the smartlist after recreating the smartlist using my own account (poweruser) but as of today only sa can query not anyone else (including me).
I hope that can give you a little bit more clues.
Wayne
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi All,
Thanks to both of you. I am using the same company and nothing has changed as far as I can tell and we only have one GP/SQL server. The user doesn't see any message when they click on the die search, it just returned an empty query.
I was able to run the script you gave me in SQL management studio exactly as you suggested and the query was successful and the screenshot is attached.
Our ERP vendor pretty much pointed us to MS Support for MS GP related stuffs and I had already submitted a ticket to them. I will have to provide them with the log files later this afternoon.
I will run the grant.sql as Abdulmalek_Hamsho suggested but I will have to wait another hour so I can make a backup of the server prior to.
I also find it weird that a power user account wasn't able to query the database thus why I am seeking help.
Wayne
Thanks to both of you. I am using the same company and nothing has changed as far as I can tell and we only have one GP/SQL server. The user doesn't see any message when they click on the die search, it just returned an empty query.
I was able to run the script you gave me in SQL management studio exactly as you suggested and the query was successful and the screenshot is attached.
Our ERP vendor pretty much pointed us to MS Support for MS GP related stuffs and I had already submitted a ticket to them. I will have to provide them with the log files later this afternoon.
I will run the grant.sql as Abdulmalek_Hamsho suggested but I will have to wait another hour so I can make a backup of the server prior to.
I also find it weird that a power user account wasn't able to query the database thus why I am seeking help.
Wayne
Wayne,
Thanks for the additional screenshots. Again, very strange that something changed between yesterday and today for a POWERUSER. I would just make sure that in SQL Server Management Studio you ran that one line query against the ASLFX database. But if you are going to run the grant.sql script anyway, then you might as well wait until you test that.
Other causes I have seen for issues with SmartList data are things like the ODBC setup and the setup of the actual table/view in SQL. If you already have a support incident open with GP Support, that's probably the best bet at this point.
Thanks for the additional screenshots. Again, very strange that something changed between yesterday and today for a POWERUSER. I would just make sure that in SQL Server Management Studio you ran that one line query against the ASLFX database. But if you are going to run the grant.sql script anyway, then you might as well wait until you test that.
Other causes I have seen for issues with SmartList data are things like the ODBC setup and the setup of the actual table/view in SQL. If you already have a support incident open with GP Support, that's probably the best bet at this point.
Just had another thought - where is this table coming from (QU00111)? Did you create it? Is there some process that is recreating it every day and populating it with new data?
ASKER
Thanks both of you for your help and whether MS can fix the problem or not I will reward the points as token of appreciation. The log they told me to retrieve are suggested to be done with the users off the system so I will have to wait until later.
ASKER
QU00111 was already created and populated. I am not sure if anyone uploaded more data into it but I don't think they did because I tried the query again as soon as I got in the morning.
I don't recognize this as an out-of-the-box GP table...I am thinking this is from a customization or a 3rd party add-on. I was asking because if the table is dropped, then recreated, permissions would most likely be reset...I am still trying to come up for an explanation of the different behavior from yesterday to today.
I would love to know what this ends up being. I have created hundreds of custom SmartLists and get asked for help on them all the time, typically the stuff you've already gone through does the trick...so there is something else going on here.
I would love to know what this ends up being. I have created hundreds of custom SmartLists and get asked for help on them all the time, typically the stuff you've already gone through does the trick...so there is something else going on here.
ASKER
It is a 3rd party customization table and I will keep both of you posted. The table wasn't dropped and recreated as far as I can tell.
Thank you.
Thank you.
I'm waiting for the grant.sql execution results then.
ASKER
Hi All,
Thank you for both of your help and both of you were right.
Abdulmalek_Hamsho: I didn't run the grant.sql command but yes it's the problem with sql database access permissions.
victoria_y: Thank you for bringing up the question about being in the right company.
The problem was that we were testing in a test company and the test users privileges were forbidded to access the other companies which the die database happens to be in. Once they were put back to allow access to the other companies then they were able to query the database. This mistake was a result of changing multiple security settings at once.
For the much efforts from both of you in trying to help me I've increased the points and split it equally.
Thanks again.
Thank you for both of your help and both of you were right.
Abdulmalek_Hamsho: I didn't run the grant.sql command but yes it's the problem with sql database access permissions.
victoria_y: Thank you for bringing up the question about being in the right company.
The problem was that we were testing in a test company and the test users privileges were forbidded to access the other companies which the die database happens to be in. Once they were put back to allow access to the other companies then they were able to query the database. This mistake was a result of changing multiple security settings at once.
For the much efforts from both of you in trying to help me I've increased the points and split it equally.
Thanks again.