Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

Can someone point me in the right direction?

Used Trigger instead of SP

http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp

thanks,Arnie Rowland

http://forums.microsoft.com/technet/ShowPost.aspx?postid=1199319&isthread=true&siteid=17&authhash=bf39638b468336d0c6fe8e12045e034125b540e1&ticks=633064241797780343

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

sql

Archive Stored Procedure

My SP looks like this that originally populates the record set.

CREATE PROCEDURE dbo.sp_insert_file

@.KBID int,
@.rev int,
@.filename nvarchar(50),
@.moddate datetime,
@.owner nvarchar(50),
@.author nvarchar(50)

As

INSERT INTO KBFile
(KBID, rev, filename, moddate, owner, author)
VALUES (@.KBID,@.rev,@.filename,@.moddate,@.owner, @.author)
GO

I need to have a stored procedure that will select a recordset based on KBID(PK) and archive the entire record set to another table. I then need to be able to UPDATE the individual records for this record set in the KBFile table.

I am trying to create an 'audit' trail while maintaining previous records.

Can someone point me in the right direction?

What about using an update trigger ? You will be able to audit all change events (not only these from stored procedures)

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Often, a TRIGGER is used for Audit trails since the TRIGGER execution cannot be avoided. Audit trails are more 'risky' when stored procedures are used.

You might find one of these references pointing to the use of TRIGGERS to be helpful:

Trigger -Audit Trail
http://www.nigelrivett.net/AuditTrailTrigger.html
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart1.asp
http://www.sqlservercentral.com/columnists/sjones/auditingyoursqlserverpart2.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create2_7eeq.asp

Architecture question

