ArcServ and SQl Server 2000

Hi,

I am trying to create a backup agent for a SQl Server. I have given the information for the following: instance, AUTHENTICATION, USERNAME, PASSWORD, CONFIRM PASSWORD.

I keep getting the error message, enter valid instance, even though i am entering in the instance.

any ideas?

Is this a named instance? Do you have any special characters like dashes in the server name?

-Sue

|||Problem solved ? If yes, post how you solved it, or if the hint from Sue helped you′.

Jens K. Suessmeyer

http://www.sqlserver2005.de

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.

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

April CTP:error when attaching db and when creating file in filegroup

Hi.
I'm using SQL Server 2005 beta 2 April CTP:

I am always getting this error while trying to attach a database and even when I try to create a file in a filegroup for table partitioning.

Error:

The file "<filename> " is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

is it a security problem?

Thanks
Christina

I found out that it will not accept a file if it is not found under the
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data
folder.

|||I saw the same thing but is that the expected behavior. Can we not create database with database files any where on the hard drive.|||

I am not aware of such requirement, but based on your error message
I think your file is compressed (or is placed on compressed disk or folder)

Could you please check it. Click on the file property and go to Advance attributes to see if

"Compress contents to save disk space" is activated (checked).

Do the same for the file folder. If this is the case, please deactivate it.

SQL Server supports compressions with some limitations

(see Using NTFS Compression with Read-Only Secondary Filegroups and Read-Only Databases under Read-only filegroups in BOL)

Thanks

Mirek

|||Christina -
Did you have a chance the check if your file is using compressed media?
Thanks
Mirek|||Dear Mirek,
Thanks for your input.
I just tried it and as u said, it worked.

Regards,
Christina

applying styles to reports DataTransform property

I was hoping that I could create an xsl file to change background colors and
the like in my report. The DataTransform documentation ain't so great.
After creating the xsl file and uploading it to the server and setting the
DataTransform property no changes were evident, I think it only operates on
xml format or something along those lines. Mainly we use pdf and html
rendering. So now I'm thinking I can apply the xsl and generate different
styles when I deploy the reports and add the drop down style selection to
the web app. Anyone giving their users the ability to pick a report style,
and how are you going about it? Any thoughts?
For Example:
deployment:
msxsl myReport.rdl slate.xsl -xe -o myReport_slate.rdl
msxsl myReport.rdl forest.xsl -xe -o myReport_forest.rdl
Here's an example xsl
<?xml version="1.0"?>
<xsl:stylesheet version="2.0"
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msrs="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:fn="http://www.w3.org/2005/xpath-functions"
exclude-result-prefixes="msrs xs fn">
<xsl:output method="xml" standalone="no" omit-xml-declaration ="no"
indent="yes"/>
<xsl:template match="//msrs:FontFamily">
<FontFamily>Tahoma</FontFamily>
</xsl:template>
<xsl:template match="//msrs:BackgroundColor">
<BackgroundColor>SteelBlue</BackgroundColor>
</xsl:template>
<xsl:template match="@.* | node()" priority="-2">
<xsl:copy>
<xsl:apply-templates select="@.* | node()"/>
</xsl:copy>
</xsl:template>
</xsl:stylesheet>Hi, I read your post regarding the use of DataTransform in reporting service. If you make any progress with it, would be kind enough to share it?
Thank You.
Rif'at
EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.comsql

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!

Apply XSLT to an xml field in SQL Server 2005

Hi all,
Is it possible to apply an xslt template to an xml field in SQL Server 2005
by means of T-SQL? Or should I create a .NET assembly to perform this task?
Thanks in advance,
Alberto.Hello Alberto,

> Is it possible to apply an xslt template to an xml field in SQL Server
> 2005 by means of T-SQL? Or should I create a .NET assembly to perform
> this task?
Here you go: http://www.sqljunkies.com/WebLog/kt...l
t.aspx
Thanks,
Kent Tegels, DevelopMentor
http://staff.develop.com/ktegels/|||Thanks Kent.
Not only you answer my question, but also you give a good example.

Apply compression - XMLA

Hi there,

