Best strategy for multiple sites

I have a client that has a central database. Users are dispersed at regional
offices, and should only have access to data pertaining to their own office.
I'm trying to figure out the best way to secure data access for each group
of users. I thought of maintaining a table of users and the regional office
they belong to. Then a UDF would only return the appropriate data. This
means doing everything at the database level, regardless of the front end
(in this case, SSRS).
Another strategy would be to do all the filtering within SSRS, using the
user global variable, and filter the data dynamically based on the user
value (with a query expression). One thing I don't like about this approach
is that any other front end (web or Windows based) could access data without
filtering on a regional site basis.
Any thoughts?
--
Thank you,
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.comIt looks to me like you are currently planning to have a one to one match
between SQL Server logins and users. This can be a real pain. Also, you lose
the chance for connection pooling. For connection pooling to work the
connection must be exactly the same. If SQL Server can be run in mixed mode
I suggest creating a read only user that is used just for reporting. Then
when you have stored procedure you just have to give this execution rights.
Then combine that with using the user global variable. Don't filter it
locally, that will require a lot more data to be processed and you are much
better off to limit the amount of data coming to RS. Instead you can have a
hidden parameter that takes the user variable does a query and uses the
first aggregate to return the office. Then for all queries that need the
office you then pass that to the query.
Another possibility is you have a database per office. Then create views to
the database that has the data. Some views are one to one, others use the
appropriate office. Then use the user in an expression for the datasource.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Alain Quesnel" <alainsansspam@.logiquel.com> wrote in message
news:%23vUJ5XpNIHA.5720@.TK2MSFTNGP04.phx.gbl...
>I have a client that has a central database. Users are dispersed at
>regional offices, and should only have access to data pertaining to their
>own office. I'm trying to figure out the best way to secure data access for
>each group of users. I thought of maintaining a table of users and the
>regional office they belong to. Then a UDF would only return the
>appropriate data. This means doing everything at the database level,
>regardless of the front end (in this case, SSRS).
> Another strategy would be to do all the filtering within SSRS, using the
> user global variable, and filter the data dynamically based on the user
> value (with a query expression). One thing I don't like about this
> approach is that any other front end (web or Windows based) could access
> data without filtering on a regional site basis.
> Any thoughts?
> --
> Thank you,
> Alain Quesnel
> alainsansspam@.logiquel.com
> www.logiquel.com
>

Best Practices Analyzer

I'd like to add my own rules to be reported on by Best Practices analyzer.
When will we be able to create custom rules?
Also need to be able to isolate to specific databases.
Thanks for your feedback Bruce.
We've considered user-defined rules and we intend to do it, but not in the
short term (i.e. not in the next release of BPA). Be assured we do have
extensibility in mind.
To scan only a few specific databases, you can type the name of the
databases you want as part of the registration of a SQL Server instance.
(semicolon delimited)
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"bruce" <bruce@.discussions.microsoft.com> wrote in message
news:449614D3-F129-470B-A845-3CA4C21096F8@.microsoft.com...
> I'd like to add my own rules to be reported on by Best Practices analyzer.
> When will we be able to create custom rules?
> Also need to be able to isolate to specific databases.
|||Does the BPA tool check user accounts for pasword strength?
"Christian Kleinerman [MS]" wrote:

> Thanks for your feedback Bruce.
> We've considered user-defined rules and we intend to do it, but not in the
> short term (i.e. not in the next release of BPA). Be assured we do have
> extensibility in mind.
> To scan only a few specific databases, you can type the name of the
> databases you want as part of the registration of a SQL Server instance.
> (semicolon delimited)
> - Christian
> --
> ___________________________
> Christian Kleinerman
> Program Manager, SQL Engine
> This posting is provided "AS IS" with no warranties, and confers no rights.
> "bruce" <bruce@.discussions.microsoft.com> wrote in message
> news:449614D3-F129-470B-A845-3CA4C21096F8@.microsoft.com...
>
>
sql

Best Practice question

