Derived Column or script

Dear All,

I am pretty new to SQL2005 technologies.

I have a table like this:

Name

Code

Peter

10

Eric

20

I am interested in translation of the “code” to “job role”:

Name

Code

Job role

Peter

10

Director

Eric

20

Consultant

I have tried to use Derived Column and also script – but I am unable to get it to work.

Any hints/solution would greatly appreciate.

Best Regards, T

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

|||

Sagestore wrote:

A simple example without NULL checking etc

In the 'Derived Column' task add a new column 'Job Role' and set the expression to be something like: -

[Code] == 10 ? "Director" : [Code] == 20 : "Consultant"

Does not seem like the correct syntax, I think it should be:

[Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )

|||

Do you have the translations stored in a table somewhere? If so, the LOOKUP component can be used.

-Jamie

|||

The translation is hardcoded which is fine.

However the number of options are more than 2 "consultan" and "director" - at least up to 5 different options.

So I need someting like:

Case 10 => Director

Case 20 => Consultant

Case 30 => Admin

Etc.

Thanks for input so far - but still im waiting for a soluion.

T

|||

Michael gave you the solution - you just need to extend it to cover all eventualities:

[Code] == 9999 ? "Something else!" : ([Code] == 30 ? "Admin" : ([Code] == 10 ? "Director" : ( [Code] == 20 ? "Consultant" : "Unknown" )))

-Jamie

|||

Thanks all of you - now I finally got it to work.

I am extremely happy with this kind of support.

Thanks again and take care, T

Deployment worked when it shouldn't have done

I've just had a situation where, when trying to process a cube, it failed because one of my Script Commands referenced a non-existent dimension.

Now I have no problem with that - its easily fixed. My problem is that the cube is obviously faulty so why was I allowed to deploy it? The deployment should surely have failed shouldn't it?

Is this a deployment bug?

Here is the script command:

Scope(Leaves([Currency]), EXCEPT( [Currency Conversion].[Currency Conversion].MEMBERS, {[Currency Conversion].[Currency Conversion].&[<None>]} ))

-Jamie

P.S. Does anyone from the SSAS team in Redmond frequent this forum?

The server has a very flexible, forgiving, and complex system for attempting to resolve name ambiguities. So if you simply provide the text [Currencty Conversion] it may be a dimension name or a hierarchy name or a level name or even a member name. (Yes, if the server can uniquely resolve this to a member name it will although a dimension prefix is a good idea at a minimum.) As a result, until the server has read the data during processing it does not fully bind the names and thus does not know for sure that this is a dimension name that cannot be found.

Yes, people such as myself from the SSAS team in Redmond do frequent this forum.

|||

Good to know - on both counts. Thank you Matt.

-Jamie

sql

Deployment 'utility' script using sqlcmd

Hi all,

I'm looking at creating a sample utility script that will invoking

scripts to deploy some SQL code. For example, a utlity script that will

run a SQL script, and on successful completion, execute the next script.

Having not used SQLCMD at all before, and being very new to SQL2005

(< 1 month) please guide me if there is a better way of invoking

this... For example, a way of avoiding the xp_cmdshell invocation!

The following code invokes a script, but I'm trying to find a way of

getting a return code back from sqlcmd, so I can progress and do the

next, or fail if the return code <> 0 (success).

[code]

--Process to create DB, Tables, and Stored Procedures

set nocount on

DECLARE

@.Error int,

@.ExecCommand varchar(512),

@.FullFilePath varchar(255)

--create the database

BEGIN TRY

SET @.FullFilePath =

'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF

Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB

TescoDNF_SSISPackageManager.sql'

SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '

SELECT @.FullFilePath

UNION

SELECT @.ExecCommand

EXEC (@.ExecCommand)

SELECT @.@.ERROR

SELECT @.Error

END TRY

BEGIN CATCH

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() AS ErrorState,

ERROR_PROCEDURE() AS ErrorProcedure,

ERROR_LINE() AS ErrorLine,