I have a couple of dozen users running about 20 reports. The reports are
running from SQL Server stored procedures on a server named CORP_DB. The
reports render quickly, even running on my development box, I've never had
anyone complain about performance. My question is this: can I use a beefy
workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
to host IIS and Reporting Services? If I do it this way should I put the two
RS databases on CORP_DB or should I put them on CORP_RS? I may be scaling up
to about 100 users and 50 to 80 reports.
Thanks,
DougSMy personal feeling is to have the RS databases local, even if the data is
on another server. The RS databases are used as an object store (all the
report definitions are stored there, as well as everything else need by RS).
SQL Server is good at how it uses memory. I am currently using a workstation
with 2 gigs of ram and 2 processors. My suggestion is to add a gig of ram if
you can (RS is pretty ram intensive) and add a processor. You might be fine
with 1 processor and 1 gig but it up if at all possible beef it up. Also,
install Windows 2003 Server Standard edition on it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>I have a couple of dozen users running about 20 reports. The reports are
>running from SQL Server stored procedures on a server named CORP_DB. The
>reports render quickly, even running on my development box, I've never had
>anyone complain about performance. My question is this: can I use a beefy
>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my server
>to host IIS and Reporting Services? If I do it this way should I put the
>two RS databases on CORP_DB or should I put them on CORP_RS? I may be
>scaling up to about 100 users and 50 to 80 reports.
> Thanks,
> DougS
>|||I have to have a sql server license for the machine that is rendering the
reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server but
it has to run on a machine by itself so you end up buying another license.
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
> My personal feeling is to have the RS databases local, even if the data is
> on another server. The RS databases are used as an object store (all the
> report definitions are stored there, as well as everything else need by
> RS). SQL Server is good at how it uses memory. I am currently using a
> workstation with 2 gigs of ram and 2 processors. My suggestion is to add a
> gig of ram if you can (RS is pretty ram intensive) and add a processor.
> You might be fine with 1 processor and 1 gig but it up if at all possible
> beef it up. Also, install Windows 2003 Server Standard edition on it.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
>
>
> "DougS" <doug@.nospam.com> wrote in message
> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never had
>>anyone complain about performance. My question is this: can I use a beefy
>>workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as my
>>server to host IIS and Reporting Services? If I do it this way should I
>>put the two RS databases on CORP_DB or should I put them on CORP_RS? I may
>>be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>|||No, you do not have to have it run on a machine by itself. I currently have
it running on the same machine as my datamart. Now, some IT departments
freak at the idea of having anything but a database run on the machine.
Especially IIS but IIS with an asp.net application really disturbs them. My
feeling is having a web server running on the database machine is the
direction all vendors are going (Oracle does this too) so the dba's need to
get with the program. Anyway, it is your own decision whether to have it on
the same box or not. Now, people that are doing a web farm by definition are
running on other boxes.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:epbro4bmFHA.3120@.TK2MSFTNGP09.phx.gbl...
>I have to have a sql server license for the machine that is rendering the
>reports dont I? That's MS's gotcha isnt it? RS is 'free' with SQL Server
>but it has to run on a machine by itself so you end up buying another
>license.
> "Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
> news:uxTlqRFmFHA.3552@.TK2MSFTNGP10.phx.gbl...
>> My personal feeling is to have the RS databases local, even if the data
>> is on another server. The RS databases are used as an object store (all
>> the report definitions are stored there, as well as everything else need
>> by RS). SQL Server is good at how it uses memory. I am currently using a
>> workstation with 2 gigs of ram and 2 processors. My suggestion is to add
>> a gig of ram if you can (RS is pretty ram intensive) and add a processor.
>> You might be fine with 1 processor and 1 gig but it up if at all possible
>> beef it up. Also, install Windows 2003 Server Standard edition on it.
>>
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>>
>>
>> "DougS" <doug@.nospam.com> wrote in message
>> news:OWP73$EmFHA.3144@.TK2MSFTNGP12.phx.gbl...
>>I have a couple of dozen users running about 20 reports. The reports are
>>running from SQL Server stored procedures on a server named CORP_DB. The
>>reports render quickly, even running on my development box, I've never
>>had anyone complain about performance. My question is this: can I use a
>>beefy workstation with 2.5ghz cpu and 1gb ram (I'll call it CORP_RS) as
>>my server to host IIS and Reporting Services? If I do it this way should
>>I put the two RS databases on CORP_DB or should I put them on CORP_RS? I
>>may be scaling up to about 100 users and 50 to 80 reports.
>> Thanks,
>> DougS
>>
>

architecture

Where are views, dts packages stored..
ie where would the actual pysical location of vies and dts packages be
Views and stored procedures are in the syscomments table. DTS packages are
in the MSDB database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"mat" <mat@.discussions.microsoft.com> wrote in message
news:37BD3C41-73C3-4F81-AAFA-3E823244BBF4@.microsoft.com...
> Where are views, dts packages stored..
> ie where would the actual pysical location of vies and dts packages be

architecture

Where are views, dts packages stored..
ie where would the actual pysical location of vies and dts packages beViews and stored procedures are in the syscomments table. DTS packages are
in the MSDB database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mat" <mat@.discussions.microsoft.com> wrote in message
news:37BD3C41-73C3-4F81-AAFA-3E823244BBF4@.microsoft.com...
> Where are views, dts packages stored..
> ie where would the actual pysical location of vies and dts packages be

architecture

Where are views, dts packages stored..
ie where would the actual pysical location of vies and dts packages beViews and stored procedures are in the syscomments table. DTS packages are
in the MSDB database.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"mat" <mat@.discussions.microsoft.com> wrote in message
news:37BD3C41-73C3-4F81-AAFA-3E823244BBF4@.microsoft.com...
> Where are views, dts packages stored..
> ie where would the actual pysical location of vies and dts packages besql

Applying Full Text on a document whose path is stored in the database

Hi,

Can we create full-text index on a document that resides on a file system and we have got the path to that document in the database.

Thanks and regards
Salil

I don't think you can do this in current versions of SQL, though i have seen it suggested for future versions. Whether it will be in 2008 or not is probably a question for the Katmai forum.

In saying that, I think a possible workaround is to use Index Server to index your documents which can then be queried via a linked server.

Of course, you are able to store the documents in an IMAGE/VARBINARY(MAX) column which can be fulltext indexed.

HTH!

Applying Full Text on a document whose path is stored in the database

Hi,

Can we create full-text index on a document that resides on a file system and we have got the path to that document in the database.

Thanks and regards
Salil

I don't think you can do this in current versions of SQL, though i have seen it suggested for future versions. Whether it will be in 2008 or not is probably a question for the Katmai forum.

In saying that, I think a possible workaround is to use Index Server to index your documents which can then be queried via a linked server.

Of course, you are able to store the documents in an IMAGE/VARBINARY(MAX) column which can be fulltext indexed.

HTH!

Applying CASE in Stored Procedures

I'm creating a stored procedures, and pass in few parameters, which is @.keys, @.fields, @.types and @.likes, whereby I will select from certain table based on the @.types, where the @.fields equal to @.keys, I also wanted to implement the LIKE to the statement as well, but I found out some problem, so ends up my stored procedure looks like this:

ALTER PROCEDURE searchBooks @.keys varchar(50), @.fields varchar(50)

AS
SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

This statement run well, but I would like to specify the table to select with parameter instead of specified it, but I found it won't works, not even let me save the stored procedure.

Similar to the statements above, I would like another version of it where I using LIKE at the WHERE clause using CASE, I did something like this:

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END) LIKE @.keys

But it said that there was syntax error near the @.keys, do not how to fix it, I tried. Thanks in advanced.

