Smartlist Builder Smartlist based on new View in SQL 2008 with GP

Hi there,

I created one smartlist based on a view in SQL 2008. Smartlist is ok and 
working fine with my user id and sa. But when I give security access to other 
users, they can see the Smartlist Option but when they click it they get 
error message that don't have rights to one of the table used in this 
smartlist. I also have given them Grants through SQL Server. But no effect. 

What is the problem?
0
4/16/2009 5:19:03 PM
greatplains 29623 articles. 3 followers. Follow

5 Replies
455 Views

Similar Articles

[PageSpeed] 53

Naseer,

Check my article on the subject at 
http://dynamicsgpblogster.blogspot.com/2008/12/smartlist-builder-security-for-sql.html. 
It explains an issue similar to the one you are having. If it does not solve 
your problem please post back.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com


"Naseer Farooqi" wrote:

> Hi there,
> 
> I created one smartlist based on a view in SQL 2008. Smartlist is ok and 
> working fine with my user id and sa. But when I give security access to other 
> users, they can see the Smartlist Option but when they click it they get 
> error message that don't have rights to one of the table used in this 
> smartlist. I also have given them Grants through SQL Server. But no effect. 
> 
> What is the problem?
0
MarianoGomez (3440)
4/16/2009 6:34:21 PM
Hi Victoria,

Thanks for this solution. This solved the problem. 

But now one more thing is there, why only POWER USER has access to the 
Smartlist Builder? Why can we not give access to other users?

Thanks


"Victoria [MVP]" wrote:

> Naseer,
> 
> From 
> http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/:
> 
> If you are on GP 10.0 and your SmartList is based on SQL views or tables, 
> you will need to add permissions to the View SmartLists with SQL Tables 
> operation.  Here are the settings under Security Tasks:
> 
> Product: SmartList Builder
> Type: SmartList Builder Permissions
> Series: SmartList Builder Permissions or SmartList Builder (depends on the 
> Service Pack)
> Operations: View SmartLists with SQL Tables
> 
> -- 
> Victoria Yudin
> Dynamics GP MVP
> Flexible Solutions - home of GP Reports
> http://www.flex-solutions.com/gpreports.html
> blog: http://www.victoriayudin.com
> 
> 
> "Naseer Farooqi" <NaseerFarooqi@discussions.microsoft.com> wrote in message 
> news:06084D20-1340-4813-AFA0-BD6D58059FC0@microsoft.com...
> > Hi there,
> >
> > I created one smartlist based on a view in SQL 2008. Smartlist is ok and
> > working fine with my user id and sa. But when I give security access to 
> > other
> > users, they can see the Smartlist Option but when they click it they get
> > error message that don't have rights to one of the table used in this
> > smartlist. I also have given them Grants through SQL Server. But no 
> > effect.
> >
> > What is the problem? 
> 
0
4/16/2009 6:59:02 PM
Yes Victoria, 

I got this answer from your other reply to some other instance.

Thanks


"Victoria [MVP]" wrote:

> Naseer,
> 
> Take a look at the last comment on that same blog post: 
> http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/. 
> I think that addresses your question.  Maybe not the 'why' but the 'how'. 
> :-)
> 
> -- 
> Victoria Yudin
> Dynamics GP MVP
> Flexible Solutions - home of GP Reports
> http://www.flex-solutions.com/gpreports.html
> blog: http://www.victoriayudin.com
> 
> 
> "Naseer Farooqi" <NaseerFarooqi@discussions.microsoft.com> wrote in message 
> news:9141F66A-46E9-43B2-A72E-EAEAD4C35DD0@microsoft.com...
> > Hi Victoria,
> >
> > Thanks for this solution. This solved the problem.
> >
> > But now one more thing is there, why only POWER USER has access to the
> > Smartlist Builder? Why can we not give access to other users?
> >
> > Thanks
> >
> >
> > "Victoria [MVP]" wrote:
> >
> >> Naseer,
> >>
> >> From
> >> http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlist-in-gp-10/:
> >>
> >> If you are on GP 10.0 and your SmartList is based on SQL views or tables,
> >> you will need to add permissions to the View SmartLists with SQL Tables
> >> operation.  Here are the settings under Security Tasks:
> >>
> >> Product: SmartList Builder
> >> Type: SmartList Builder Permissions
> >> Series: SmartList Builder Permissions or SmartList Builder (depends on 
> >> the
> >> Service Pack)
> >> Operations: View SmartLists with SQL Tables
> >>
> >> -- 
> >> Victoria Yudin
> >> Dynamics GP MVP
> >> Flexible Solutions - home of GP Reports
> >> http://www.flex-solutions.com/gpreports.html
> >> blog: http://www.victoriayudin.com
> >>
> >>
> >> "Naseer Farooqi" <NaseerFarooqi@discussions.microsoft.com> wrote in 
> >> message
> >> news:06084D20-1340-4813-AFA0-BD6D58059FC0@microsoft.com...
> >> > Hi there,
> >> >
> >> > I created one smartlist based on a view in SQL 2008. Smartlist is ok 
> >> > and
> >> > working fine with my user id and sa. But when I give security access to
> >> > other
> >> > users, they can see the Smartlist Option but when they click it they 
> >> > get
> >> > error message that don't have rights to one of the table used in this
> >> > smartlist. I also have given them Grants through SQL Server. But no
> >> > effect.
> >> >
> >> > What is the problem?
> >> 
> 
0
4/16/2009 7:52:04 PM
Hi,

Users are able to access the custom smartlists built with Smartlist
Builder only when we assign the select table grant permission to the
users despite assigning security in Smartlist builder Permissions
within GP security. However this is only when we use SQL Tables for
building smartlists and also when we create our own tables/views and
not GP views/tables.



Thanks

Janakiram M.P.

On Apr 16, 9:59=A0pm, Naseer Farooqi
<NaseerFaro...@discussions.microsoft.com> wrote:
> Hi Victoria,
>
> Thanks for this solution. This solved the problem.
>
> But now one more thing is there, why only POWER USER has access to the
> Smartlist Builder? Why can we not give access to other users?
>
> Thanks
>
>
>
> "Victoria [MVP]" wrote:
> > Naseer,
>
> > From
> >http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlis...
>
> > If you are on GP 10.0 and your SmartList is based on SQL views or table=
s,
> > you will need to add permissions to the View SmartLists with SQL Tables
> > operation. =A0Here are the settings under Security Tasks:
>
> > Product: SmartList Builder
> > Type: SmartList Builder Permissions
> > Series: SmartList Builder Permissions or SmartList Builder (depends on =
the
> > Service Pack)
> > Operations: View SmartLists with SQL Tables
>
> > --
> > Victoria Yudin
> > Dynamics GP MVP
> > Flexible Solutions - home of GP Reports
> >http://www.flex-solutions.com/gpreports.html
> > blog:http://www.victoriayudin.com
>
> > "Naseer Farooqi" <NaseerFaro...@discussions.microsoft.com> wrote in mes=
sage
> >news:06084D20-1340-4813-AFA0-BD6D58059FC0@microsoft.com...
> > > Hi there,
>
> > > I created one smartlist based on a view in SQL 2008. Smartlist is ok =
and
> > > working fine with my user id and sa. But when I give security access =
to
> > > other
> > > users, they can see the Smartlist Option but when they click it they =
get
> > > error message that don't have rights to one of the table used in this
> > > smartlist. I also have given them Grants through SQL Server. But no
> > > effect.
>
> > > What is the problem?- Hide quoted text -
>
> - Show quoted text -

0
janakirammp (688)
4/16/2009 7:55:46 PM
Um....

From
http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlis...

If you are on GP 10.0 and your SmartList is based on SQL views or tables,
you will need to add permissions to the View SmartLists with SQL Tables
 operation. Here are the settings under Security Tasks:

Product: SmartList Builder
Type: SmartList Builder Permissions
Series: SmartList Builder Permissions or SmartList Builder (depends on the 
Service Pack)
Operations: View SmartLists with SQL Tables

-- 
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: http://www.victoriayudin.com


"Janakiram" <janakirammp@gmail.com> wrote in message 
news:99bd3095-d7ed-4571-8590-cd38e4be9860@h2g2000yqg.googlegroups.com...
Hi,

Users are able to access the custom smartlists built with Smartlist
Builder only when we assign the select table grant permission to the
users despite assigning security in Smartlist builder Permissions
within GP security. However this is only when we use SQL Tables for
building smartlists and also when we create our own tables/views and
not GP views/tables.



Thanks

Janakiram M.P.

On Apr 16, 9:59 pm, Naseer Farooqi
<NaseerFaro...@discussions.microsoft.com> wrote:
> Hi Victoria,
>
> Thanks for this solution. This solved the problem.
>
> But now one more thing is there, why only POWER USER has access to the
> Smartlist Builder? Why can we not give access to other users?
>
> Thanks
>
>
>
> "Victoria [MVP]" wrote:
> > Naseer,
>
> > From
> >http://victoriayudin.com/2008/10/08/granting-access-to-a-new-smartlis...
>
> > If you are on GP 10.0 and your SmartList is based on SQL views or 
> > tables,
> > you will need to add permissions to the View SmartLists with SQL Tables
> > operation. Here are the settings under Security Tasks:
>
> > Product: SmartList Builder
> > Type: SmartList Builder Permissions
> > Series: SmartList Builder Permissions or SmartList Builder (depends on 
> > the
> > Service Pack)
> > Operations: View SmartLists with SQL Tables
>
> > --
> > Victoria Yudin
> > Dynamics GP MVP
> > Flexible Solutions - home of GP Reports
> >http://www.flex-solutions.com/gpreports.html
> > blog:http://www.victoriayudin.com
>
> > "Naseer Farooqi" <NaseerFaro...@discussions.microsoft.com> wrote in 
> > message
> >news:06084D20-1340-4813-AFA0-BD6D58059FC0@microsoft.com...
> > > Hi there,
>
> > > I created one smartlist based on a view in SQL 2008. Smartlist is ok 
> > > and
> > > working fine with my user id and sa. But when I give security access 
> > > to
> > > other
> > > users, they can see the Smartlist Option but when they click it they 
> > > get
> > > error message that don't have rights to one of the table used in this
> > > smartlist. I also have given them Grants through SQL Server. But no
> > > effect.
>
> > > What is the problem?- Hide quoted text -
>
> - Show quoted text -

0
victoria (3340)
4/16/2009 8:49:26 PM
Reply:

Similar Artilces:

New Access user needs a little help
When I fire up access a list of databases i created are on the right side of the screen...I would like to delete some of those I created exploring the program. Does anyone have time to assist me? Thanks Jerry "Jerry L Moore" <Jerry L Moore@discussions.microsoft.com> wrote in message news:AA914856-1391-4B99-B09B-DCEB9CE3AA92@microsoft.com... > When I fire up access a list of databases i created are on the right > side of > the screen...I would like to delete some of those I created exploring > the > program. Does anyone have time to assist me? T...

How to allow new user's pst to be default pst?
We have a new person using a computer previously used by a different person. We want to eliminate the previous person's old pst file and let the new person start fresh with her own pst. I created a new pst, then chose OPEN from the folder menu, navigated to the new folder, selected it, pressed ok but the old pst file information stays loaded. How do I make the new pst file the default pst? I deleted the old pst file completely but then of Outlook wouldn't open at all of course. I even uninstalled Outlook, deleted the old pst, then reinstalled Outlook as if it had never bee...