Our company has several large 3rd-party applications, each with its' own
dedicated SQL Server database. I am developing our own add-ons and utilities
that use data from these databases.
My question is, should my Views, Functions & Stored Procedures reside in the
3rd-party database (they have a standard naming convention to identify our
code), or should they be in a separate database referencing the tables, views
etc. in the 3rd-party database?
Is there any loss in efficiency by keeping our code in a separate datebase?
We are using SQL 2000 Standard.
Thank you.I don't see a problem with keeping the objects in the third-party database
as long as you have a naming convention to identify the objects and are
prepared to re-apply scripts to re-create the objects, if needed. If you
keep objects in a separate database, you'll need to create users in both
databases and can't use ownership chaining unless you enable the
'db-chaining' database option in both databases.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> Our company has several large 3rd-party applications, each with its' own
> dedicated SQL Server database. I am developing our own add-ons and
> utilities
> that use data from these databases.
> My question is, should my Views, Functions & Stored Procedures reside in
> the
> 3rd-party database (they have a standard naming convention to identify our
> code), or should they be in a separate database referencing the tables,
> views
> etc. in the 3rd-party database?
> Is there any loss in efficiency by keeping our code in a separate
> datebase?
> We are using SQL 2000 Standard.
> Thank you.|||Dan,
Thank you for the quick response. One thing I did not address was storing
data in custom tables in the 3rd party db which I am also doing. Because the
data in the custom tables changes more frequently than the 3rd party, I use
scheduled DTS packages to copy data from the custom tables to a separate db.
Any thoughts on that?
Thank you again.
--
"Dan Guzman" wrote:
> I don't see a problem with keeping the objects in the third-party database
> as long as you have a naming convention to identify the objects and are
> prepared to re-apply scripts to re-create the objects, if needed. If you
> keep objects in a separate database, you'll need to create users in both
> databases and can't use ownership chaining unless you enable the
> 'db-chaining' database option in both databases.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> > Our company has several large 3rd-party applications, each with its' own
> > dedicated SQL Server database. I am developing our own add-ons and
> > utilities
> > that use data from these databases.
> >
> > My question is, should my Views, Functions & Stored Procedures reside in
> > the
> > 3rd-party database (they have a standard naming convention to identify our
> > code), or should they be in a separate database referencing the tables,
> > views
> > etc. in the 3rd-party database?
> >
> > Is there any loss in efficiency by keeping our code in a separate
> > datebase?
> >
> > We are using SQL 2000 Standard.
> >
> > Thank you.
>|||> Any thoughts on that?
The main reason would might want to store the tables in a different database
is if you have a lot of data and the recovery/availability requirements are
different. Separation will give you more flexibility.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
news:A89EB44E-5E8C-4239-9E01-A92644E4EE3A@.microsoft.com...
> Dan,
> Thank you for the quick response. One thing I did not address was storing
> data in custom tables in the 3rd party db which I am also doing. Because
> the
> data in the custom tables changes more frequently than the 3rd party, I
> use
> scheduled DTS packages to copy data from the custom tables to a separate
> db.
> Any thoughts on that?
> Thank you again.
> --
> "Dan Guzman" wrote:
>> I don't see a problem with keeping the objects in the third-party
>> database
>> as long as you have a naming convention to identify the objects and are
>> prepared to re-apply scripts to re-create the objects, if needed. If you
>> keep objects in a separate database, you'll need to create users in both
>> databases and can't use ownership chaining unless you enable the
>> 'db-chaining' database option in both databases.
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in
>> message
>> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
>> > Our company has several large 3rd-party applications, each with its'
>> > own
>> > dedicated SQL Server database. I am developing our own add-ons and
>> > utilities
>> > that use data from these databases.
>> >
>> > My question is, should my Views, Functions & Stored Procedures reside
>> > in
>> > the
>> > 3rd-party database (they have a standard naming convention to identify
>> > our
>> > code), or should they be in a separate database referencing the tables,
>> > views
>> > etc. in the 3rd-party database?
>> >
>> > Is there any loss in efficiency by keeping our code in a separate
>> > datebase?
>> >
>> > We are using SQL 2000 Standard.
>> >
>> > Thank you.|||Thank you.
--
"Dan Guzman" wrote:
> > Any thoughts on that?
> The main reason would might want to store the tables in a different database
> is if you have a lot of data and the recovery/availability requirements are
> different. Separation will give you more flexibility.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in message
> news:A89EB44E-5E8C-4239-9E01-A92644E4EE3A@.microsoft.com...
> > Dan,
> >
> > Thank you for the quick response. One thing I did not address was storing
> > data in custom tables in the 3rd party db which I am also doing. Because
> > the
> > data in the custom tables changes more frequently than the 3rd party, I
> > use
> > scheduled DTS packages to copy data from the custom tables to a separate
> > db.
> > Any thoughts on that?
> >
> > Thank you again.
> > --
> >
> > "Dan Guzman" wrote:
> >
> >> I don't see a problem with keeping the objects in the third-party
> >> database
> >> as long as you have a naming convention to identify the objects and are
> >> prepared to re-apply scripts to re-create the objects, if needed. If you
> >> keep objects in a separate database, you'll need to create users in both
> >> databases and can't use ownership chaining unless you enable the
> >> 'db-chaining' database option in both databases.
> >>
> >> --
> >> Hope this helps.
> >>
> >> Dan Guzman
> >> SQL Server MVP
> >>
> >> "SQLGuptaSybase" <SQLGuptaSybase@.discussions.microsoft.com> wrote in
> >> message
> >> news:745D6B85-63D3-4194-9213-8721D7B3713D@.microsoft.com...
> >> > Our company has several large 3rd-party applications, each with its'
> >> > own
> >> > dedicated SQL Server database. I am developing our own add-ons and
> >> > utilities
> >> > that use data from these databases.
> >> >
> >> > My question is, should my Views, Functions & Stored Procedures reside
> >> > in
> >> > the
> >> > 3rd-party database (they have a standard naming convention to identify
> >> > our
> >> > code), or should they be in a separate database referencing the tables,
> >> > views
> >> > etc. in the 3rd-party database?
> >> >
> >> > Is there any loss in efficiency by keeping our code in a separate
> >> > datebase?
> >> >
> >> > We are using SQL 2000 Standard.
> >> >
> >> > Thank you.
> >>
>