I'm using SQL Server 2000, connecting with Visual Studio.NET 2003

Moving to the Transact-SQL forum, which is best suited to this type of questions.|||

What is the value of @.keys?

If you hard-code the values do you get results?

|||I pass in value to the @.key, I want the key to be the column name in the table, and compare it with the value, my statement is just a simple Select statement, but I want to write in the better way that allow programmer to specify which column to check, so I do not need to write so many statement to select different table.|||Using dynamic column names is not possible, you would probably have to use dynamic SQL to compose the string first and then execute it using either EXEC or sp_executesql.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Well, the CASE in the WHERE does works

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Author
WHEN 'Publisher' THEN Publisher
WHEN 'Title' THEN Title
WHEN 'ISBN' THEN ISBN
END)= @.keys

but if I try to use the LIKE instead of '=' , it did alert me said got syntax error near @.keys, I couldn't get it fix.. adding CASE in the FROM was not possible though, thanks for the link, I will do some reading there.

|||

This works fine in 2005, but consider not doing this. The plan for this query will always be horrendous and will have to touch/lock every row in the books table. I would suggest you look at the following paper:

http://www.sommarskog.se/dyn-search.html

It has a wealth of possibilities for handing this type of situation. There are a few ways to do this, but no matter what I wouldn't just give the user the chance to search for one or the other. I would personally use full dynamic SQL to do something like this, especially in SQL Server 2005 where you can get around the security issues of dynamic SQL. Then the plan can be adjusted on calls that don't use the same filters.

All of this is nicely covered in that article.

|||

Hi,

In my sql server 2000 (Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) ) the LIKE statement works fine..

Pls check all the columns (Author, Publisher, Title, ISBN) datatype, if it is different convert all the columns to varchar/nvarchar,

SELECT *
FROM books
WHERE
(CASE @.field
WHEN 'Author' THEN Convert(NVarchar,Author)
WHEN 'Publisher' THEN Convert(NVarchar,Publisher)
WHEN 'Title' THEN Convert(NVarchar,Title)
WHEN 'ISBN' THEN Convert(NVarchar,ISBN)
END) LIKE @.keys

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...[vbcol=seagreen]
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:

> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...[vbcol=seagreen]
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>

Appliction role Problem with Stored Procedure