outlook 2007 view
I had Franklin Covey outlook add on software on my old computer and they have a view where you have the calander, inbox, to-do list, and journal all showing on one page so you could see everything and document activity quickly through out the day without opening and closing everything. Is there a way to duplicate this with outlook software. I have tried to use the open the journal in a new window but can't insert just a blank journal page for today into the view. Any ideas would be appreciated. ...

Default for new objects
Hello, Using Publisher 2003, Windows XP. I cannot get the Default for New Objects checkbox to be active on text boxes. Other shapes, no problem. Following all directions to the letter: 1. Create the text box. 2. Right-click>Format Text Box. 3. Change various settings (tried them all). 4. Colors and Lines tab has the checkbox disabled. I also tried working with a style other than Normal. Thanks, in advance, for any assistance! -- Are you saying you would like every text box to be the same within a session? Format, text box, Colors & Lines tab, on the left bottom is a check off ...

Access External Tables Using SmartList Builder
Can I access external SQL tables in SmartList Builder? We want to query tables in the Tensoft Fabless Semiconductor Manufacturing (FSM) software module that is bolted to GP. Thank you. You certainly can! If your tables reside in another database, you will need to create views from within your GP company database as follow: create view vwTensoftTable as select field1, field2,...,fieldN from YourTensoftDatabase.dbo.tensofttable You will need to then grant permissions to DYNGRP on those views. From there on, you can pull into SLB. Best regards, -- Best regards, -- MG.- Mariano Gomez, ...

Can I add new nodes to an XPathDocument?
I have an XPathDocument which contains a list of color definitions. If I want to add a new color definition to, or delete an existing one from, the source xml file how can I force this to be reflected in the XPathDocument? Do I have to reload the XPathDocument? If so, would I need to dispose of the old XPathDocument and it's associated XPathNavigator somehow? Can I even change a node value in the XPathDocument? E.g. if I want to change the color description. Thanks Steve steve bull wrote: > I have an XPathDocument which contains a list of color definitions. If I want to add ...

SmartList Security for New SmartLists
I'm having a problem with version 9 SmartList Security. I create a SmartList and then open SmartList and let it add the new object. I go to Advanced Security and the SmartList is marked automatically but nobody that is marked for it can see it. I have to unmark and remark the checkbox. Is there a setup issue or a software issue? -- Charles Allen, MVP Hi Charles, I have you gone into Smart list Security. I have found that even though the New Smartlist object is checked in Advanced Security, it will not be checked in the Smart List Security. I believe that this has been writt...

access status bar from View
My status bar object is created in MainFrm.cpp. I need to display information in it from my View.cpp. I'm not quite sure how I should do this. Should I do a SetPaneText in my View. If so how do I do this. Otherwise, should I set some some variable that is accessed from MainFrm? Thanks for the help. -- to reply, remove Big10 from address Andrew, You can access the main frame window from your view: CMainFrame* pFrame = (CMainFrame *)AfxGetMainWnd(); .... and then access your status bar via GetMessageBar. Dave -- MVP VC++ FAQ: http://www.mvps.org/vcfaq What I do is use WM_SETMESSA...

New To Group
Hi everyone, I just signed up to this newsgroup. Since we're in the business, I can send helpful Microsoft Exchange maintenance info from time to time, if you don't mind. [non commercial] All the best, Matt Hockin Lucid8 Information Services LLC Creators of http://www.goexchange.com * Phone: 425.451.2595 * Monday-Friday 8:00am to 6:00pm PST ...

datasheet view form open NEW
I have a datasheet form when it opens I'd like it to setfocus at the "Enter NEW Area HERE"(defaultvalue of txtAreaName) new record instead of the first record on the list. I cannot just change it to data entry to YES because then I can't see all records which I want to see. Is there a way to setfocus to the new record in datasheet view? Thanks If you're opening the form in datasheet view in code, you can do it in the DoCmd.OpenForm statement by passing an openarg and testing for that argument in the OnLoad or OnOpen event in the form. If the argument is present th...