Best practice for writing own system procedures

Hello,

I'm searching for a best practice or other documentation for writing my own 'system procedure'.
I want to write procs which I can call in the context of every database without using a database name analogous to sp_who for example.
I read about the 'Resource Database'. All system procedures are stored in that readonly database and appear logically in the sys schema of every database.
But I couldn't find documentation about writing my own 'system procedure'.
I discover so far that procs with prefix sp_ stored in the master database do what I want. But is that the only way or is there a better way to do it?
In other threads I read that it is recommended not to use sp_ as prefix for procedures

Wolfgang?

It's not recommended to use the sp_ prefix because there is a slight performance hit if you use it for user stored procedures in a database other than master -- this is because SQL Server will look in master for the stored procedure, if it sees the sp_ prefix. However, if you're creating "system" stored procedures that should be callable from all databases, and which are created in master, then the sp_ prefix might make sense...

There are really no best practices I know of, that apply only to stored procedures in master. They follow the same basic rules as any other stored procedure. Note that you can't create objects in (or even access) the resource database -- it is hidden so that only the query engine can access it.

--
Adam Machanic
Pro SQL Server 2005, available now
http://www..apress.com/book/bookDisplay.html?bID=457
--

|||Just in addition to Adam, the performance hit will be caused from the Cache Miss that is produced if the procedures takes the sp_ prefix, a schema lock on the procedure and a recompilation of the procedure.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de
|||Thank you for your answers.
As only procs with prefix 'sp_' stored in master are callable from all other databases I assume that that is the right way to write my own "system" procedures.
But I'm not very happy with that approach. I don't like to store ny own procs and tables in the master database because master is a central database for the whole server.

Wolfgang|||

You would have to define "system stored procedure" first. Very particularly, a system stored procedure has a prefix of sp_, is written by Microsoft, shipped with the product, and has special rules for name resolution.

I believe you are asking about "administrative stored procedures", these are procedures that you write that perform actions you need against one or more databases within a SQL Server. For these, I use an administrative database on the instance. I usually call it admin. In that database, I put all of my administrative procs along with any supporting tables, views, functions, etc. Any procedure can be called from any database, you just have to fully qualify the procedure.

|||OK, then talk about "administrative stored procedures".
Unfortunatly your approach doesn't fit my requirements. When I execute in a database kunktest1 'admin.dbo.kunkproc' I'm in the context of database admin and not in the kontext of database kunktest1. But I need to be in the kontext of the database from where I call the "administrative stored procedure" to select the objects of that database for example.

Wolfgang|||Normally you will be able to access the tables in the other databases using the three part names of the objects, like DatabaseName.OwnerOrSchema.Objectname. If this is not feasible for you and you really need the context of the database I would suggest generating a procedure in each database customized for each database.

HTH, Jens Suessmeyer:

http://www.sqlserver2005.de

Best practice analyser tool installation

I installed a sql server best practice analyser tool on
our new windows 2003 server.
I log in as my own username which is a system
admininstrator.
After installation, I found the program is only available
by logging using my own username, if login using other
admin username or other username, I cannot see the program.
Even when I move the shortcut to all users, it gives
message:
This action is only available to currently installed
program
And during installation, it didn't ask to intall for all
users or current users.
How can I make the program available to all users?
Thanks
I think this should work. Try the following from the other (non-setup)
accounts.
regasm.exe $(O)\bpacommon.dll /tlb
regasm.exe $(O)\bpaclient.dll /tlb
- Christian
___________________________
Christian Kleinerman
Program Manager, SQL Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Best practise analyser" <anonymous@.discussions.microsoft.com> wrote in
message news:0a8b01c4903a$462f62b0$a401280a@.phx.gbl...
> I installed a sql server best practice analyser tool on
> our new windows 2003 server.
> I log in as my own username which is a system
> admininstrator.
> After installation, I found the program is only available
> by logging using my own username, if login using other
> admin username or other username, I cannot see the program.
> Even when I move the shortcut to all users, it gives
> message:
> This action is only available to currently installed
> program
> And during installation, it didn't ask to intall for all
> users or current users.
> How can I make the program available to all users?
> Thanks