Hi,
I'm new using Application Role. I use SQL Query Analyzer to try some
example.
I have create a Application role call "ABC" and have set permission to all
tables & stored procedures.
In Query Analyzer, after setting the role using sp_setapprole,
i) I try to run the stored procedure but it give me 0 result. (it
should return me some records which it doesn't)
ii) I try to run a select statement for a table, it return me all
records.
Since Application Role allows me to set permission for Stored Procedures &
tables, I thought both should be the same.
Does anyone know why is this so?
Regards
VanessaDo you mean that ii is working and i is not? Do you receive any errors?
Try executing the stored procedure code manually.
Or post both i and ii code here.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> Hi,
> I'm new using Application Role. I use SQL Query Analyzer to try some
> example.
> I have create a Application role call "ABC" and have set permission to all
> tables & stored procedures.
> In Query Analyzer, after setting the role using sp_setapprole,
> i) I try to run the stored procedure but it give me 0 result. (it
> should return me some records which it doesn't)
> ii) I try to run a select statement for a table, it return me all
> records.
> Since Application Role allows me to set permission for Stored Procedures &
> tables, I thought both should be the same.
> Does anyone know why is this so?
> Regards
> Vanessa
>
>|||For stored procedure, i did not receive any errors but it return me zero
result. If i didn't set application role and run the SP, it return me 55
rows of record.
In the Query Analyzer, the statement i run is
Step 1) EXEC sp_setapprole 'EMERGE', 'test'
Step 2 SP ) Exec aneadm.uspGetAppAllSetting
Step 2 Normal Select Statement) select * from aneadm.tblvisit
The SP code is
CREATE PROCEDURE aneadm.uspGetAppAllSetting
AS
-- version 1.0
SET NOCOUNT ON
DECLARE @.ReturnCode INTEGER
DECLARE @.IsActiveBit BIT
DECLARE @.Site VARCHAR(80)
DECLARE @.SiteID INTEGER
SELECT @.ReturnCode = 1 -- not ok
SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
DECLARE @.tSettingDesc VARCHAR(255)
SELECT @.tSettingDesc = NULL
SELECT SettingKey,
SettingDesc,
NoOfCopies,
IsPreview
FROM tblAppSetting (nolock)
WHERE SiteID = @.SiteID
AND IsActive = 1
GO
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> Do you mean that ii is working and i is not? Do you receive any errors?
> Try executing the stored procedure code manually.
> Or post both i and ii code here.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> Hi,
>> I'm new using Application Role. I use SQL Query Analyzer to try some
>> example.
>> I have create a Application role call "ABC" and have set permission to
>> all
>> tables & stored procedures.
>> In Query Analyzer, after setting the role using sp_setapprole,
>> i) I try to run the stored procedure but it give me 0 result. (it
>> should return me some records which it doesn't)
>> ii) I try to run a select statement for a table, it return me all
>> records.
>> Since Application Role allows me to set permission for Stored Procedures
>> &
>> tables, I thought both should be the same.
>> Does anyone know why is this so?
>> Regards
>> Vanessa
>>|||Once you set the application role, user_name() will return 'EMERGE'. Is this
what you want?
Or maybe suser_sname() for the current user id?
Ben Nevarez, MCDBA, OCP
Database Administrator
"Vanessa" wrote:
> For stored procedure, i did not receive any errors but it return me zero
> result. If i didn't set application role and run the SP, it return me 55
> rows of record.
> In the Query Analyzer, the statement i run is
> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
> Step 2 Normal Select Statement) select * from aneadm.tblvisit
> The SP code is
>
> CREATE PROCEDURE aneadm.uspGetAppAllSetting
> AS
> -- version 1.0
> SET NOCOUNT ON
> DECLARE @.ReturnCode INTEGER
> DECLARE @.IsActiveBit BIT
> DECLARE @.Site VARCHAR(80)
> DECLARE @.SiteID INTEGER
> SELECT @.ReturnCode = 1 -- not ok
> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
> DECLARE @.tSettingDesc VARCHAR(255)
> SELECT @.tSettingDesc = NULL
> SELECT SettingKey,
> SettingDesc,
> NoOfCopies,
> IsPreview
> FROM tblAppSetting (nolock)
> WHERE SiteID = @.SiteID
> AND IsActive = 1
> GO
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
> >
> > Do you mean that ii is working and i is not? Do you receive any errors?
> >
> > Try executing the stored procedure code manually.
> >
> > Or post both i and ii code here.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Vanessa" wrote:
> >
> >> Hi,
> >>
> >> I'm new using Application Role. I use SQL Query Analyzer to try some
> >> example.
> >>
> >> I have create a Application role call "ABC" and have set permission to
> >> all
> >> tables & stored procedures.
> >>
> >> In Query Analyzer, after setting the role using sp_setapprole,
> >> i) I try to run the stored procedure but it give me 0 result. (it
> >> should return me some records which it doesn't)
> >> ii) I try to run a select statement for a table, it return me all
> >> records.
> >>
> >> Since Application Role allows me to set permission for Stored Procedures
> >> &
> >> tables, I thought both should be the same.
> >>
> >> Does anyone know why is this so?
> >>
> >> Regards
> >> Vanessa
> >>
> >>
> >>
>
>|||Thanks.
I think i know why I can't retrieve the records when using Stored Procedure.
Regards
Vanessa
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:2CA7337C-3FCC-4F51-8DCA-8F6F6C70E855@.microsoft.com...
> Once you set the application role, user_name() will return 'EMERGE'. Is
> this
> what you want?
> Or maybe suser_sname() for the current user id?
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Vanessa" wrote:
>> For stored procedure, i did not receive any errors but it return me zero
>> result. If i didn't set application role and run the SP, it return me 55
>> rows of record.
>> In the Query Analyzer, the statement i run is
>> Step 1) EXEC sp_setapprole 'EMERGE', 'test'
>> Step 2 SP ) Exec aneadm.uspGetAppAllSetting
>> Step 2 Normal Select Statement) select * from aneadm.tblvisit
>> The SP code is
>>
>> CREATE PROCEDURE aneadm.uspGetAppAllSetting
>> AS
>> -- version 1.0
>> SET NOCOUNT ON
>> DECLARE @.ReturnCode INTEGER
>> DECLARE @.IsActiveBit BIT
>> DECLARE @.Site VARCHAR(80)
>> DECLARE @.SiteID INTEGER
>> SELECT @.ReturnCode = 1 -- not ok
>> SELECT @.Site = aneadm.ufntblGetCurrentSite(User_Name())
>> SELECT @.SiteID = aneadm.ufnGetReferenceID('SITE', @.Site)
>> DECLARE @.tSettingDesc VARCHAR(255)
>> SELECT @.tSettingDesc = NULL
>> SELECT SettingKey,
>> SettingDesc,
>> NoOfCopies,
>> IsPreview
>> FROM tblAppSetting (nolock)
>> WHERE SiteID = @.SiteID
>> AND IsActive = 1
>> GO
>>
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:988729A9-2543-4688-9B29-09C2D1E16A80@.microsoft.com...
>> >
>> > Do you mean that ii is working and i is not? Do you receive any errors?
>> >
>> > Try executing the stored procedure code manually.
>> >
>> > Or post both i and ii code here.
>> >
>> > Ben Nevarez, MCDBA, OCP
>> > Database Administrator
>> >
>> >
>> > "Vanessa" wrote:
>> >
>> >> Hi,
>> >>
>> >> I'm new using Application Role. I use SQL Query Analyzer to try some
>> >> example.
>> >>
>> >> I have create a Application role call "ABC" and have set permission to
>> >> all
>> >> tables & stored procedures.
>> >>
>> >> In Query Analyzer, after setting the role using sp_setapprole,
>> >> i) I try to run the stored procedure but it give me 0 result.
>> >> (it
>> >> should return me some records which it doesn't)
>> >> ii) I try to run a select statement for a table, it return me
>> >> all
>> >> records.
>> >>
>> >> Since Application Role allows me to set permission for Stored
>> >> Procedures
>> >> &
>> >> tables, I thought both should be the same.
>> >>
>> >> Does anyone know why is this so?
>> >>
>> >> Regards
>> >> Vanessa
>> >>
>> >>
>> >>
>>

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Application/Security Design: Stored Procedures versus SQL queries