Summarizing Items based on classifications
Looking for some help on figuring out how to do something in Excel 97. I'm using a spreadsheet to track my expenses and spending. Until now, I've just been recording the date, amount, and an explanation, and subtotalling each month. Now I'd like to add a category and total by category for each month. I've added a new column for a category code (A, B, C, etc). So I have Date, Amount, Category, and Explanation. I don't know how to tell Excel to total each category by month on another page... Any help is appreciated... Thanks in advance... Paul =SUMPRODUCT(--(M...

I want buy Money 2008...
....but I want use my old file mny Money 2000 "ITALIANO". Money 2008 US can't convert file Money2000ITA but it can convert only US old version. Exist a converter "international"??? Thanx - Claudio P. Exporting to .qif file from Money 2000 and importing into Money 2008 should work. It did when I converted my Money 2005 French to Money2008 US. "Claudio P." <cldparisi@gmail.com> a �crit dans le message de news:aecde3b2-9f9a-4eaa-9346-9978d2ac05c0@p20g2000yqi.googlegroups.com... > ...but I want use my old file mny Money 2000 "ITALIANO". ...

Title bar in Doc/View application
I have an MFC application using the doc/view architecture. The default title bar shows "Untitled - My App Name. How/where would I change the code to use the title I want? Thank you for your help. "billyard" <dmetcalf@columbus.rr.com> wrote in message news:474a21c2$0$2328$4c368faf@roadrunner.com... >I have an MFC application using the doc/view architecture. The default >title bar shows "Untitled - My App Name. How/where would I change the code >to use the title I want? Thank you for your help. Go to your Resource view and double click on String Table. ...

