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 <[email protected]> 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 < > [email protected]> 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 <[email protected]>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"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