Hello everyone,

I don't know what category would be appropriate for this question but security seems to be close enough.

I have this case scenario: I am running an automated application that extracts data from a web site and stores the data into a table on SQL server 2005. This information is not confidential in the extreme of social insurance #'s, bank account #s, but should not be seen by a typical employee (it has no use for them). After the data has been stored, it retrieves the data from the same table, processes it, and updates the same table. This application runs every hour infinitely.

Should all the insert, update, and select queries be stored under a stored procedure? I am not concern with performance. My concern would fall under design and security.

Is it worth to hide the details of inserting/updating/selecting behind a stored procedure? Or should I just allow the program to send select/update/insert SQL queries?

No employee (other then the developer and the DB admin) or customer ever access this table (They do not have permission from SQL). The username and passwords were created with security in mind.

Any thoughts or ideas?

Thanks for your time, Adrian

It sounds as though 'a typical employee' would not have access to the table. As long as the PUBLIC role cannot access the table, and 'a typical employee' does not have permissions, you are covered.

Using Stored Procedures 'may' be excessive in this situation.

|||

Thank your for your response.

I still am left wondering whether it is worth to hide the details of inserting/updating/selecting behind a stored procedure or whther I should just allow the program to send select/update/insert SQL queries.

I consider the stored procedure as the last "line of defense" when it comes to enforcing business rules. Although in theory of a 3-tier system, the business layer takes care of this, I have seen other developers program some sort of application and they forgot one aspect of the business or some sort of formating error. A stored procedure would correct this b/c it doesn't matter which business layer accesses the SQL server, they all will be enforced by the server.

Do you have an understanding of where I am coming from? Where do we draw the line of what the business layer enforces and what the SQL server enforced?

This is where I'd like to get other opinions on it too.

Thank you for your time, Adrian

|||

Adrian,

First, let me state that in 'almost' all situations, I recommend using stored procedures for a several reasons, including: reuseability, abstraction and security. Reuseabilitiy infurs that the same code is called multiple times from the application. Abstraction allows the procedure code (and how/where the data is actually stored) to be 'tuned' without having to redeploy the application. And of course, security protects the tables from inadvertent alteration and maintains a minimum level of data protection -and may even inject some form of audit trailing.

That said, I recommend against putting excessive 'business rules' into stored procedures. I prefer business rules to be in the 'middle tier' -mainly for scalability reasons, as well as my attempts to keep the data server dedicated to protecting the data. Here, however, you will find a great range of opinions, and as hardware becomes more robust and less expensive, and data clusters have become easier to configure and operate -'scalability' becomes less of an issue.

Now in your situation, you posit that the application is automatic -little chance for SQL injection, and users have no reason for direct data access. You have to balance the extra effort for creating and testing the stored procedures against the benefit. Granted, there 'may' be a slight increase in performance due to reusing compiled procedures -but you are doing the same activities over and over again so even the 'ad-hoc' queries would most likely be in the procedure cache (make sure the queries are properly parameterized). But the main question to ask is: "What is the benefit and what is the cost of mandating stored procedure use above direct table access?"

|||

May I know how to give all store procedures exec permission to one user instead one by one. Thanks in advance for your advice.

eg.

GRANT EXECUTE ON [dbo].[SP_xxx] TO [yyy]

GO

one by one procedure how can do it all for one go.

With regards

Bala

|||Create a database role. Add all users to the role. Grant permissions to the role.|||

Hi,