I intend to create a backup job for my cube, however I didn't find anywhere the XMLA configuration command for the "Apply Compression" option in the cube backup properties. Even if I check this box and script the setting it doesn't show up. I haven't find anything even in the XMLA command reference .

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Object>
<DatabaseID>test</DatabaseID>
</Object>
<File>test.abf</File>
<AllowOverwrite>true</AllowOverwrite>
</Backup>

Any help would be appreciated!

Thanks,

Greg

There is an ApplyCompression element, which is in the latest BOL. You do not see this element when you turn compression on as the default value is on, you only see it when you turn compression off.

Code Snippet

<Backup xmlns=http://schemas.microsoft.com/analysisservices/2003/engine>

<Object>

<DatabaseID>Adventure Works DW</DatabaseID>

</Object>

<File>Adventure Works DW.abf</File>

<ApplyCompression>false</ApplyCompression>

</Backup>

Apply a long list of SQL Statements

I just received a long text file that contains all statements required to
create all necessary tables as well as inserting all the data into this
table
Using the Enterprise Manager how can I apply all these statements?
Thank you,
SamuelSamuel
> Using the Enterprise Manager how can I apply all these statements?
Why ? open a query analyzer and run this statement (modify it for your
needs)
EXEC master..xp_cmdshell
'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
"Samuel Shulman" <samuel.shulman@.ntlworld.com> wrote in message
news:O1$a3wzfGHA.2032@.TK2MSFTNGP02.phx.gbl...
>I just received a long text file that contains all statements required to
>create all necessary tables as well as inserting all the data into this
>table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>|||copy the info into query analyzer and execute it there.
"Samuel Shulman" wrote:
> I just received a long text file that contains all statements required to
> create all necessary tables as well as inserting all the data into this
> table
> Using the Enterprise Manager how can I apply all these statements?
> Thank you,
> Samuel
>
>|||On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>Why ? open a query analyzer and run this statement (modify it for your
>needs)
>EXEC master..xp_cmdshell
>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
Hi Uri,
Ir you're going to use osql.exe, why use Query Analyzer at all? Just
open a DOS prompt (or command window, or whatever it is called now).
For better ability to view the output, I'd recommend either adding an
output file to the osql command (using the -o parameter), or switching
to Quary Analyzer, copying in the text of the script, then hitting the
Execute button.
--
Hugo Kornelis, SQL Server MVP|||Hugo
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
Don't you see people who was wrapping this command into a stored procedure
and run in within a job or whatever?
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:a0h972p5rpockl0n8v38sabjlbkg57ejls@.4ax.com...
> On Wed, 24 May 2006 17:22:51 +0300, Uri Dimant wrote:
>>Samuel
>> Using the Enterprise Manager how can I apply all these statements?
>>Why ? open a query analyzer and run this statement (modify it for your
>>needs)
>>EXEC master..xp_cmdshell
>>'osql -S<yourServer> -U<user> -P<password> -iC:\File.sql -n '
> Hi Uri,
> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
> open a DOS prompt (or command window, or whatever it is called now).
> For better ability to view the output, I'd recommend either adding an
> output file to the osql command (using the -o parameter), or switching
> to Quary Analyzer, copying in the text of the script, then hitting the
> Execute button.
> --
> Hugo Kornelis, SQL Server MVP|||On Thu, 25 May 2006 09:43:06 +0300, Uri Dimant wrote:
>Hugo
>> Ir you're going to use osql.exe, why use Query Analyzer at all? Just
>> open a DOS prompt (or command window, or whatever it is called now).
>Don't you see people who was wrapping this command into a stored procedure
>and run in within a job or whatever?
Hi Uri,
Since Samuel asked "how to apply these statements (...) using Enterprise
Manager", I figured he wanted to do it manually, not to schedule it.
Also, scripts that contain CREATE TABLE statements should normally run
just once.
--
Hugo Kornelis, SQL Server MVP

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 roles

