Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of questions
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple company

Hi. We have a SQL database for one company with multi user access. I am
curious as to best practice solutions for expanding a db to multiple company
access.
Some parts of the db need to be shared, others are private to the company
logged in.
If every time a query was executed it required company filtering, it seems a
waste having company parameters in every minor query, and would require
entire code rewrite. Not being a programmer I don't know what options sql
server may offer, but I know enough about sql to have these sort of question
s
distract your thinking until answered...
I hope this is the correct forum for general questions such as this.
Thanks in advance.
swasswas
Create a LOGIN for those 'outside' companies. Grant the database access to
this LOGIN .
You can create VIEWS to show the data that only this LOGIN needs and GRANT
it SELECT ON the VIEW (For more details please refer to the BOL)
As well , you can GRANT an EXECUTE permissions to the LOGIN for STORED
PROCEDURES and not granting permissions on underlaying tables
"swas" <swas@.discussions.microsoft.com> wrote in message
news:C49B53B0-9483-4E87-9166-109ED0495E24@.microsoft.com...
> Hi. We have a SQL database for one company with multi user access. I am
> curious as to best practice solutions for expanding a db to multiple
> company
> access.
> Some parts of the db need to be shared, others are private to the company
> logged in.
> If every time a query was executed it required company filtering, it seems
> a
> waste having company parameters in every minor query, and would require
> entire code rewrite. Not being a programmer I don't know what options sql
> server may offer, but I know enough about sql to have these sort of
> questions
> distract your thinking until answered...
> I hope this is the correct forum for general questions such as this.
> Thanks in advance.
>
> swas

Multiple applications on 1 database - Namespacing ?

Hello,
I was wondering what would be the best practice to integrate multiple
application-specific objects into one database.
The most obvious way would be to prefix the object names for example
App1_tblUsers and App2_tblUsers.
Is there a better way to ultimately come to some kind of namespacing system
for the database ?
I've read about using the owner object, but since it's an existing and live
database, I'm reluctant to get my feet too wet into something, before I've
looked into other slick ways.
BerenBeren wrote:
> Hello,
> I was wondering what would be the best practice to integrate multiple
> application-specific objects into one database.
> The most obvious way would be to prefix the object names for example
> App1_tblUsers and App2_tblUsers.
> Is there a better way to ultimately come to some kind of namespacing
> system for the database ?
> I've read about using the owner object, but since it's an existing
> and live database, I'm reluctant to get my feet too wet into
> something, before I've looked into other slick ways.
> Beren
Sounds like you really need three databases if there is no or little
overlap between them. Any reason that's not an option. The tables should
not be "application" entities, but database ones. So, if you need to
prefix them with something, you'll probably want to give them a logical
database name prefix, not an application one.
Or maybe you can add an ApplicationID as a FK column to the User table
and create an Application table that defines all applications.
David Gugick
Imceda Software
www.imceda.com|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Ob5LUIiDFHA.1628@.TK2MSFTNGP15.phx.gbl...
> Beren wrote:
> Sounds like you really need three databases if there is no or little
> overlap between them. Any reason that's not an option. The tables should
> not be "application" entities, but database ones. So, if you need to
> prefix them with something, you'll probably want to give them a logical
> database name prefix, not an application one.
> Or maybe you can add an ApplicationID as a FK column to the User table and
> create an Application table that defines all applications.
> --
> David Gugick
> Imceda Software
> www.imceda.com
The problem is unfortunately your first statement. The existing tables have
almost no similarities to the new ones, and I'm explicitly told to use this
existing db for the second application too.
The only similarity would be the names I'm using (both apps have a tblUser
table etc); the problem.
The real downer though is that the second application already has its
specific tables and procedures nearly finished (on a test sql server), so
instead of renaming every table / view/ proc, from this second db, I'd need
a way to group my objects; so that - after merging the the test db with the
live db- the server recognizes the merged db as having unique names
nonetheless.|||Beren wrote:
> "David Gugick" <davidg-nospam@.imceda.com> wrote in message
> news:Ob5LUIiDFHA.1628@.TK2MSFTNGP15.phx.gbl...
> The problem is unfortunately your first statement. The existing
> tables have almost no similarities to the new ones, and I'm
> explicitly told to use this existing db for the second application
> too. The only similarity would be the names I'm using (both apps have
> a
> tblUser table etc); the problem.
> The real downer though is that the second application already has its
> specific tables and procedures nearly finished (on a test sql
> server), so instead of renaming every table / view/ proc, from this
> second db, I'd need a way to group my objects; so that - after
> merging the the test db with the live db- the server recognizes the
> merged db as having unique names nonetheless.
You either have to rename or use a new database. Renaming will likely
have bigger implications than using a new database because of all the
table relationships and procedures involved.
Why not recommend a new database, since it's clearly the way to go.
David Gugick
Imceda Software
www.imceda.com