ERROR_MESSAGE() AS ErrorMessage;

GOTO ErrorAbort

END CATCH
ErrorAbort:

[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set

of SQL scripts to, for example, a server. For example, some pseudo-code

Create Database

If Error abort

Create Table1

If Error abort

Create Table2

If Error Abort

Create Stored Procedure1

If Error Abort

ELSE Complete and report success

I do agree that this is something that could be (better) done in a

"proper" coding language like .Net, c# etc. but (a) it's just a simple

utility script (b) it teaches me moore about usage of SQLCMD and (c) I

do not have any skill in any normal programming language, hence I was

planning to write a quick deployment utility with a script.

The idea might be something as ugly as a table structure that has a

list of scripts registered in it, with some sequence logic - like

creating parent tables before children tables - and then a cursor (or a

better method if I can find it) that fetches a sqlcmd filename

execution command, executes it, and on success fetches the next one

based on the sequence logic.

I can probably do all of that in about 4 hours in T-SQL, if I can find

a way to confirm the successful execution of the previous command....|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files

and execute them, and not merge them all into a single large script.

So, I want utility script to do this:

Run external sql script

On failure abort, on Success

Run external sql script

On Failure abort, On Success

...

You being to see why I referred to a cursor?

The point is that the utility script wouldn't contain any of the client

SQL commands - it would fetch them by referring to the table, and

fetching the path to the SQL file, and building a SQLCMD to execute

that script

I guess, as you say, I could add a generic update ##SQLScriptTracker

table, then check it on the new execution, or abort. I had hoped for a

neater solution - i.e. SQLCMD being able to return a returncode that it

gets from a SQL file it ran....|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as

well, since with the batch file I have to add a file each time.

Thanks|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

Deployment 'utility' script using sqlcmd

Hi all,
I'm looking at creating a sample utility script that will invoking scripts to deploy some SQL code. For example, a utlity script that will run a SQL script, and on successful completion, execute the next script.
Having not used SQLCMD at all before, and being very new to SQL2005 (< 1 month) please guide me if there is a better way of invoking this... For example, a way of avoiding the xp_cmdshell invocation!
The following code invokes a script, but I'm trying to find a way of getting a return code back from sqlcmd, so I can progress and do the next, or fail if the return code <> 0 (success).
[code]
--Process to create DB, Tables, and Stored Procedures
set nocount on
DECLARE
@.Error int,
@.ExecCommand varchar(512),
@.FullFilePath varchar(255)
--create the database
BEGIN TRY
SET @.FullFilePath = 'D:\Documentation\Projects\Integration Services\BIDS Projects\Tesco DNF Integration Services\TescoDNF ProductPromo\SQL Code\OBJECTS\Create DB TescoDNF_SSISPackageManager.sql'
SET @.ExecCommand = 'xp_cmdshell ''sqlcmd -S Rgalbraith\SQL2005_1 -i "'+@.FullFilePath+'"'' '
SELECT @.FullFilePath
UNION
SELECT @.ExecCommand
EXEC (@.ExecCommand)
SELECT @.@.ERROR
SELECT @.Error
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage;
GOTO ErrorAbort
END CATCH

ErrorAbort:
[/code]

Hello
I didn't quite understand what are you trying to achieve. You have some sequence of sql scripts that need to be executed against a server one after another, if no error occurs?
Then, what if an error occurs? Maybe there is some branching in the scripts? I.e. if script1.sql succeedes, then execute script2.sql, else execute script3.sql. If script3.sql fails, restore a database backup...
And why are you doing this from SQL? Isn't using a programming language more effective?

|||My thought had been to have a simple T-SQL script that 'deploys' a set of SQL scripts to, for example, a server. For example, some pseudo-code
Create Database
If Error abort
Create Table1
If Error abort
Create Table2
If Error Abort
Create Stored Procedure1
If Error Abort
ELSE Complete and report success
I do agree that this is something that could be (better) done in a "proper" coding language like .Net, c# etc. but (a) it's just a simple utility script (b) it teaches me moore about usage of SQLCMD and (c) I do not have any skill in any normal programming language, hence I was planning to write a quick deployment utility with a script.
The idea might be something as ugly as a table structure that has a list of scripts registered in it, with some sequence logic - like creating parent tables before children tables - and then a cursor (or a better method if I can find it) that fetches a sqlcmd filename execution command, executes it, and on success fetches the next one based on the sequence logic.
I can probably do all of that in about 4 hours in T-SQL, if I can find a way to confirm the successful execution of the previous command....
|||

Ok I got it.
You can have a "Version" table, then number your scripts so that each of them updates the version. Before executing each portion of code, you can check the current version to be exactly the number you need.
e.g.
Create Database
Create table Version(VersionNum varchar(255), ChangeID int)
insert into Version(VersionNum, ChanegeID) values("1", @.ID)
GO
If (select VersionNum where ChangeID=@.ID)="1"
BEGIN
Create Table1
Update Version set VersionNum = "2" where ChangeID=@.ID
END
GO
If (select VersionNum where ChangeID=@.ID)="2"
...
So, basicly you just update the version number as the last command of each batch. Then, you check for the appropriate version number at the beginning of the next block.
This way you can even do some "branching". Even more, if your scripts fails, you can check what scripts have succeeded and what scripts have not, simply by looking at the VersionNo field.

Anyhow, I'd strongly reccomend using ordinal programming language if you are going to use that utility more than once and it MIGHT become somehow complicated.

|||Well, in a sense. The point is though that I want to fetch sql files and execute them, and not merge them all into a single large script.
So, I want utility script to do this:
Run external sql script
On failure abort, on Success
Run external sql script
On Failure abort, On Success
...
You being to see why I referred to a cursor?
The point is that the utility script wouldn't contain any of the client SQL commands - it would fetch them by referring to the table, and fetching the path to the SQL file, and building a SQLCMD to execute that script
I guess, as you say, I could add a generic update ##SQLScriptTracker table, then check it on the new execution, or abort. I had hoped for a neater solution - i.e. SQLCMD being able to return a returncode that it gets from a SQL file it ran....
|||

Why dont go for Batch files (.bat). There you can execute the individual script files one by one using the SQLCMD. And for aborting when error occurs, check the ERRORLEVEL, if its not 0 then quit execution or skip to other location using GOTO.

echo Backup database
sqlcmd -S(local) -U<uid> -P<pwd> -i"backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp

echo Update database
sqlcmd -S(local) -U<uid> -P<pwd> -i"create_proc.sql"
IF ERRORLEVEL 1 GOTO abort

echo Update customer data
sqlcmd -S(local) -U<uid> -P<pwd> -i"update_customer_data.sql"
IF ERRORLEVEL 1 GOTO abort_with_restore

:abort_bkp
echo Error backup database. Setup aborted

:abort_with_restore
echo Error updating data. Restoring database...
sqlcmd -S(local) -U<uid> -P<pwd> -i"restore_db.sql"
IF ERRORLEVEL 1 GOTO res_falied
...
...

|||

hmm ... it seems as thought ERRORLEVEL is only set on the SUCCESS/FAILURE of the SQLCMD invocation, and not based on the SUCCESS/FAILURE of the invoked sql commands?

for example:

batch CALLBACKUP.BAT file contents:

echo Backup database
sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
IF ERRORLEVEL 1 GOTO abort_bkp
IF ERRORLEVEL 0 GOTO done

:abort_bkp
echo Error backup database. Setup aborted

:done
echo all done now

backup_db.sql contents

backup database DataStore2 to disk = 'D:\BackupDatabase.bak'

execution results:

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Msg 911, Level 16, State 11, Server RGALBRAITH\SQL2005_1, Line 1
Could not locate entry in sysdatabases for database 'DataStore2'. No entry found with that name. Make sure that the name
is entered correctly.
Msg 3013, Level 16, State 1, Server RGALBRAITH\SQL2005_1, Line 1
BACKUP DATABASE is terminating abnormally.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>IF ERRORLEVEL 0 GOTO done

D:\>echo all done now
all done now

A sample of sqlcmd failing was:

D:\>callbackup

D:\>echo Backup database
Backup database

D:\>sqlcmd -SRGalbraith\SQL2005_1 -E -i"d:\backup_db.sql"
Sqlcmd: 'd:\backup_db.sql': Invalid filename.

D:\>IF ERRORLEVEL 1 GOTO abort_bkp

D:\>echo Error backup database. Setup aborted
Error backup database. Setup aborted

D:\>echo all done now
all done now

...

As is probably obvious, I'm not much of a batch file coder :-), but the jmist of it is there - when the SQLCMD failed (file not found) then it reported error, but when the SQL script failed (database not found) no error was reported. Is there a way around that?

|||

You have to set the -b option for the SQLCMD. -b makes the batch abort with an error if the script fails. So you would write this...

@.ECHO OFF

@.echo.
@.echo Backup database
sqlcmd -S.\sqlexpress -E -i"backup_db.sql" -b
IF %ERRORLEVEL% NEQ 0 GOTO err_bkp_failed

:success
echo Database update successful
goto end

:err_bkp_failed
echo Backup failed. Aborting...
goto end

:end

HTH

|||hmm - good to know! still going to investiage the other options as well, since with the batch file I have to add a file each time.
Thanks
|||

Visual Studio .Net 2003 had a "create batch file" command which was beautiful for creating this batch file to process the sequence of sql scripts that you create.

I still use it today. But it seems we are in need to migrate to Visual Studio 2005, and this feature has been disabled now.

Do you have a more elegant solution now?

|||

Actually you don't have to modifiy the bat script each time. I have been using bat scripts to do exactly this for years.

The shell support the For Each looping structure which will set a shell variable to each file name that meet's a spec.

For Each %%1 in *.sql <execute a dos command>

I have been using the OSQL command line utility for years like this. I guess I will have to update to SQLCMD now.

You can find out the details of shell commands by going to "My Computer" <Help> and searching for "For Each"

You can find out about OSQL in BOL

|||

I've been searching solution on catching MS SQL abortion errors in a launching batch file. With option '-b', at least the batch file could return error code 1 instead of 0. Thanks for the hint!

Still, I'd appreciate if anyone could offer answer on capturing the stdout error in the batch file. My problem is that once the sql statement is aborted, it immediately exits from the erroneous line, ignores the rest code in the same script. Therefore, no error could be saved.

Also, I found that in some env. the 'sqlcmd' is not recognized (SQL Server 2000?) but 'osql' or 'isql'. Are there any differences among them (must be, but I don't know).

sql

Deploying SSRS

Is there a way to deploy reports through a script or scheduled task?
Thanks
RobOn 8 jun, 00:07, Rob <R...@.discussions.microsoft.com> wrote:
> Is there a way to deploy reports through a script or scheduled task?
> Thanks
> Rob
Rob,
You can use the rs.exe utility for this.
Ben|||Ben,
Check out the site: www.sqldbatips.com
The Reporting Services Scripter is a good help and creates a lot of RS
scripts that can help in the learning process.
Reeves
"ben.koolstra@.besam.nl" wrote:
> On 8 jun, 00:07, Rob <R...@.discussions.microsoft.com> wrote:
> > Is there a way to deploy reports through a script or scheduled task?
> >
> > Thanks
> > Rob
> Rob,
> You can use the rs.exe utility for this.
> Ben
>

Deploying Analysis Server Project to another domain

I am looking for a way to deploy an Analysis Server 2005 project to a remote location. I can generate and XMLA create script, FTP the script to the remote location, and execute it to create the cube database on the other server.

However, the security roles inthe XMLA file contains SIDs instead of Active Directory Security group names. The server I am moving this database to is in another Domain and so the SIDs don't correspond to the same users. However, the Active Directory group names on the remote server are exactly the same as the Active Directory group names in my local domain. (The group names are the names of companies).

I am looking for a way to deploy this project to my remote server with the SIDs converted to the Active Directory group names.

Does anyone have any ideas? This type of deployment scenario will be quite common in my development environment.

I am using SQL2005 SP2, along with Visual Studio 2005.

Have a look at the Analysis Services Deployment Wizard, it has options for either

deploying the roles in full|||

I'll throw in my two cents. If the XMLA for your role contains:

<Members>

<Member>

<Name>YOURDOMAIN\yourusername</Name>

<Sid>S-1-5-21-4211028405-896031999-4015973603-1183</Sid>

</Member>

</Members>

I believe that you can cut out the Sid entirely. The following XMLA works fine for me:

<Members>

<Member>

<Name>YOURDOMAIN\yourusername</Name>

</Member>

</Members>

As for whether you can do without YOURDOMAIN\ in the above, I'm not sure. I know that if the group is local to the computer you're, then leaving off the domain works. But if it's a domain group, I'm guessing you've got to specify the domain. Worth testing, though.

Deploying Analysis Server Project to another domain

I am looking for a way to deploy an Analysis Server 2005 project to a remote location. I can generate and XMLA create script, FTP the script to the remote location, and execute it to create the cube database on the other server.

However, the security roles inthe XMLA file contains SIDs instead of Active Directory Security group names. The server I am moving this database to is in another Domain and so the SIDs don't correspond to the same users. However, the Active Directory group names on the remote server are exactly the same as the Active Directory group names in my local domain. (The group names are the names of companies).

I am looking for a way to deploy this project to my remote server with the SIDs converted to the Active Directory group names.

Does anyone have any ideas? This type of deployment scenario will be quite common in my development environment.

I am using SQL2005 SP2, along with Visual Studio 2005.

Have a look at the Analysis Services Deployment Wizard, it has options for either

deploying the roles in full|||

I'll throw in my two cents. If the XMLA for your role contains:

<Members>

<Member>

<Name>YOURDOMAIN\yourusername</Name>

<Sid>S-1-5-21-4211028405-896031999-4015973603-1183</Sid>

</Member>

</Members>

I believe that you can cut out the Sid entirely. The following XMLA works fine for me:

<Members>

<Member>

<Name>YOURDOMAIN\yourusername</Name>

</Member>

</Members>

As for whether you can do without YOURDOMAIN\ in the above, I'm not sure. I know that if the group is local to the computer you're, then leaving off the domain works. But if it's a domain group, I'm guessing you've got to specify the domain. Worth testing, though.

Deny Anything to sa Account in SQL Express?

No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"JohnJohn" <JohnJohn@.discussions.microsoft.com> wrote in message
news:88E5AC42-E241-4365-9D82-5D000664A2F3@.microsoft.com...
> Hello. My client is distributing an application that uses SQL Server 2005
> Express and mixed mode authentication. Most, if not all, of our users
> will
> be installing SQL Server 2005 Express only for our application.
> We want to be able to prevent ANY users (including sa) from being able to
> select, insert, create tables, views, etc. except through the application.
> Is this even possible?
> Thanks,
> JohnHello. My client is distributing an application that uses SQL Server 2005
Express and mixed mode authentication. Most, if not all, of our users will
be installing SQL Server 2005 Express only for our application.
We want to be able to prevent ANY users (including sa) from being able to
select, insert, create tables, views, etc. except through the application.
Is this even possible?
Thanks,
John|||No.
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"JohnJohn" <JohnJohn@.discussions.microsoft.com> wrote in message
news:88E5AC42-E241-4365-9D82-5D000664A2F3@.microsoft.com...
> Hello. My client is distributing an application that uses SQL Server 2005
> Express and mixed mode authentication. Most, if not all, of our users
> will
> be installing SQL Server 2005 Express only for our application.
> We want to be able to prevent ANY users (including sa) from being able to
> select, insert, create tables, views, etc. except through the application.
> Is this even possible?
> Thanks,
> John