I an having problems setting up an application role:
-- create the app role
exec sp_addapprole 'MyAppRole', ''approlepassword'
-- grant it ALL priv
grant all to MyAppRole
-- create new new user
exec sp_addlogin 'User1', 'password', 'MyDatabase'
use MyDatabase
sp_grantdbaccess 'User1'
-- new user login and executes
exec sp_setapprole 'MyAppRole', ''approlepassword'
now User1 tries to do anyithing and they have no access to any objects, why
is this, I have granted all priv to the app role. I must be missing
something basic here.
Can anyone point me in the right direction.
Thanks,
Tony"Tony" <tonyng2@.spacecommand.net> wrote in message
news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I an having problems setting up an application role:
> -- create the app role
> exec sp_addapprole 'MyAppRole', ''approlepassword'
> -- grant it ALL priv
> grant all to MyAppRole
> -- create new new user
> exec sp_addlogin 'User1', 'password', 'MyDatabase'
> use MyDatabase
> sp_grantdbaccess 'User1'
> -- new user login and executes
> exec sp_setapprole 'MyAppRole', ''approlepassword'
> now User1 tries to do anyithing and they have no access to any objects,

---

why

quote:

---

> is this, I have granted all priv to the app role. I must be missing
> something basic here.
> Can anyone point me in the right direction.
> Thanks,
> Tony
>
>

---

GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
object permissions, you need to grant individual permissions for each
object:
grant execute on proc1 to MyAppRole
grant update on table1 to MyAppRole
etc.
You can make this easier by using built-in roles like
db_datareader/db_datawriter, or by cutting, pasting, reviewing and executing
the output of a query like this:
select 'grant execute on ' + routine_name + ' to MyAppRole'
from information_schema.routines
where routine_type = 'procedure'
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

---

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

---

executing

quote:

---

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

---

--
But, I dynamically add/remove objects all the time. I surly don't want to
have to reset ll the permissions each time.
I guess using application roles is not going to cut if for me, since this
would be way to much of a maint. headache. Guess I will just have to assign
the users to built in roles unless someone knows an easier way to maintain
the app role when new objects are being added/removed at any time without
requiring the app role to be changed.
Would be really nice if I could grant the app role as another role such as
dbadmin.
Thanks,
Tony|||Okay figure out I can assign app role to be a member of db_owner.
But, when a user is set to the app role, all objects created by this user
get the app role owner, and not dbo as they should if they have db_owner.
Why is this?
Example:
sp_addlogin User1, password, db1
use db1
sp_grantdbaccess User1
sp_addapprole MyAppRole, rolepassword
sp_addrolemember db_owner, MyAppRole
Now user logins in as User1
sp_setapprole MyAppRole, rolepassword
Create table Test(Name varchar(50))
Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
as is should (well I think it should)
Why is this?
Tony
"Simon Hayes" <sql@.hayes.ch> wrote in message
news:401ff029$1_2@.news.bluewin.ch...

quote:

---

> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:exnIzfn6DHA.2568@.TK2MSFTNGP10.phx.gbl...
> why
> GRANT ALL does not grant object permissions (SELECT, UPDATE etc.) - it
> grants statement permissions (CREATE TABLE, BACKUP LOG etc.). To grant
> object permissions, you need to grant individual permissions for each
> object:
> grant execute on proc1 to MyAppRole
> grant update on table1 to MyAppRole
> etc.
> You can make this easier by using built-in roles like
> db_datareader/db_datawriter, or by cutting, pasting, reviewing and

---

executing

quote:

---

> the output of a query like this:
> select 'grant execute on ' + routine_name + ' to MyAppRole'
> from information_schema.routines
> where routine_type = 'procedure'
> Simon
>

---

|||A db_owner role member needs to explicitly specify owner 'dbo' in order to
create dbo-owned objects.
CREATE TABLE dbo.Test(Name varchar(50))
Hope this helps.
Dan Guzman
SQL Server MVP
"Tony" <tonyng2@.spacecommand.net> wrote in message
news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...

quote:

---

> Okay figure out I can assign app role to be a member of db_owner.
> But, when a user is set to the app role, all objects created by this user
> get the app role owner, and not dbo as they should if they have db_owner.
> Why is this?
> Example:
> sp_addlogin User1, password, db1
> use db1
> sp_grantdbaccess User1
> sp_addapprole MyAppRole, rolepassword
> sp_addrolemember db_owner, MyAppRole
> Now user logins in as User1
> sp_setapprole MyAppRole, rolepassword
> Create table Test(Name varchar(50))
> Test table is now owned by MyAppRole as MyAppRole.Test instead of dbo.Test
> as is should (well I think it should)
> Why is this?
> Tony
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:401ff029$1_2@.news.bluewin.ch...
objects,[QUOTE]
> executing
>

