Controlling Access

Is there a method of only allowing users to access SQL
Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
I am not referring to the "Application Roles" available
within SQL Server, rather I would like the following to
take place: -
Example 1.
User connects to SQL Server Database, by either using
Commercial Software or Bespoke System, using "NT
Authentication".
SQL Server recognises the user and the Application/System
and allows access to the Database.
Example 2.
User connects to SQL Server Database, by linking/Importing
Tables to a new Database Document, using "NT
Authentication".
SQL Server recognises the user but not the
Application/System and denies access to the Database.
I am using SQL Server 7.0, although we will soon be
migrating to 2000.
TIA
Tony C.Unfortunately no. You could monitor for connections after
the fact, e.g. have a job that checks who is connected using
what application and kill processes that are connected using
the non-approved applications. But you can't really control
access based on what application is being used.
-Sue
On Fri, 2 Apr 2004 01:34:33 -0800, "Tony C"
<tony.chorleyRUBBISH@.mcalpineplc.com> wrote:

>Is there a method of only allowing users to access SQL
>Server Databases USING SPECIFIED APPLICATIONS/DATABASES?
>I am not referring to the "Application Roles" available
>within SQL Server, rather I would like the following to
>take place: -
>Example 1.
>User connects to SQL Server Database, by either using
>Commercial Software or Bespoke System, using "NT
>Authentication".
>SQL Server recognises the user and the Application/System
>and allows access to the Database.
>Example 2.
>User connects to SQL Server Database, by linking/Importing
>Tables to a new Database Document, using "NT
>Authentication".
>SQL Server recognises the user but not the
>Application/System and denies access to the Database.
>I am using SQL Server 7.0, although we will soon be
>migrating to 2000.
>TIA
>Tony C.

containstable(isabout...) query length

Hi ,
i am using a very long query in a containstable predicate with an isabout
search method.
somthing like this :CONTAINSTABLE( dbo.TBL_FTS_DATA,FreeTextDump,
'ISABOUT(....
my problem is that when my' ISABOUT is more then 8000 chars long the query
fails.
it seems that the isabout portion of the command is limited to 8000 chars.
is there any way around this?
thanks
shay
No, this is hard coded. Have you seen this kb?
http://support.microsoft.com/default...b;en-us;317486
A problem like this was solved with SP3. I take it you are above Sp3. One of
the work arounds is that you could do two separate ContainsTable queries and
AND or OR them.
Another problem is with near which is hard coded as 1366 words IIRC.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"shay cohen" <shay@.infospheraltd.com> wrote in message
news:egjgSYEXFHA.3760@.TK2MSFTNGP15.phx.gbl...
> Hi ,
> i am using a very long query in a containstable predicate with an isabout
> search method.
>
> somthing like this :CONTAINSTABLE( dbo.TBL_FTS_DATA,FreeTextDump,
> 'ISABOUT(....
> my problem is that when my' ISABOUT is more then 8000 chars long the query
> fails.
> it seems that the isabout portion of the command is limited to 8000 chars.
> is there any way around this?
> thanks
> shay
>

Contains Characters or Numbers Method

Hello,

I was wondering if there is any method that I can use to determine if a field (defined as text) has any character fields or is really a number. I want to figure out if a field is truly all numeric, and out of curiosity, was wondering if there was a way in SQL or T-SQL.

Thanks.

look at SQL Server function IsNumeric. It return 1 if it is, otherwise 0. You can use it with CASE WHEN. If it is numeric, get it, otherwise, set to other value such as 0.

|||

You can check whether the following will work for you:

WHEREyourColumnNOTLIKE'%[^0-9]%'

or

WHERE IsNumeric(yourColumn)=1

|||

Hey,

I didn't know there was an IsNumeric method... and I'm using SQL 2000 so regular expression's won't work for me. Sorry I should have mentioned the 2000 part.

Thanks.

Consolidating multiple lookups

In many of my packages I have to translate an organizational code into a surrogate key. The method for translating is rather convoluted and involves a few lookup tables. (For example, lookup in the OrgKey table. If there is a match, use that key; if not, do a lookup of the first 5 characters in the BUKey table. If there is a match, use that key; if not, do a lookup of the first 2 characters... You get the idea.)

Since many of my packages use this same logic, I would like to consolidate it all into one custom transformation. I assume I can do this with a script transform, but then I'd lose all the caching built into the lookup transforms.

Should I just bite the bullet, and copy and paste the whole Rube Goldberg contraption of cascading lookup transforms into each package? Or is there a better solution I'm overlooking?The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

A second unit of re-use is a custom transform (not a script transform). THe script transform has "cut and paste" inheritence, which is no inheritance at all, so a change to one implementation forks it from the original, or both from both. That kind of re-use does not sounds as relevant to your scenario.

Nevertheless, I tend to the look at script transformations (for the most part) as proof of concept for custom transforms. If and once they are converted over to Custom transforms, then there is no more cut-and-paste inheritence (i.e. you can fix bugs once, not N times) Without taking advantage of the two re-usable objects (packages and custom components), forking will likely occur.|||

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.|||

Hubajube wrote:

jaegd wrote:

The bullet to bite is to use sub-packages more frequently.

If multiple packages use the same lookup logic, and since the package it the unit of re-use, use the unit of re-use (the package)

Maybe I'm missing something here. Since the source and destination deal with physical files only, are we talking about writing and reading to temp flat files or something? There isn't a direct way to send a data from a package to a subpackage is there?

That said, it sounds like a custom transform is probably the way to go.

Correct. The only easy way to "send" to a subpackage is via staging tables (either database tables or flat files)