O R G A N I C / F E R T I L I Z E R: sms: adjust permissions when using new tables...

Oct 27, 2006

sms: adjust permissions when using new tables...

okay, here's a little tidbit the next time you go messing around with creating new tables in sms. (uh, not that i have any knowledge of doing that.) if you've seen this error in web reports, then you'll know what i'm talking about. basically, in this scenario, you create a table, populate the data, match it up to something and everything works in query analyzer. the second you move it to a sms report... you get this:
An error occurred when the report was run. The details are as follows: SELECT permission denied on object 'myNewTable', database 'mySMSDB', schema 'dbo'.

Error Number: -2147217911
Source: Microsoft OLE DB Provider for SQL Server
Native Error: 229
essentially, the problem is the table lacks the correct permissions. it seems the best way to go about doing this is first to create a view off the table you've made. afterwards, apply these permissions to the view:
smsschm_users select
webreport_approle select

after that, your report should run just fine... i'll post more on what i was working on later... that is consequently not something i am doing in production or would ever, ever, ever recommend. :D