Thanks for the reply, we everyday delete the store procedure and recreate atleast few hundred. Once we delete the permission lost or new store procedures no permission set. If we know what is the command to run for all store procedures, instead of one by one i will run every time after creating the store procedure.

With regards

Bala

|||

I always recommend adding stored procedures to source control. Each stored procedure as a separate file. Databases can be 'refreshed' from source control in order to keep out development 'detritus'.

Each stored procedure file also contains the necessary permissions. Here is an example: (Works in SQL 2000/2005)

IF EXISTS
( SELECT ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_NAME = 'MyProcedureName'
)
DROP PROCEDURE dbo.MyProcedureName
GO

CREATE PROCEDURE dbo.MyProcedureName
/****************************************************************
* PROCEDURE: MyProcedureName
* DATE:
* AUTHOR:
*-
* DESCRIPTION:
*
*-
* CODE REVIEW: Date/Who/Status
*-
* VSS revision history: (location in source control)
****************************************************************/
( @.Parameter1 datatype,
, @.Parameter2 datatype
)
AS

SET NOCOUNT ON

-- Procedure Code Here

SELECT
@.Err = @.@.ERROR
, @.RowsAffected = @.@.ROWCOUNT

IF ( @.Err != 0 )
RETURN @.Err

IF ( @.RowsAffected = 0 )
RETURN -1

RETURN 0
GO

GRANT EXECUTE ON dbo.MyProcedureName TO MyCustomRole
GO

|||

Hi Arnie,

Thankyou very much i will do that.

With regards

Bala

Application Roles for Cross-Database Joins

I have an application that segregates data into two different
databases. Database A has stored procs that perform joins between
tables in database A and database B. I am thinking that I have reached
the limits of Application Roles, but correct me if I am wrong.
My application creates a connection to database A as 'testuser' with
read only access, then executes sp_setapprole to gain read write
permissions. Even then the only way 'testuser' can get data out of the
databases is via stored procs or views, no access to tables directly.
Anyone know of a solution? Here is the error I get:

Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
38
Server user 'testuser' is not a valid user in database 'DatabaseB'

The system user is in fact in database A and B.

thanks

Jason SchaitelJason_Schaitel (jason_schaitel@.hotmail.com) writes:
> I have an application that segregates data into two different
> databases. Database A has stored procs that perform joins between
> tables in database A and database B. I am thinking that I have reached
> the limits of Application Roles, but correct me if I am wrong.
> My application creates a connection to database A as 'testuser' with
> read only access, then executes sp_setapprole to gain read write
> permissions. Even then the only way 'testuser' can get data out of the
> databases is via stored procs or views, no access to tables directly.
> Anyone know of a solution? Here is the error I get:
> Server: Msg 916, Level 14, State 1, Procedure pr_GetLocationInfo, Line
> 38
> Server user 'testuser' is not a valid user in database 'DatabaseB'
> The system user is in fact in database A and B.

Books Online says:

When an application role is activated, the permissions usually
associated with the user's connection that activated the application
role are ignored. The user's connection gains the permissions
associated with the application role for the database in which the
application role is defined. The user's connection can gain access to
another database only through permissions granted to the guest user
account in that database. Therefore, if the guest user account does not
exist in a database, the connection cannot gain access to that
database.

That is, once you have set the application role in A, you are someone
else, and your access outside A is limited.

The one way I can think of to sort this out - beside uniting the databases
into one - is to enable the server configuration parameter "Cross DB
Ownership Chaining". This option was added in SP3 is off by default.
If there no other databases from other applications on the server,
there is no problem to enable this option. However, on consolidated
server that hosts databases for unrelated applications, this is not
recommendable.

For cross DB chaining to work, the databases must also have the same
owner.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>
> The one way I can think of to sort this out - beside uniting the databases
> into one - is to enable the server configuration parameter "Cross DB
> Ownership Chaining". This option was added in SP3 is off by default.
> If there no other databases from other applications on the server,
> there is no problem to enable this option. However, on consolidated
> server that hosts databases for unrelated applications, this is not
> recommendable.

Jason could instead enable the 'db chaining' database option for only those
databases needed by the application rather than turning the cross-database
chaining server-wide.

> For cross DB chaining to work, the databases must also have the same
> owner.

This is true, assuming the objects are owned by 'dbo', because database
ownership determines the dbo user mapping. In the case of non-dbo-owned
objects, the object owners in the different databases need to map to the
same login in order to maintain an unbroken ownership chain.

> The user's connection can gain access to
> another database only through permissions granted to the guest user
> account in that database. Therefore, if the guest user account does not
> exist in a database, the connection cannot gain access to that
> database.

