Thanks again for the info! As Axton explained for Oracle you would reference the schema.table but I was not sure if MS SQL would do the same. I like the idea of seeing something.table in definitions and logging to indicate the resources being used are outside the realm of ARSystem.
The enhanced security at this time is not too much of a concern at this time (although I may go ahead since it is a new system and lock down ARSystem from the start). Both the schema and different DB approach will give us more flexible permissions than we have now. I am looking for group as you mention but also for separation and independence (within reason). Now I am going to bounce this off our DBAs. It is not so much their decision regarding the architecture however we greatly value their input as the experts. If we gave them too much say they would delete the thousands of AR maintained views that drive them crazy. I think we are going to go with a separate DB unless the DBAs or somebody on the list comes up with a good reason why it isn't good idea. Worst case after we set this up is we have to move those objects in to the ARSystem DB and use the schema approach :) Thanks everybody, Jason On Fri, Sep 7, 2012 at 3:42 PM, Jose Manuel Huerta Guillén < arsl...@theremedyforit.com> wrote: > ** A schema in MS SQL is just a way to group elements inside a database. > So, you can access them as any other element at the database. But is very > easy to define different properties or security based on schema. That means > that SQL Direct actions or view forms are done as in they were on the same > database (if fact they are). > > Using a different database just makes it more difficult to access form > Remedy. If the only purpose is to group elements, then the schema was > introduced with this objective. If you want to set different properties, > then you need different databases. > > Anyway, it's a DBA decision. > > > Regards, > > > Jose Manuel Huerta > http://theremedyforit.com/ > > > > > On Fri, Sep 7, 2012 at 9:58 PM, Jason Miller <jason.mil...@gmail.com>wrote: > >> ** This is the second time using a schema has been mentioned. I am >> going to look into this. >> >> I like where you are heading with this. It keeps the objects organized >> and can limit permissions. The one issue I see with this approach is my >> team is local server admin on the DB server and local server admins >> are automatically sysadmin. I am not happy with with this because I don't >> like that it gives all server admins (outside of my team) sysadmin but >> according to our DBA it is just kind of how things work in this setup. >> The privileged restrictions you mention would not be enforceable for my >> team. Maybe it is time to remove our local admin access? >> >> The other thing I am considering is when we restore the ARSystem DB form >> another environment the Utilities DB remains the same and doesn't require >> any updates. A schema would be in the ARSystem DB and things like >> references to linked servers would need to be updated since they also came >> over with the DB. I a figuring we could export the schema before the >> ARSystem DB refresh and import it back after the refresh? >> >> A few questions: >> >> - Using a schema would we see the schema reference say a Direct SQL >> action or SQL Menu as we would with calling a different DB? I think there >> could be a benefit of seeing this reference when troubleshooting logs. >> - Do you think my original suggestion of using a different DB that >> uses the same infrastructure (SQL Instance, storage volume) would have a >> negative impact on performance? >> >> >> Thanks! >> Jason >> >> >> On Fri, Sep 7, 2012 at 12:15 PM, Jose Manuel Huerta Guillén < >> arsl...@theremedyforit.com> wrote: >> >>> ** If the database is MS-SQL and you want to organize your objects to >>> reduce the risk of editing the wrong object, then I don't recommend you to >>> create a separated DB. MS-SQL provides the schema functionality. Just >>> create a new schema, and put all objects there. Then create a user with >>> privileges only at this schema. Thus, this user will only see the external >>> objects and wont see the Remedy ones. Give this user to people that must >>> edit those objects. Remedy will be able to see those objects because they >>> are at the same database. Also the impact on the database is null, since >>> they are at the same database. >>> >>> >>> Jose Manuel Huerta >>> http://theremedyforit.com/ >>> >>> >>> >>> >>> On Fri, Sep 7, 2012 at 9:08 PM, Jason Miller <jason.mil...@gmail.com>wrote: >>> >>>> MS SQL 2008 Standard 64-bit >>> >>> >>> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >>> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
