BEFORE Triggers: access

Showing posts with label access. Show all posts

Tuesday, March 27, 2012

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
>

Sunday, March 25, 2012

Best Replication Method to Use

We want to allow our customer base to be able to access their account
information online. I want to setup a second SQL server so the customers can
use this for looks up. The front end to access this info is web based.
What replication method is the best one to use to update the database say
every 24 hours at night? Thanks!!
if your database is not too large, a snapshot replication maybe best for
you.
else somekind of logshipping will be good too, see the other thread on
simple log shipping.
justin
Scopus69 wrote:
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||I think transactional replication would work for this. However this will
require each table you are replicating to have a primary key.
I am a little confused by the data flow. Are you saying data moves from the
web server SQL Server database to another SQL Server? Or is it moving
internally to the SQL Server supporting the web site.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
> We want to allow our customer base to be able to access their account
> information online. I want to setup a second SQL server so the customers
> can
> use this for looks up. The front end to access this info is web based.
> What replication method is the best one to use to update the database say
> every 24 hours at night? Thanks!!
|||Sorry for the confusion. The GUI interface to the data is a web interface
that connects to the backend SQL server. What I would like to do is setup
another web & SQL server for our cutomers so they can use it for lookups. I
really don't want them in our prduction DB.
I was wondering what is the best way to get the data off the production SQL
server to the customer SQL server on a nightly basis? I don't think log
shipping will work because it will put the shipped DB in "read only"
So what method would be the best to use? Thanks!
"Hilary Cotter" wrote:

> I think transactional replication would work for this. However this will
> require each table you are replicating to have a primary key.
> I am a little confused by the data flow. Are you saying data moves from the
> web server SQL Server database to another SQL Server? Or is it moving
> internally to the SQL Server supporting the web site.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:3D5CC7EA-4702-417E-AE2D-9985B1E4B781@.microsoft.com...
>
>
|||I think transactional is your best bet.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Scopus69" <Scopus69@.nospam.postalias> wrote in message
news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...[vbcol=seagreen]
> Sorry for the confusion. The GUI interface to the data is a web interface
> that connects to the backend SQL server. What I would like to do is
> setup
> another web & SQL server for our cutomers so they can use it for lookups.
> I
> really don't want them in our prduction DB.
> I was wondering what is the best way to get the data off the production
> SQL
> server to the customer SQL server on a nightly basis? I don't think log
> shipping will work because it will put the shipped DB in "read only"
> So what method would be the best to use? Thanks!
> "Hilary Cotter" wrote:
|||I also like Transactional Replication if the data is dynamic at the source
and the users who will be talking to your target server need updated
information as well for their lookups. If current data is not an issue, that
is they don't mind the data being static, then may be snapshot will work.
But then again it depends on how large the data is. For me one way
Transactional seems to fit the bill here.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:enpFYKCDGHA.1028@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> I think transactional is your best bet.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Scopus69" <Scopus69@.nospam.postalias> wrote in message
> news:76F950CE-B1EA-493F-8092-5ED6AA73EC75@.microsoft.com...
interface[vbcol=seagreen]
lookups.[vbcol=seagreen]
will[vbcol=seagreen]
based.
>
sql

Best Practise For Updates between Access 2000 and SQL Server