To expand on this BOL excerpt, it's necessary to enable the guest user in
the non-application role databases so that users have a security context
after the application role is enabled. However, no permissions need to be
granted to guest or public in Jason's situation because access is done only
through views and procs from application role database.

--
Hope this helps.

Dan Guzman
SQL Server MVP|||I have tried to look in BOL and Google Groups for the how to enable the
cross database ownership chaining option at the database level and not
having much luck. Can you point me to it?

thanks

Jason|||Jason_Schaitel (jason_schaitel@.hotmail.com) writes:
> I have tried to look in BOL and Google Groups for the how to enable the
> cross database ownership chaining option at the database level and not
> having much luck. Can you point me to it?

exec sp_dboption yourdb, 'db chaining', true

This option is not in the original Books Online, as it was added in SP3.
But it is in the updated Books Online, see link below.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Application role to access xp_cmdshell

I have an Access app linked to a SQL server db. This app uses
xp_cmdshell in some stored procedures and it works fine. As long as
the user is administrator... I'd like to set up an application role
that can execute xp_cmdshell and access my db but I don't know how to
do it as xp_cmdshell is in the Master db while everything else is in
my own db. I'm also unsure whether to call sp_setapprole from the sp's
or from the Access app.
Can somebody please give me some code examples or direct me to a good
site?
/CarlAs long as the ownership chain is unbroken, direct permissions on
xp_cmdshell are not needed. This necessitates that your user procs be owned
by 'dbo', your user database be owned by 'sa' and cross-database chaining
(intoduced in SQL 2000 SP3) be enabled. Example script below.
For security reasons, it is important that your user proc be coded in such a
way that only the intended command can be executed. Also, you should enable
cross-database chaining only if you fully trust users that have permissions
to create dbo-owned objects. See Cross-database chaining in the SQL 2000
Books Online for more information.
You will also need to allow non-sysadmin users to execute xp_cmdshell. You
can do this from Enterprise Manager under Management-->SQL Server
Agent-->Job System. Uncheck the 'Only users with sysadmin privileges...'
check box and specify the Windows account you want to use as the OS security
context for non-sysadmin users. This account should have the minimal
permissions need to perform the needed tasks.

> I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
You'll need to execute sp_setapprole directly from your application. From
the Books Online:
<Excerpt href="http://links.10026.com/?link=tsqlref.chm::/ts_sp_sa-sz_6tt1.htm">
The sp_setapprole stored procedure can be executed only by direct
Transact-SQL statements; it cannot be executed within another stored
procedure or from within a user-defined transaction.
</Excerpt>
USE MyDatabase
EXEC sp_changedbowner 'sa'
GO
-- for SQL 2000 SP3+
EXEC sp_dboption 'MyDatabase', 'db chaining', true
GO
CREATE PROC dbo.MyXpCmdShellProc
AS
EXEC master..xp_cmdshell 'MyCommand'
GO
GRANT EXEC ON dbo.MyXpCmdShellProc TO MyAppRole
GO
EXEC sp_setapprole 'MyAppRole', 'MyAppRolePassword'
EXEC dbo.MyXpCmdShellProc
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402170439.d8b1453@.posting.google.com...
> I have an Access app linked to a SQL server db. This app uses
> xp_cmdshell in some stored procedures and it works fine. As long as
> the user is administrator... I'd like to set up an application role
> that can execute xp_cmdshell and access my db but I don't know how to
> do it as xp_cmdshell is in the Master db while everything else is in
> my own db. I'm also unsure whether to call sp_setapprole from the sp's
> or from the Access app.
> Can somebody please give me some code examples or direct me to a good
> site?
> /Carl|||Thanks Dan for your excellent explanation. But unfortunately I'm still
at SQL 7... Any other options?
Carl
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message news:<#kQj98V9DHA.3176@.TK
2MSFTNGP11.phx.gbl>...
> As long as the ownership chain is unbroken, direct permissions on
> xp_cmdshell are not needed. This necessitates that your user procs be own
ed
> by 'dbo', your user database be owned by 'sa' and cross-database chaining
> (intoduced in SQL 2000 SP3) be enabled. Example script below.
>|||The technique will work with SQL 7 too. The only difference is that
cross-database chaining is not configurable under SQL 7 and pre-SQL2000 SP3
(it is always on). Just remember to run the 'db chaining' option on in your
user database if you later upgrade to SQL 2000 SP3+.
Hope this helps.
Dan Guzman
SQL Server MVP
"Carl Olsson" <caos@.regerar.com> wrote in message
news:d495b147.0402172316.6a6cf8b@.posting.google.com...
> Thanks Dan for your excellent explanation. But unfortunately I'm still
> at SQL 7... Any other options?
> Carl
>
> "Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:<#kQj98V9DHA.3176@.TK2MSFTNGP11.phx.gbl>...
owned
chaining

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
Manoj
Answered your question in another thread.
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