Best place for company info?

Say I have 4 applications which each use their own database. There are two
SQL Servers involved and the databases are split across servers like this:
SQLServer1
- BigApp1
- SmallApp1
SQLServer2
- BigApp2
- SmallApp2
The above helps balance load. If I want to store information about the
company developing these applications (such as company name, address, phone
number, etc), where should I put it? In a seperate database or in one of
the application databases?
This information will be idenpendent of any application but all applications
will reference it.
Thanks,
BrettHi
It is not clear if this is one company per application or one company for
all applications. I would add this information to each database if the
information is application specific, you could create a partitioned view if
you wish to view all values. If this is a common information, then I would
consider adding it to it's own database along with any other common/shared
information.
John
"Brett" <no@.spam.net> wrote in message
news:%23BLwNcXLFHA.724@.TK2MSFTNGP14.phx.gbl...
> Say I have 4 applications which each use their own database. There are
> two SQL Servers involved and the databases are split across servers like
> this:
> SQLServer1
> - BigApp1
> - SmallApp1
> SQLServer2
> - BigApp2
> - SmallApp2
> The above helps balance load. If I want to store information about the
> company developing these applications (such as company name, address,
> phone number, etc), where should I put it? In a seperate database or in
> one of the application databases?
> This information will be idenpendent of any application but all
> applications will reference it.
> Thanks,
> Brett
>|||One company owns all four applications. It is the parent of these four
apps. I thought about putting the company info in its own database but that
creates additional connections and use of resources right?
Each of the four databases has its own website. The websites run CFMX,
which creates a connection to its respective database. If the company info
is in its on database in either SQLServer1 or SQLServer2, would that create
more connections?
If not, which server should that database go on - SQLServer1 or SQLServer2,
with respect to load balancing since all four websites will be accessing the
company database (displaying company info on certain pages of the website
for example). It can eleviate some of the load problem by storing the
company info in an application variable.
Thanks,
Brett
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
> Hi
> It is not clear if this is one company per application or one company for
> all applications. I would add this information to each database if the
> information is application specific, you could create a partitioned view
> if you wish to view all values. If this is a common information, then I
> would consider adding it to it's own database along with any other
> common/shared information.
> John
>
> "Brett" <no@.spam.net> wrote in message
> news:%23BLwNcXLFHA.724@.TK2MSFTNGP14.phx.gbl...
>|||How much info are you talking about? And how often is it updated? By
putting the same duplicate info in separate databases, you make updates to
that info a little trickier. Are you using Replication to keep the
databases in sync? If so, then it would seem you could put it on both and
that would handle most of the update issues. Of course, as you point out,
if there's only a couple of lines of information, it might make more sense
to put it in application variables and skip the DB overhead altogether...
"Brett" <no@.spam.net> wrote in message
news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> One company owns all four applications. It is the parent of these four
> apps. I thought about putting the company info in its own database but
> that creates additional connections and use of resources right?
> Each of the four databases has its own website. The websites run CFMX,
> which creates a connection to its respective database. If the company
> info is in its on database in either SQLServer1 or SQLServer2, would that
> create more connections?
> If not, which server should that database go on - SQLServer1 or
> SQLServer2, with respect to load balancing since all four websites will be
> accessing the company database (displaying company info on certain pages
> of the website for example). It can eleviate some of the load problem by
> storing the company info in an application variable.
> Thanks,
> Brett
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||The info won't be much but may grow. It will be items such as:
- Company name
- address
- tel/fax numbers
- maybe company employees and their details
- may a map URL
and what ever else.
The info won't be updated often. Maybe 5 times/mo or so. Logically it is
completely independent of any application or database. It will need to be
in a database. Just, do I want to put it inside of an existing application
database or its own?
The application variables for each app will still need to get the info from
somewhere. I don't want them hard coded. That's why I'd like the database
setup. One change, one place.
Any application will hit the company info DB maybe once/w to get updates.
Little overhead actually.
Suggestions?
Thanks,
Brett
"Michael C#" <xyz@.abcdef.com> wrote in message
news:zEk%d.1527$BX1.327@.fe08.lga...
> How much info are you talking about? And how often is it updated? By
> putting the same duplicate info in separate databases, you make updates to
> that info a little trickier. Are you using Replication to keep the
> databases in sync? If so, then it would seem you could put it on both and
> that would handle most of the update issues. Of course, as you point out,
> if there's only a couple of lines of information, it might make more sense
> to put it in application variables and skip the DB overhead altogether...
> "Brett" <no@.spam.net> wrote in message
> news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
>|||Sounds like you're leaning toward putting it in its own database. That
being the case, unless it's specifically tied to the data stored in one of
your current databases, it makes sense to create a separate database for it.
And if you decide you want to access the data from within your other
database, just fully qualify the table names in your queries. It also
sounds like the information (company employees/details, etc.) could be
subject to a some growth/changes.
"Brett" <no@.spam.net> wrote in message
news:OcgM$zYLFHA.244@.TK2MSFTNGP12.phx.gbl...
> The info won't be much but may grow. It will be items such as:
> - Company name
> - address
> - tel/fax numbers
> - maybe company employees and their details
> - may a map URL
> and what ever else.
> The info won't be updated often. Maybe 5 times/mo or so. Logically it is
> completely independent of any application or database. It will need to be
> in a database. Just, do I want to put it inside of an existing
> application database or its own?
> The application variables for each app will still need to get the info
> from somewhere. I don't want them hard coded. That's why I'd like the
> database setup. One change, one place.
> Any application will hit the company info DB maybe once/w to get
> updates. Little overhead actually.
> Suggestions?
> Thanks,
> Brett
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:zEk%d.1527$BX1.327@.fe08.lga...
>|||Hi
"Brett" <no@.spam.net> wrote in message
news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> One company owns all four applications. It is the parent of these four
> apps. I thought about putting the company info in its own database but
> that creates additional connections and use of resources right?
You can always create a view.
> Each of the four databases has its own website. The websites run CFMX,
> which creates a connection to its respective database. If the company
> info is in its on database in either SQLServer1 or SQLServer2, would that
> create more connections?
> If not, which server should that database go on - SQLServer1 or
> SQLServer2, with respect to load balancing since all four websites will be
> accessing the company database (displaying company info on certain pages
> of the website for example). It can eleviate some of the load problem by
> storing the company info in an application variable.
This would depend on how/when it is updated and how/how often it is used and
if you always have to display the current information that is in the table.
You may want to consider using replication as suggested by Michael.
John
> Thanks,
> Brett
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:uyDwXBYLFHA.1172@.TK2MSFTNGP12.phx.gbl...
>|||Yes - this all true.
If the company DB is on a server seperate from a DB that wish to call data
from the company DB, how do I fully qualify the company DB table path in say
a SELECT?
Thanks,
Brett
"Michael C#" <xyz@.abcdef.com> wrote in message
news:0ml%d.11715$xQ5.2876@.fe10.lga...
> Sounds like you're leaning toward putting it in its own database. That
> being the case, unless it's specifically tied to the data stored in one of
> your current databases, it makes sense to create a separate database for
> it. And if you decide you want to access the data from within your other
> database, just fully qualify the table names in your queries. It also
> sounds like the information (company employees/details, etc.) could be
> subject to a some growth/changes.
> "Brett" <no@.spam.net> wrote in message
> news:OcgM$zYLFHA.244@.TK2MSFTNGP12.phx.gbl...
>|||"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:Ol5aFBZLFHA.576@.TK2MSFTNGP15.phx.gbl...
> Hi
> "Brett" <no@.spam.net> wrote in message
> news:%23ya4AJYLFHA.568@.TK2MSFTNGP09.phx.gbl...
> You can always create a view.
> This would depend on how/when it is updated and how/how often it is used
> and if you always have to display the current information that is in the
> table. You may want to consider using replication as suggested by Michael.
Wouldn't replication cause more overhead than the once/w query from each
application?
Thanks,
Brett|||On a separate server? You're jumping around a little bit here. To answer
your question, look up sp_addlinkedserver in BOL.
"Brett" <no@.spam.net> wrote in message
news:O7sbMRZLFHA.3356@.TK2MSFTNGP12.phx.gbl...
> Yes - this all true.
> If the company DB is on a server seperate from a DB that wish to call data
> from the company DB, how do I fully qualify the company DB table path in
> say a SELECT?
> Thanks,
> Brett
> "Michael C#" <xyz@.abcdef.com> wrote in message
> news:0ml%d.11715$xQ5.2876@.fe10.lga...
>