---

|||Hmm, I 'm used to it doing that automatically if you ARE a db_owner.
But I can handle that.
Thanks,
Tony
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:%23pkSCPy6DHA.2432@.TK2MSFTNGP10.phx.gbl...

quote:

---

> A db_owner role member needs to explicitly specify owner 'dbo' in order to
> create dbo-owned objects.
> CREATE TABLE dbo.Test(Name varchar(50))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Tony" <tonyng2@.spacecommand.net> wrote in message
> news:%233fORlv6DHA.2572@.TK2MSFTNGP09.phx.gbl...
user[QUOTE]
db_owner.[QUOTE]
dbo.Test[QUOTE]
> objects,
>

---

application role same as this user

we have many homegrown applications. for each application, we create a
sql user id for the application. that id has db_datareader and
db_datawriter (no other permissions) in its database and cannot access
any other databases. is that basically the same thing as an
application role? if not, what advantages does an application role have
over that?With or without application roles, users still need a login (individual or
shared) to connect to SQL Server. The main advantage of an application role
compared to a regular database role is that you can grant full permissions
to the app role and choose to enable it only from within your application.
This way, users cannot perform ad-hoc queries outside the application unless
they have been granted permissions to do so.
Hope this helps.
Dan Guzman
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:4051B3AC.816C10DF@.dontemailme.com...
> we have many homegrown applications. for each application, we create a
> sql user id for the application. that id has db_datareader and
> db_datawriter (no other permissions) in its database and cannot access
> any other databases. is that basically the same thing as an
> application role? if not, what advantages does an application role have
> over that?
>

Application Role

after i create the application role, how do i make the application use this
role
in other words , how do i assign the application to this role '
--
regards
Maidoo.maido
Do you mean how to activate the application role within VB aplication?
"maidoo" <maidooalex@.msn.com> wrote in message
news:028CA4C1-55CB-4819-A2BA-BB6B8CC48B60@.microsoft.com...
> after i create the application role, how do i make the application use
this
> role
> in other words , how do i assign the application to this role '
> --
> regards
> Maidoo.
>|||actually, i don't know the step taken after creating this role , do i
activate it from the application, or there's something in the SQl Server
that let me assign this role to an application (exe file or something
like that )
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi
Yes , you will have to activate it from the application.
Private Sub Form_Load()
Dim strConnect As String
Dim db_username As ADODB.Recordset
Dim ss As String
Dim pp As String, toto As String
pp = "pp"
toto = "toto"
Set cnAdo = New ADODB.Connection
strConnect = "driver={SQL
Server};uid=uri_test;pwd=tennis;server=S
erverName;database=databasename;Netw
ork=dbmssocn"
cnAdo.Provider = "SQLOLEDB"
cnAdo.ConnectionString = strConnect
cnAdo.CommandTimeout = 30 '10
cnAdo.CursorLocation = adUseServer
cnAdo.Mode = adModeRead
cnAdo.Open
' user_appl_role and user_appl_pwd are corect
cnAdo.Execute "EXEC sp_setapprole '" & pp & " ', '" & toto & "'"
Set db_username = New Recordset
sqlcommand = "select user_name ()"
db_username.CursorType = adOpenKeyset
db_username.LockType = adLockReadOnly
db_username.Open sqlcommand, cnAdo
db_username_role = db_username.Fields.Item(0).Value
' the result of these select is the correct name of the application role
End Sub
"Maidoo oriky" <maidooalex@.msn.com> wrote in message
news:%2368vAA88EHA.2552@.TK2MSFTNGP09.phx.gbl...
>
> actually, i don't know the step taken after creating this role , do i
> activate it from the application, or there's something in the SQl Server
> that let me assign this role to an application (exe file or something
> like that )
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||thnx 4 ur help
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steveHi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steve
Hi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

application login permissions...