Application Role permissions report

Can I generate the report for each application role with their objects &
their permissions using sql stored procedure ?
Let me know.
Thanks for all the help.
ManojAnswered your question in another thread.
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Manoj" <Manoj@.discussions.microsoft.com> wrote in message
news:1FF02616-9454-47DC-951D-14586999778F@.microsoft.com...
> Can I generate the report for each application role with their objects &
> their permissions using sql stored procedure ?
> Let me know.
> Thanks for all the help.
> Manoj

Application respond slowly

Dear All
My SQL Server is contains 2 databases. Both of them are used by
different client/server application and each application uses several
stored procedure to query and update its database. The problem is my
users reports that those two application respond slowly.
I used SQL Profile, and here the result.
EventClass______________________TestData
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1 = SQL:BatchCompleted Update TableB Set Col2 = '23456..
SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
SQL:BatchCompleted Update TableA Set Col1=
What should I do to improve the performance?
- by using views to query data or modify the stored procedure so that
they select data into temporary tables.
Pls give me your advice
Thanks
Robert LieThere's a boundless number of potential issues and bottlenecks. Far more
information is needed to help with this. First try to gather some
perfromance stats. Is the server busy? Is there blocking? What
perfromance do you get when running in query analyzer?
"robert lie" <robert.lie24@.gmail.com> wrote in message
news:ebnyU$RZFHA.2884@.tk2msftngp13.phx.gbl...
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by different
> client/server application and each application uses several stored
> procedure to query and update its database. The problem is my users
> reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that they
> select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie|||As mentioned by Danny, there can be a lot of reason. Before you just jump to
Profiler, I'd investigate a bit more what the users means that the
applications respond slowly. It might help to know if it's something that
has happended all of a sudden or if performance has degraded slowly over
time. It will also be different things to look for if it's a new application
that just never has performed decent.
If performance has degraded slowly over time, I'd look at things like
missing statistic update, Index defragmentation, memory consumption,
Disksystem defragmention, logfile size (i.e. does it autogrow too often?),
disk space etc.
If performance has degraded all of a sudden, I'd more look for diskproblems,
disk space issues, server HW problems, CPU utilization etc. I'd then also
check what have been done to the server in terms of updates (Windows, SQL,
AntiVirus etc) to see if it could something that are consuming a lof of CPU.
If performance never has been decent with those applications I'd look at
more basic things like server configuration, RAID, disksystem, memory etc.
It could also be poorly designed applications, but if it's two applications
independent of each other, it's not very likely that they both suffer from
poor design.
I hope that some of the above can help you to troubleshoot the issues?
Regards
Steen
robert lie wrote:
> Dear All
> My SQL Server is contains 2 databases. Both of them are used by
> different client/server application and each application uses several
> stored procedure to query and update its database. The problem is my
> users reports that those two application respond slowly.
> I used SQL Profile, and here the result.
> EventClass______________________TestData
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1 => SQL:BatchCompleted Update TableB Set Col2 = '23456..
> SQL:BatchCompleted If @.@.TRANCOUNT > 0 COMMIT TRAN
> SQL:BatchCompleted Update TableA Set Col1=>
> What should I do to improve the performance?
> - by using views to query data or modify the stored procedure so that
> they select data into temporary tables.
> Pls give me your advice
> Thanks
> Robert Lie

application development and permissions

Using SS2000 SP4. We have an application that will use a single user to logi
n
from the application after development. We will use only stored procedures
and views to access the data.
I was thinking of creating a "developer" login for the developer and
assigning it to the db_owner role. As I understand it, this would make the
owner of the tables, stored procedures and views "dbo". After development,
assuming there is no dynamic sql used, would I then be able to give execute
permission on the stored procedures and select permissions on the views to
the application user and have everything work?
Thanks,
Dan D.Dan
Sure
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
> Using SS2000 SP4. We have an application that will use a single user to
> login
> from the application after development. We will use only stored procedures
> and views to access the data.
> I was thinking of creating a "developer" login for the developer and
> assigning it to the db_owner role. As I understand it, this would make the
> owner of the tables, stored procedures and views "dbo". After development,
> assuming there is no dynamic sql used, would I then be able to give
> execute
> permission on the stored procedures and select permissions on the views to
> the application user and have everything work?
> Thanks,
> --
> Dan D.|||Thanks.
--
Dan D.
"Uri Dimant" wrote:

> Dan
> Sure
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:A56A2358-08D0-4C65-9D2F-564BF9873B6D@.microsoft.com...
>
>