Hi,
Let me paint a picture...Access 2000 frontend, linking to a SQL Server
backend, inherited database, me - fairly new at this! :o)
The developer has, for better or for worse, (I'm not sure), designed a
frontend that grabs a set of order records from SQL and pulls them into
a form. While the records are there, the user can allocate the order to
a person by selecting their name, we would only be talking about 300
records at a time. When the user closes the relevant form, the orders
that have been updated in the frontend are sent to the SQL-Server.
Now, from what I can make of the code that Updates back to SQL, the
developer initially grabs a recordset of those orders in the Frontend
requiring an update and then loops through them one by one and updates
the SQL-Server record using a field call OrderID.
My questions are: Is this the only method of doing this? Meaning -
surely there is a way to do a bulk Update between the two applications
that doesn't require cycling through records.
And what is considered best practise for doing bulk record updates
between Access and SQL-Server? I could probaly re-invent the wheel
myself, but if anyone can point me in the right direction it would be
appreciated.
Kind RegardsThis is not the right forum.
--
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:

> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> The developer has, for better or for worse, (I'm not sure), designed a
> frontend that grabs a set of order records from SQL and pulls them into
> a form. While the records are there, the user can allocate the order to
> a person by selecting their name, we would only be talking about 300
> records at a time. When the user closes the relevant form, the orders
> that have been updated in the frontend are sent to the SQL-Server.
>
> Now, from what I can make of the code that Updates back to SQL, the
> developer initially grabs a recordset of those orders in the Frontend
> requiring an update and then loops through them one by one and updates
> the SQL-Server record using a field call OrderID.
>
> My questions are: Is this the only method of doing this? Meaning -
> surely there is a way to do a bulk Update between the two applications
> that doesn't require cycling through records.
>
> And what is considered best practise for doing bulk record updates
> between Access and SQL-Server? I could probaly re-invent the wheel
> myself, but if anyone can point me in the right direction it would be
> appreciated.
>
> Kind Regards
>|||Petep
This is the right forum for you. There is a seperate forum for acceess/sql
integration post there.
What you described is not the right method. probably they are using mdb not
adp.
SQL SERVER integrates well with access in adp mode. you can do set based
operations as you do in sql. In this mode data will completely reside in sql
.
so you can write spocs/functions what not, everything in sql.
Regards
R.D
--Knowledge gets doubled when shared
"PeteP" wrote:

> Hi,
>
> Let me paint a picture...Access 2000 frontend, linking to a SQL Server
> backend, inherited database, me - fairly new at this! :o)
>
> The developer has, for better or for worse, (I'm not sure), designed a
> frontend that grabs a set of order records from SQL and pulls them into
> a form. While the records are there, the user can allocate the order to
> a person by selecting their name, we would only be talking about 300
> records at a time. When the user closes the relevant form, the orders
> that have been updated in the frontend are sent to the SQL-Server.
>
> Now, from what I can make of the code that Updates back to SQL, the
> developer initially grabs a recordset of those orders in the Frontend
> requiring an update and then loops through them one by one and updates
> the SQL-Server record using a field call OrderID.
>
> My questions are: Is this the only method of doing this? Meaning -
> surely there is a way to do a bulk Update between the two applications
> that doesn't require cycling through records.
>
> And what is considered best practise for doing bulk record updates
> between Access and SQL-Server? I could probaly re-invent the wheel
> myself, but if anyone can point me in the right direction it would be
> appreciated.
>
> Kind Regards
>

Thursday, March 22, 2012

best practices stored procedures and triggers

--
Dan D.
"Hugo Kornelis" wrote:

> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
>
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
>
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fir
e
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:

> The trigger they wrote also contained a rookie mistake. It was designed fo
r
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This co
de
> will raise an error if there is more than one row in the Inserted table (i
n
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or inser
t
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in the
ir
> procs. If you do put it in your procs, you will need to put that logic int
o
> every proc that will update or insert into that table. If this is a large
#
> of procs then you are setting up a shotgun surgery approach to maintenance
.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want t
he
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...[vbcol=seagreen]
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under contro
l.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, th
e
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:

> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
>
>

best practices stored procedures and triggers

Using SS2000 SP4. We have a .NET app that accesses the sql server database.
I've tried to keep all access via stored procedures and views and the user
only has execute permissions on the sp and select on the views.
The contractors have put a trigger on one of the tables.
CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
FOR INSERT, UPDATE
AS
IF (Select bitCompPrimaryContact From Inserted) = 1
Begin
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End
So, the user needs select and update permissions on the table. Since I
didn't want to give insert, update or delete permissions on any tables to the
user is there a way around it? What is a best practice approach. Do I just
not use update triggers?
Thanks,
--
Dan D.As long as you are using stored procedures you do not need to use this
trigger. Tell the contractors to put this code within the stored procedure
that updates the table tblCompanyContacts.
--
Keith Kratochvil
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> Using SS2000 SP4. We have a .NET app that accesses the sql server
> database.
> I've tried to keep all access via stored procedures and views and the user
> only has execute permissions on the sp and select on the views.
> The contractors have put a trigger on one of the tables.
> CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> FOR INSERT, UPDATE
> AS
> IF (Select bitCompPrimaryContact From Inserted) = 1
> Begin
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End
> So, the user needs select and update permissions on the table. Since I
> didn't want to give insert, update or delete permissions on any tables to
> the
> user is there a way around it? What is a best practice approach. Do I just
> not use update triggers?
> Thanks,
> --
> Dan D.|||That sounds good. Thanks Keith.
--
Dan D.
"Keith Kratochvil" wrote:
> As long as you are using stored procedures you do not need to use this
> trigger. Tell the contractors to put this code within the stored procedure
> that updates the table tblCompanyContacts.
> --
> Keith Kratochvil
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > database.
> > I've tried to keep all access via stored procedures and views and the user
> > only has execute permissions on the sp and select on the views.
> >
> > The contractors have put a trigger on one of the tables.
> >
> > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > FOR INSERT, UPDATE
> > AS
> > IF (Select bitCompPrimaryContact From Inserted) = 1
> > Begin
> > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > Where numCompanyId = (Select numCompanyId From Inserted)
> > And numContactID <> (Select numContactId From Inserted)
> > End
> >
> > So, the user needs select and update permissions on the table. Since I
> > didn't want to give insert, update or delete permissions on any tables to
> > the
> > user is there a way around it? What is a best practice approach. Do I just
> > not use update triggers?
> >
> > Thanks,
> > --
> > Dan D.
>
>|||I don't believe you don't need to grant any additional permissions on
the base table to users to execute a trigger. The "grant execute" on
the update or insert stored procedures should be enough.
Dan D. wrote:
> That sounds good. Thanks Keith.
> --
> Dan D.
>
> "Keith Kratochvil" wrote:
> > As long as you are using stored procedures you do not need to use this
> > trigger. Tell the contractors to put this code within the stored procedure
> > that updates the table tblCompanyContacts.
> >
> > --
> > Keith Kratochvil
> >
> >
> > "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> > news:D9A15C06-49C3-4F1A-8EB1-884159C0184F@.microsoft.com...
> > > Using SS2000 SP4. We have a .NET app that accesses the sql server
> > > database.
> > > I've tried to keep all access via stored procedures and views and the user
> > > only has execute permissions on the sp and select on the views.
> > >
> > > The contractors have put a trigger on one of the tables.
> > >
> > > CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> > > FOR INSERT, UPDATE
> > > AS
> > > IF (Select bitCompPrimaryContact From Inserted) = 1
> > > Begin
> > > Update tblCompanyContacts Set bitCompPrimaryContact = 0
> > > Where numCompanyId = (Select numCompanyId From Inserted)
> > > And numContactID <> (Select numContactId From Inserted)
> > > End
> > >
> > > So, the user needs select and update permissions on the table. Since I
> > > didn't want to give insert, update or delete permissions on any tables to
> > > the
> > > user is there a way around it? What is a best practice approach. Do I just
> > > not use update triggers?
> > >
> > > Thanks,
> > > --
> > > Dan D.
> >
> >
> >|||On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
>Using SS2000 SP4. We have a .NET app that accesses the sql server database.
>I've tried to keep all access via stored procedures and views and the user
>only has execute permissions on the sp and select on the views.
>The contractors have put a trigger on one of the tables.
>CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
>FOR INSERT, UPDATE
>AS
>IF (Select bitCompPrimaryContact From Inserted) = 1
>Begin
>Update tblCompanyContacts Set bitCompPrimaryContact = 0
>Where numCompanyId = (Select numCompanyId From Inserted)
>And numContactID <> (Select numContactId From Inserted)
>End
Hi Dan,
First, fire the contractors. This trigger will fail on any multi-row
insert or update. No-one should write triggers like that and be paid for
it!
>So, the user needs select and update permissions on the table.
Says who? As long as the table is owned by the same user that owns the
trigger, the user doesn't need any permissions on the table - see the
repro script below.
> Since I
>didn't want to give insert, update or delete permissions on any tables to the
>user is there a way around it? What is a best practice approach. Do I just
>not use update triggers?
In most cases, I would recommend having all objects (tables, views,
stored procedures, triggers, functions, ...) owned by dbo. Then, you can
set permissions exactly as required. Due to all objects having the same
owner, all statements from within stored procedures, triggers, and
functions will work. (Unless you're using dynamic SQL).
Here's a repro to show that a user doesn't need permission on the table
if a trigger changes data in the table.
First, run the script below while connected with an accoount with
sufficient rights to add databases and logins:
USE master
go
DROP DATABASE test
go
sp_droplogin @.loginame = 'test'
go
CREATE DATABASE test
go
sp_addlogin @.loginame = 'test',
@.passwd = 'test',
@.defdb = 'test'
go
USE test
go
sp_grantdbaccess @.loginame = 'test'
go
CREATE TABLE tab (a int NOT NULL,
b int DEFAULT NULL)
go
CREATE VIEW vw
AS
SELECT a, b
FROM tab
go
CREATE TRIGGER trig
ON tab
FOR INSERT, UPDATE
AS
IF @.@.ROWCOUNT > 0
BEGIN
UPDATE tab
SET b = a + 15
WHERE b IS NULL
AND EXISTS
(SELECT *
FROM inserted
WHERE inserted.a = tab.a)
END
go
CREATE PROC ins_tab (@.a int)
AS
INSERT INTO tab (a)
VALUES (@.a)
go
DENY ALL ON tab TO test
DENY UPDATE, INSERT, DELETE ON vw TO test
GRANT SELECT ON vw TO test
GRANT EXECUTE ON ins_tab TO test
go
Then, open a new connection, log in with userid 'test' (use SQL Server
authentication, not integrated authentication) and execute the code
below:
-- Login as user 'test'
-- Fails - no permissions on tab
SELECT *
FROM tab
-- Succeeds - SELECT permission on vw granted
SELECT *
FROM vw
-- Fails - no INSERT permission on vw
INSERT INTO vw (a)
VALUES (3)
-- Succees - EXECUTE permission on ins_tab granted
EXECUTE ins_tab @.a = 3
-- Check that trigger has executed as well
SELECT *
FROM vw
go
Here's the output:
Msg 229, Level 14, State 5, Line 1
SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
a b
-- --
Msg 229, Level 14, State 5, Line 1
INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
a b
-- --
3 18
Hugo Kornelis, SQL Server MVP|||I see it now. Thanks for such a detailed explanation Hugo.
BTW, we did fire the contractors.:)
--
Dan D.
"Hugo Kornelis" wrote:
> On Mon, 22 May 2006 06:52:02 -0700, Dan D. wrote:
> >Using SS2000 SP4. We have a .NET app that accesses the sql server database.
> >I've tried to keep all access via stored procedures and views and the user
> >only has execute permissions on the sp and select on the views.
> >
> >The contractors have put a trigger on one of the tables.
> >
> >CREATE TRIGGER [SetPrimaryContact_Company] ON [dbo].[tblCompanyContacts]
> >FOR INSERT, UPDATE
> >AS
> >IF (Select bitCompPrimaryContact From Inserted) = 1
> >Begin
> >Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >Where numCompanyId = (Select numCompanyId From Inserted)
> >And numContactID <> (Select numContactId From Inserted)
> >End
> Hi Dan,
> First, fire the contractors. This trigger will fail on any multi-row
> insert or update. No-one should write triggers like that and be paid for
> it!
> >So, the user needs select and update permissions on the table.
> Says who? As long as the table is owned by the same user that owns the
> trigger, the user doesn't need any permissions on the table - see the
> repro script below.
> > Since I
> >didn't want to give insert, update or delete permissions on any tables to the
> >user is there a way around it? What is a best practice approach. Do I just
> >not use update triggers?
> In most cases, I would recommend having all objects (tables, views,
> stored procedures, triggers, functions, ...) owned by dbo. Then, you can
> set permissions exactly as required. Due to all objects having the same
> owner, all statements from within stored procedures, triggers, and
> functions will work. (Unless you're using dynamic SQL).
> Here's a repro to show that a user doesn't need permission on the table
> if a trigger changes data in the table.
> First, run the script below while connected with an accoount with
> sufficient rights to add databases and logins:
> USE master
> go
> DROP DATABASE test
> go
> sp_droplogin @.loginame = 'test'
> go
> CREATE DATABASE test
> go
> sp_addlogin @.loginame = 'test',
> @.passwd = 'test',
> @.defdb = 'test'
> go
> USE test
> go
> sp_grantdbaccess @.loginame = 'test'
> go
> CREATE TABLE tab (a int NOT NULL,
> b int DEFAULT NULL)
> go
> CREATE VIEW vw
> AS
> SELECT a, b
> FROM tab
> go
> CREATE TRIGGER trig
> ON tab
> FOR INSERT, UPDATE
> AS
> IF @.@.ROWCOUNT > 0
> BEGIN
> UPDATE tab
> SET b = a + 15
> WHERE b IS NULL
> AND EXISTS
> (SELECT *
> FROM inserted
> WHERE inserted.a = tab.a)
> END
> go
> CREATE PROC ins_tab (@.a int)
> AS
> INSERT INTO tab (a)
> VALUES (@.a)
> go
> DENY ALL ON tab TO test
> DENY UPDATE, INSERT, DELETE ON vw TO test
> GRANT SELECT ON vw TO test
> GRANT EXECUTE ON ins_tab TO test
> go
> Then, open a new connection, log in with userid 'test' (use SQL Server
> authentication, not integrated authentication) and execute the code
> below:
> -- Login as user 'test'
> -- Fails - no permissions on tab
> SELECT *
> FROM tab
> -- Succeeds - SELECT permission on vw granted
> SELECT *
> FROM vw
> -- Fails - no INSERT permission on vw
> INSERT INTO vw (a)
> VALUES (3)
> -- Succees - EXECUTE permission on ins_tab granted
> EXECUTE ins_tab @.a = 3
> -- Check that trigger has executed as well
> SELECT *
> FROM vw
> go
> Here's the output:
> Msg 229, Level 14, State 5, Line 1
> SELECT permission denied on object 'tab', database 'test', owner 'dbo'.
> a b
> -- --
> Msg 229, Level 14, State 5, Line 1
> INSERT permission denied on object 'vw', database 'test', owner 'dbo'.
> a b
> -- --
> 3 18
>
> --
> Hugo Kornelis, SQL Server MVP
>|||The trigger they wrote also contained a rookie mistake. It was designed for
a single row at a time. When you are writing a trigger you always need to
plan on having multi row events occur at some point.
Snip--
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId = (Select numCompanyId From Inserted)
And numContactID <> (Select numContactId From Inserted)
End snip--
See how the subqueries assume that only one item will be returned. This code
will raise an error if there is more than one row in the Inserted table (in
other words, if a multi row update or insert occurs) and that would cause
the original transaction to be rolled back.
Far better would be the following:
---
Update tblCompanyContacts Set bitCompPrimaryContact = 0
FROM tblCompanyContacts C
JOIN Inserted I
ON I.numCompanyId = C.numCompanyId
And I.numContactID <> C.numContactId
---
This way no matter how many rows have been affected by the update or insert
statement the trigger will now handle them.
All of the advice you received on moving the logic to the stored procedure
has merit. You should understand the following. The trigger will fire for
any update or insert on your table. Some folks like to use triggers to
handle some of their data integrity enforcement and not worry about in their
procs. If you do put it in your procs, you will need to put that logic into
every proc that will update or insert into that table. If this is a large #
of procs then you are setting up a shotgun surgery approach to maintenance.
The other thing to be aware of is that your trigger fires as part of your
original transaction, so an error in it will cause your original action to
be rolled back, as well as the locks from your original action are still
maintained during the trigger. In this case that is not a bad thing, since
you want this to happen as part of the update, and if it fails, you want the
update to fail as well.
--
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com|||So, is it probably safer to stay away from subqueries in triggers?
One of the things we have to be careful about with triggers is that we do a
lot of data manipulation on the backend. So we have to be sure that if we
insert 5M rows on the backend and not through the app, a trigger doesn't fire
and do something it shouldn't.
Thanks,
--
Dan D.
"David Lundell" wrote:
> The trigger they wrote also contained a rookie mistake. It was designed for
> a single row at a time. When you are writing a trigger you always need to
> plan on having multi row events occur at some point.
> Snip--
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId = (Select numCompanyId From Inserted)
> And numContactID <> (Select numContactId From Inserted)
> End snip--
> See how the subqueries assume that only one item will be returned. This code
> will raise an error if there is more than one row in the Inserted table (in
> other words, if a multi row update or insert occurs) and that would cause
> the original transaction to be rolled back.
> Far better would be the following:
> ---
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> FROM tblCompanyContacts C
> JOIN Inserted I
> ON I.numCompanyId = C.numCompanyId
> And I.numContactID <> C.numContactId
> ---
> This way no matter how many rows have been affected by the update or insert
> statement the trigger will now handle them.
> All of the advice you received on moving the logic to the stored procedure
> has merit. You should understand the following. The trigger will fire for
> any update or insert on your table. Some folks like to use triggers to
> handle some of their data integrity enforcement and not worry about in their
> procs. If you do put it in your procs, you will need to put that logic into
> every proc that will update or insert into that table. If this is a large #
> of procs then you are setting up a shotgun surgery approach to maintenance.
> The other thing to be aware of is that your trigger fires as part of your
> original transaction, so an error in it will cause your original action to
> be rolled back, as well as the locks from your original action are still
> maintained during the trigger. In this case that is not a bad thing, since
> you want this to happen as part of the update, and if it fails, you want the
> update to fail as well.
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
>
>|||Subqueries are not the issue. It is the way the subqueries were written
Update tblCompanyContacts Set bitCompPrimaryContact = 0
Where numCompanyId IN(Select numCompanyId From Inserted)
And numContactID NOT IN (Select numContactId From Inserted)
This would also work, although it might not be as efficient as my earlier
example.
As for backend loading. I usually disable my triggers for the load and
reenable them afterwards. But only if I make certain to perform what my
trigger would perform. In this case setting the bitCompPrimaryContact = 0.
ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
Do the load
ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
David Lundell
Principal Consultant and Trainer
www.MutuallyBeneficial.com
David@.MutuallyBeneficial.com
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> So, is it probably safer to stay away from subqueries in triggers?
> One of the things we have to be careful about with triggers is that we do
> a
> lot of data manipulation on the backend. So we have to be sure that if we
> insert 5M rows on the backend and not through the app, a trigger doesn't
> fire
> and do something it shouldn't.
> Thanks,
> --
> Dan D.
>
> "David Lundell" wrote:
>> The trigger they wrote also contained a rookie mistake. It was designed
>> for
>> a single row at a time. When you are writing a trigger you always need to
>> plan on having multi row events occur at some point.
>> Snip--
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> Where numCompanyId = (Select numCompanyId From Inserted)
>> And numContactID <> (Select numContactId From Inserted)
>> End snip--
>> See how the subqueries assume that only one item will be returned. This
>> code
>> will raise an error if there is more than one row in the Inserted table
>> (in
>> other words, if a multi row update or insert occurs) and that would cause
>> the original transaction to be rolled back.
>> Far better would be the following:
>> ---
>> Update tblCompanyContacts Set bitCompPrimaryContact = 0
>> FROM tblCompanyContacts C
>> JOIN Inserted I
>> ON I.numCompanyId = C.numCompanyId
>> And I.numContactID <> C.numContactId
>> ---
>> This way no matter how many rows have been affected by the update or
>> insert
>> statement the trigger will now handle them.
>> All of the advice you received on moving the logic to the stored
>> procedure
>> has merit. You should understand the following. The trigger will fire for
>> any update or insert on your table. Some folks like to use triggers to
>> handle some of their data integrity enforcement and not worry about in
>> their
>> procs. If you do put it in your procs, you will need to put that logic
>> into
>> every proc that will update or insert into that table. If this is a large
>> #
>> of procs then you are setting up a shotgun surgery approach to
>> maintenance.
>> The other thing to be aware of is that your trigger fires as part of your
>> original transaction, so an error in it will cause your original action
>> to
>> be rolled back, as well as the locks from your original action are still
>> maintained during the trigger. In this case that is not a bad thing,
>> since
>> you want this to happen as part of the update, and if it fails, you want
>> the
>> update to fail as well.
>> --
>> David Lundell
>> Principal Consultant and Trainer
>> www.MutuallyBeneficial.com
>> David@.MutuallyBeneficial.com
>>|||We're still working on a solution. We have many clients that we do mailings
for and we have hundreds of databases. I'm not sure it's the best way to try
and keep track of every trigger on every table.
We're an example of a small company that grew very fast without any
standards or best practices. Now we're trying to get everything under control.
We recently had a case where a developer put a trigger on a table for an
application and didn't tell anyone about it. Adn to compound the problem, the
trigger didn't work like he thought it would. I guess he didn't test it
thoroughtly. When someone loaded some new data on the backend, the trigger
fired and updated 150,000+ rows that it shouldn't have. We didn't discovered
it until months later and we're trying to figure out how to roll it back.
Thanks for all of your help,
--
Dan D.
"David Lundell" wrote:
> Subqueries are not the issue. It is the way the subqueries were written
> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> Where numCompanyId IN(Select numCompanyId From Inserted)
> And numContactID NOT IN (Select numContactId From Inserted)
> This would also work, although it might not be as efficient as my earlier
> example.
> As for backend loading. I usually disable my triggers for the load and
> reenable them afterwards. But only if I make certain to perform what my
> trigger would perform. In this case setting the bitCompPrimaryContact = 0.
> ALTER TABLE dbo.Widget DISABLE TRIGGER ALL
> Do the load
> ALTER TABLE dbo.Widget ENABLE TRIGGER ALL
>
> --
> David Lundell
> Principal Consultant and Trainer
> www.MutuallyBeneficial.com
> David@.MutuallyBeneficial.com
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:21CA8851-BF8B-4DAE-92AB-BBC85A239E11@.microsoft.com...
> > So, is it probably safer to stay away from subqueries in triggers?
> >
> > One of the things we have to be careful about with triggers is that we do
> > a
> > lot of data manipulation on the backend. So we have to be sure that if we
> > insert 5M rows on the backend and not through the app, a trigger doesn't
> > fire
> > and do something it shouldn't.
> >
> > Thanks,
> > --
> > Dan D.
> >
> >
> > "David Lundell" wrote:
> >
> >> The trigger they wrote also contained a rookie mistake. It was designed
> >> for
> >> a single row at a time. When you are writing a trigger you always need to
> >> plan on having multi row events occur at some point.
> >>
> >> Snip--
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> Where numCompanyId = (Select numCompanyId From Inserted)
> >> And numContactID <> (Select numContactId From Inserted)
> >> End snip--
> >>
> >> See how the subqueries assume that only one item will be returned. This
> >> code
> >> will raise an error if there is more than one row in the Inserted table
> >> (in
> >> other words, if a multi row update or insert occurs) and that would cause
> >> the original transaction to be rolled back.
> >>
> >> Far better would be the following:
> >> ---
> >> Update tblCompanyContacts Set bitCompPrimaryContact = 0
> >> FROM tblCompanyContacts C
> >> JOIN Inserted I
> >> ON I.numCompanyId = C.numCompanyId
> >> And I.numContactID <> C.numContactId
> >> ---
> >> This way no matter how many rows have been affected by the update or
> >> insert
> >> statement the trigger will now handle them.
> >>
> >> All of the advice you received on moving the logic to the stored
> >> procedure
> >> has merit. You should understand the following. The trigger will fire for
> >> any update or insert on your table. Some folks like to use triggers to
> >> handle some of their data integrity enforcement and not worry about in
> >> their
> >> procs. If you do put it in your procs, you will need to put that logic
> >> into
> >> every proc that will update or insert into that table. If this is a large
> >> #
> >> of procs then you are setting up a shotgun surgery approach to
> >> maintenance.
> >> The other thing to be aware of is that your trigger fires as part of your
> >> original transaction, so an error in it will cause your original action
> >> to
> >> be rolled back, as well as the locks from your original action are still
> >> maintained during the trigger. In this case that is not a bad thing,
> >> since
> >> you want this to happen as part of the update, and if it fails, you want
> >> the
> >> update to fail as well.
> >> --
> >> David Lundell
> >> Principal Consultant and Trainer
> >> www.MutuallyBeneficial.com
> >> David@.MutuallyBeneficial.com
> >>
> >>
> >>
>
>|||On Tue, 23 May 2006 06:07:02 -0700, Dan D. wrote:
>So, is it probably safer to stay away from subqueries in triggers?
Hi Dan,
Only in the same way that not using a database at all prevents errors :)
Use subqueries if you need them - just be aware that a trigger will
always fires exactly once when a corresponding INSERT, UPDATE or DELETE
statement is executed. Design your triggers to work regardless of number
of rows affected. And test them with single-row, multi-row and zero-row
operations.
>One of the things we have to be careful about with triggers is that we do a
>lot of data manipulation on the backend. So we have to be sure that if we
>insert 5M rows on the backend and not through the app, a trigger doesn't fire
>and do something it shouldn't.
Bottom line when deciding if something belongs in a stored proc or in a
trigger is: is in integrity related?
Triggers should be used to enforce the integrity rules that can't be
enforced with standard SQL. For instance complicated constraints that
can't be exppressed in SQL constraints. Or computations that (for
whatever reason) can not be calculated in a view. The execution of a
trigger is about as hard to bypass as the checking of a constraint or
the "updating" of a calculated value in a view. That's because it should
be used for code that really should never be bypassed.
Bypassing the execution of stored procedures is much easier. Exactly how
easy depends on yoour security model as well - but there are always SOME
users able to update a table directly without executing the stored proc
that should be used to update the table.
--
Hugo Kornelis, SQL Server MVP

Best practices for SQL data access

Hi,

i am newbie in ASP.net world. i am using 3 tier application architechture for my web based application. data base is sql server 2000. i have looked at object and sql datasource objects but i think they are not suitable for my requirements. so i am planning to directly use ado.net to access data from database.( i.e. creating connection, then creating commands n executing them)

now what i am looking for is the best known practices for the above task. i have following solutions in my mind please let me know if i am missing some or which could be the best aproach.

careate one class which will handle all the database requests so that all the pages and business objects request that class to to do all the db related stuff. (creating connection, command n execution)

have a class which will return connection to your page or business object and then u can use that connection to do db related stuff.

some thing in between that you create a sqlcommand and pass it to a class which will take care of connections and execute you request.

what i am worried about more is the connections to database and the connection pooling n sharing stuff. i dont have any idea how they works.

please help me in this regard

thanks

Have you looked at the Data Access Application Block?

http://msdn2.microsoft.com/en-us/library/aa480458.aspx

|||

thanks i had a look through it, n it sound goods.

any other thoughs or ideas???

|||

aakbar:
thanks i had a look through it, n it sound goods.
any other thoughs or ideas???

Yes, follow those suggestions there... Smile

|||

Outside of that, if was using ADO, I'd create a Business Logic class for each of the tables in my database using this class to generate all of the business logic which in turn would then call the ADO function within the DAL which would be your Data Application Blocks.

Tuesday, March 20, 2012

Best Practices Database Owner, Database Connection Method (asp)

Hi-

I have a sql server database, and am wring web apps to access it.

I've created databases different ways, and ended up with different owners (eg dbo, nt authority\network services...)

I also have connection strings using windows authentication, and some using a user name and password.

I have read that using windows authentication is the best way to go, as far as security goes, but I have noticed some connectivity issues when I upload the site to the server, and test it remotely.

What is the safest 'owner' of the database, and what's the safest way to connect?

Thanks

Dan

You may get somewhat different details from different people but I think most will agree with what I'm about to say (I may live to regret those words!). Remember that the goal is give your users a little privileges as possible

owner of the database should be dbo

|||

Create a login which has an entry in your Active Directory (AD)*, and give it the needed permissions.

Map that login to a database user (name it MyAppUser), this user has only needed permisions on the database (e.g. execute stored procedures and maybe SELECTing some fields from some tables).

Use Windows Authentication if it is possible.

Encrypt your ConnectionString in your Web.Config file.

*: you can enforce some policies like password has to be strong and changed every two weeks or months. Old password can not be used and some policies that can increase the security.

Remember: Too much security doesn't always good.

Good luck.

|||

One more thing I would like to mention is try to use stored procedures ONLY as much as you can.

This will increase the performance (usually) and make your App secure (e.g. SQL Injuction).

Try to not thatMyAppUserother thatEXECstored procedures.

Insred of sending a lot of T-SQL statments over the network, you will just send the stored procedure name.. and once it is executed it will be cached (better performance for later execution).

Make you logic in the stored procedure, allow you to change the logic later -if needed- without redeploying the application or compiling it.

Good luck.

|||

OK, so stored procedures seems to be a common theme.

hodw do I best use them(SP), and use the GUI advantage of visual studio.net?

Do I write, say a SP called "SP_Update_Client()" Then have the asp.net page call

"SP_Update_Client("Param1","Param2")

and how do I get a hold of the stored procedure IN Visual studio?

thanks

dan

(Im getting lazzy in this GUI world)

|||

You don't "get hold" of a proc like you would, say, a dll. You create a sql command and attach parameters to it as in this example http://www.codeproject.com/useritems/simplecodeasp.asp

Note esp their use of output parameters to return data

|||

hummm-

I think Im starting to get it.

If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Thanks so much for the discussion an the artilce

|||

Harperator:
If I am writing a small app (500 users, connecting 10 - 25 x a week) will I notice a benifit of procs? in speed? Or is it more of a security issue at this size?

Stored Procedure = Both security + performance, but the main thing here is the security especially SQL Injuction.

Good luck.

|||

Agree with CS4Ever's statement

Best Practices Analyzer Access Denied Message

I am receiving an Access is Denied messsage when trying to run the Best
Practices Analyzer against a Named Instance of SQL using the AV and Severe
Errors,Unexpected Shutdowns,Datbase File Compression, NO_LOG backups,Error
Log File Size, and Failed Backups rules. I am receiving expected results for
all other rule sets. Any direction or help is appreciated.
If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
Practices Analyzer\log, we could analyze why you are getting this message
Thanks
Sethu
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.
"sskeen" <sskeen@.discussions.microsoft.com> wrote in message
news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>I am receiving an Access is Denied messsage when trying to run the Best
> Practices Analyzer against a Named Instance of SQL using the AV and
> Severe
> Errors,Unexpected Shutdowns,Datbase File Compression, NO_LOG backups,Error
> Log File Size, and Failed Backups rules. I am receiving expected results
> for
> all other rule sets. Any direction or help is appreciated.
|||Here is the log file information for analysis:
//--
Best Practice log file created at 3/25/2005 10:16:12 AM
Job name: test
Log file: c:\program files\microsoft sql server best practices
analyzer\log\test_clmi-vm-sqltools_0001_(local)_sqlbpa.log
//--
Preparing rule... Microsoft.SqlBpa.Rules.BpAffinityMask.BPAffinityMa sk
Getting affinity mask setting... BPAffinityMask.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression
Getting machine name and list of databases ... BpFileCompression.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpDbFileCompression.Bp FileCompression
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup
Getting current date ... BPNoLogBackup.Execute()
Getting machine name and server instance ... BPNoLogBackup.Execute()
Getting info from NT Event Log ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogB kup
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og
Getting machine and instance name... BPNoLogBackup.Execute()
Getting error log path... BPNoLogBackup.Execute()
Getting log size ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycl eErrLog
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt
Getting current date ... BPFailedBackupEvent.Execute()
Getting machine name and server instance ... BPFailedBackupEvent.Execute()
Getting info from NT Event Log ... BPFailedBackupEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFailedBackupEvents.B PFailedBackupEvt
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized
Getting machine name ... BPFTBackgroundServicesOptimized.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTBackgroundServices Optimized.BPFTBgServicesOptimized
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem
Getting machine name ... BPFTMSSearchLocalSystem.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSyste m.BPMSSearchLocalSystem
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore
Getting machine name... BPFTPropertyStore.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize. BPFTPropertyStore
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule... Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt
Getting current date, machine name, and instance name ...
BPSevereErrorEvent.Execute()
Getting info from NT Event Log ... BPSevereErrorEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
-- End of inner exception stack trace --
Server stack trace:
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BP SevereErrorEvt
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Preparing rule...
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn
Getting current date, machine name, and instance name ...
BPUnexpectedShutdown.Execute()
Getting startup and shutdown info from NT Event Log ...
BPUnexpectedShutdown.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
-- End of inner exception stack trace --
Server stack trace:
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
at Microsoft.BPA.Server.BPProxy.Execute()
at
System.Runtime.Remoting.Messaging.StackBuilderSink .PrivateProcessMessage(MethodBase
mb, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext,
Object[]& outArgs)
at
System.Runtime.Remoting.Messaging.StackBuilderSink .SyncProcessMessage(IMessage msg, Int32 methodPtr, Boolean fExecuteInContext)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage
reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData&
msgData, Int32 type)
at Microsoft.BPA.Server.BPProxy.Execute()
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
Cleaning up resources used by
rule:Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns. BPUnexpectedShutdwn
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Access is
denied.
at
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.executeJob(NameValueCollection bpInputParams, BPASQLServerInfo sqlServer)
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nnerException: Error in
the application.
at Microsoft.BPA.Server.JobCoordinatorComponent.JobRu nner.Run()
"Sethu Srinivasan [MSFT]" wrote:

> If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
> Practices Analyzer\log, we could analyze why you are getting this message
> Thanks
> Sethu
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "sskeen" <sskeen@.discussions.microsoft.com> wrote in message
> news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>
>
|||Preparing rule... Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression
Getting machine name and list of databases ... BpFileCompression.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpDbFileCompression.BpFileC ompression.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup
Getting current date ... BPNoLogBackup.Execute()
Getting machine name and server instance ... BPNoLogBackup.Execute()
Getting info from NT Event Log ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpNoLogBackup.BPNoLogBkup.E xecute()
Preparing rule... Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og
Getting machine and instance name... BPNoLogBackup.Execute()
Getting error log path... BPNoLogBackup.Execute()
Getting log size ... BPNoLogBackup.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpCycleErrorLog.BPCycleErrL og.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt
Getting current date ... BPFailedBackupEvent.Execute()
Getting machine name and server instance ... BPFailedBackupEvent.Execute()
Getting info from NT Event Log ... BPFailedBackupEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFailedBackupEvents.BPFail edBackupEvt.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized
Getting machine name ... BPFTBackgroundServicesOptimized.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTBackgroundServicesOptim ized.BPFTBgServicesOptimized.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem
Getting machine name ... BPFTMSSearchLocalSystem.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpFTMSSearchLocalSystem.BPM SSearchLocalSystem.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore
Getting machine name... BPFTPropertyStore.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpFTPropertyStoreSize.BPFTP ropertyStore.Execute()
Preparing rule... Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt
Getting current date, machine name, and instance name ...
BPSevereErrorEvent.Execute()
Getting info from NT Event Log ... BPSevereErrorEvent.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at Microsoft.SqlBpa.Rules.BpSevereErrorEvents.BPSever eErrorEvt.Execute()
Preparing rule...
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn
Getting current date, machine name, and instance name ...
BPUnexpectedShutdown.Execute()
Getting startup and shutdown info from NT Event Log ...
BPUnexpectedShutdown.Execute()
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
Microsoft.BPA.BestPractice.BPException: Access is denied. -->
System.UnauthorizedAccessException: Access is denied.
at System.Runtime.InteropServices.Marshal.ThrowExcept ionForHR(Int32
errorCode, IntPtr errorInfo)
at System.Management.ManagementScope.InitializeGuts(O bject o)
at System.Management.ManagementScope.Initialize()
at System.Management.ManagementScope.Connect()
at
Microsoft.SqlBpa.Rules.BpUnexpectedShutdowns.BPUne xpectedShutdwn.Execute()
"Sethu Srinivasan [MSFT]" wrote:

> If you can post the BPA logs from %PROGRAMFILES%\Microsoft SQL Server Best
> Practices Analyzer\log, we could analyze why you are getting this message
> Thanks
> Sethu
> --
> This posting is provided "AS IS" with no warranties, and confers no rights.
> Use of included script samples are subject to the terms specified at
> http://www.microsoft.com/info/cpyright.htm.
>
> "sskeen" <sskeen@.discussions.microsoft.com> wrote in message
> news:D5ED9CBC-67AE-487B-840A-16DFAF3F8131@.microsoft.com...
>
>

Tuesday, March 27, 2012

Best strategy for multiple sites

Sunday, March 25, 2012

Best Replication Method to Use

Best Practise For Updates between Access 2000 and SQL Server

Thursday, March 22, 2012

best practices stored procedures and triggers

best practices stored procedures and triggers

Best practices for SQL data access

Tuesday, March 20, 2012

Best Practices Database Owner, Database Connection Method (asp)

Best Practices Analyzer Access Denied Message

BEFORE Triggers

Blog Archive

About Me