Try Catch to Pinpoint Set-Based Error?
SQL 2008. I have a fairly simple Merge statement with this: When Not Matched Then Insert Values ( ... long column list ... cast( crazyCol1 as decimal(16,8) ) / 1000000 ) , cast( crazyCol2 as decimal(16,8) ) / 1000000 ) cast( crazyCol2 as decimal(16,8) ) / 1000000 ) cast( crazyCol2 as decimal(16,8) ) / 1000000 ) ) The insert source is about 50,000 rows. One of the rows is failing the cast (original source is varchar(36). I would like to implement a try/catch block around this somehow so I can catch the offending row. How can I do that? Thanks. SnapDive (...

Go to a new record on another form?
Hello! I have a form (Form2) which is being opened (or sometimes set focus to) from another form (Form1). On the open and gotfocus events, I want it to go to a new record; however, I also want it to be able to see/edit previous records (so the DataEntry mode option is right out). -I have no OnGotFocus, OnLoad, etc events in Form2 -the code on Form1 calling Form2 is on a command button. I've included the whole mess of code on that button, just in case I'm missing something dreadfully obvious: Private Sub Command28_Click() 'the "record the valiant effort of this volunteer&qu...

SmartList/SmartList builder
Hello, I was training a group of customers today on SmartList builder and they were creating trying to create a SmartList report. Everything goes well until at least 4 or 5 of them were having errors saving the SmartList in the SmartList Builder window. The error was something about a duplicate key being created on the SLB11000 and the SLB 11100 tables (I cannot recall which users have errors on which tables) but after attempting to save the SmartList ID and building the SmartList ID on the actual SmartList view, I cannot see the favorite there. (They say they may have, at one point in...

The new member could not be added
Hi, I could find an answer to this for Outlook 2002 but not for Outlook 2003. Is the answer the same? We are using SBS 2003. The error is: "The distribution list has reached the maximum size for your network e-mail server. The new member could not be added." List was at 132 members. there is a limit on the number of entries that can be added to a personal distribution list...I've heard different numbers...but 130 is close...only way around this is to "nest" distribution groups, or create the group in AD so it's in the GAL... -- Susan Conkey [MVP] "Pa...

Security based on Account Type
Can we define security/access based on field(s), say Account Type of Account, when Account Type is anything but "TypeA", all users of the system have full access, however when Account Type is "TypeA", only certain group(s) of people can see the accounts, how should I implement it? thanks A way I've been working on is to put some client side code, so that when the type of an account changes, the client side code will change the owner of the account, together with playing with the business unit security settings to achieve this. what do you guys think? appreciate...

New entit device
hi I have a custom entity called device which is linked to the service activity by the field regarging. I can not change this relationship even if I remove activity as an associated entity. A device is related to a customer on a one to many relationship that is device can be for many customers. What i want to do is show the device name on the service activity form when a customer is scheduled for a service. I can not do this unless the device is manually selected in the regarding field. Can someone please advice me how to go about this. I tried to set a workflow rule which said if the...

New Mail Desktop Alert...
I don't see it - why?! I have all the settings set (under 'advanced email options' the NMDA box is checked, and I have selected 'show new mail desktop alert' in the systray icon menu). They always worked in the Beta (which was never installed on this system), but I've never got them to work in the full version - and they were one of the reasons I purchased it - that and the reading pane. In addition, this is the default account, as it's the only account I have. I have it set to auto send/receive every 5 minutes, and keep OL minimised to the systray (ahem, notifica...

New Car Loan
Ok, I've been using Money for years and years, but never had to enter a loan into it before. I bought a car, a simple interest loan from Chrysler. The loan wizard apparently doesn't like the numbers I plug into it. I want to track the loan like I track my credit card and bank transactions, and I have the monthly payment amount, the loan amount, the loan+principal amount, plus the interest rate as given to me by Chrysler, but no matter what I type in, Money tells me that my numbers don't match. Isn't there any way to do this WITHOUT the stupid wizard? I don't wan...

Distributed Transaction on SQL 2005
A stored procedure running from server A database db1 updates both local tables, as well as, remote ones, on server B database db2 via a db link. All updates are part of the same distributed transaction. I have got it to work on one server. In other words, I have made all provisions to make this work like turn off rpc security, configure msdtc in windows components. However, when I changed the remote server to be server C database db2... The two inserts done on the local server succeeded. But delete comman against remote table never comes back. No errors or locks. Wha...

Loop SQL
I need to Loop this statement until all records in the tblCheckDetails that have this SalesID (Forms!frmFXVoidItem!TxtSalesID) table are added to the tblVoids...How would I do this? Thanks DS CurrentDb.Execute "INSERT INTO tblVoids (VoidID,VoidCheckID,VoidItemID,VoidAmount,VoidReasonID,VoidManagerID,VoidQTY) " & _ "Values(" & Forms!frmFXVoidItemReason!TxtVoidID & "," & Forms!frmFXVoidItem!TxtSalesID & "," & Forms!frmFXVoidItem!TxtItemID & "," & Forms!frmFXVoidItem!TxtAmount & "," & Forms...

Prefer how Outlook 2000 looked-now using 2003-Can I switch views?
I don't like the way Outlook 2003 displays and would prefer to go back to the 2000 display. Is this possible. Kinda, http://www.slipstick.com/emo/2003/up031029.htm#classicview might be helpful. "Manhart" <Manhart@discussions.microsoft.com> wrote in message news:A4549A3C-4B06-4AEC-8ADA-DD154CC7795B@microsoft.com... >I don't like the way Outlook 2003 displays and would prefer to go back to >the > 2000 display. Is this possible. Outlook 2003 is highly customizeable - tell us what you want to change, and we'll tell you how to achieve it. There is ...

Received Not Invoiced
GP 9.0 contains a prewritten Smartlist under Receivings line Items entitled "Shipments Received but Not Invoiced*". I, erroneously, expected the results to match the Received Not Invoiced report (Reports - Purchasing - Analysis - Received Not Invoiced). However, it does not. In my instance, the Smartlist contains numerous items that are not on the report. It appears that the Smartlist is simply querying POP10500 file and comparing the QTY Shipped field to the QTY Matched fields and displaying those items where these amounts are not equal. When I attempted to examine the Rece...