background sql2kt on nt5
if I created a sql standard as application login. should i assign
permissions of objects to that accout or should i create a role, assign
permissions of objects to the role, then add that application login account
to the role?
what are some of the common or best practices? can anyone give me some
suggestions? thank you.
steveHi,
I suggest you to create a role and assign permissions to that role.
Say if it is a Finance application, Create a new user role called Finance
and aiisn all permissions to that role. After this you can assign this role
to the application user. If there a new application user comes in later, you
can just assign that role to that user.
(Before creating the role, Just have a look into db_datareader and
db_datawriter role- this may help you out)
Thanks
Hari
MCDBA
"== Steve Pdx==" <lins@.nospam.portptld.com> wrote in message
news:#yfq2fuNEHA.1160@.TK2MSFTNGP09.phx.gbl...
> background sql2kt on nt5
> if I created a sql standard as application login. should i assign
> permissions of objects to that accout or should i create a role, assign
> permissions of objects to the role, then add that application login
account
> to the role?
> what are some of the common or best practices? can anyone give me some
> suggestions? thank you.
> steve
>

Appending Text to a SQL Text Data Type

I am trying to use a cursor to create a mass Text field with the results fro
m
the selections from a series of VarChar(8000) values. I know I need to use
UpdateText, but it only seems to store the 1st one it reads. Can anyone hel
p?
Here's my text:
Declare @.TriggerText nVarChar(4000)
Declare @.ptrval Binary(16)
Declare @.Offset Int
-- Create temporary table to hold Text field
Create Table #tempTrigger
(TextField Text NULL)
Insert Into #tempTrigger Select ''
-- Get Trigger "basis"
Declare curTriggerBasis Insensitive Cursor For
Select c.Text
From sysObjects o (nolock)
Inner Join sysComments c (nolock)
On o.ID = c.ID
Where o.Name = 'cttx_Customer'
Order By ColID
For Read Only
Open curTriggerBasis
Fetch Next From curTriggerBasis Into @.TriggerText
While @.@.Fetch_Status = 0
Begin
Select @.ptrval = TEXTPTR(TextField),
@.Offset = DataLength(TextField)
From #tempTrigger (nolock)
UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
Fetch Next From curTriggerBasis Into @.TriggerText
End
Close curTriggerBasis
Deallocate curTriggerBasis
Select * From #tempTrigger (nolock)> but it only seems to store the 1st one it reads
How are you determining this? What does 'SELECT DATALENGTH(TextField) FROM
#tempTrigger' return?
Happy Holidays
Dan Guzman
SQL Server MVP
"bobnunny" <u17151@.uwe> wrote in message news:59ac1c8e96b9c@.uwe...
>I am trying to use a cursor to create a mass Text field with the results
>from
> the selections from a series of VarChar(8000) values. I know I need to
> use
> UpdateText, but it only seems to store the 1st one it reads. Can anyone
> help?
> Here's my text:
> Declare @.TriggerText nVarChar(4000)
> Declare @.ptrval Binary(16)
> Declare @.Offset Int
> -- Create temporary table to hold Text field
> Create Table #tempTrigger
> (TextField Text NULL)
> Insert Into #tempTrigger Select ''
> -- Get Trigger "basis"
> Declare curTriggerBasis Insensitive Cursor For
> Select c.Text
> From sysObjects o (nolock)
> Inner Join sysComments c (nolock)
> On o.ID = c.ID
> Where o.Name = 'cttx_Customer'
> Order By ColID
> For Read Only
> Open curTriggerBasis
> Fetch Next From curTriggerBasis Into @.TriggerText
> While @.@.Fetch_Status = 0
> Begin
> Select @.ptrval = TEXTPTR(TextField),
> @.Offset = DataLength(TextField)
> From #tempTrigger (nolock)
> UpdateText #tempTrigger.TextField @.ptrval @.Offset 0 @.TriggerText
> Fetch Next From curTriggerBasis Into @.TriggerText
> End
> Close curTriggerBasis
> Deallocate curTriggerBasis
> Select * From #tempTrigger (nolock)|||I've put Print statements in there to check this out. It shows Datalength a
s
4000 everytime except the last one. BUT, like an idiot I was checking the
loop so hard, but the Select statement at the end will only return the first
4000. Once I changed that to DataLength, it showed it had it all.
Thanx!
Dan Guzman wrote:
>How are you determining this? What does 'SELECT DATALENGTH(TextField) FRO
M
>#tempTrigger' return?
>
>[quoted text clipped - 36 lines]