Showing posts with label example. Show all posts
Showing posts with label example. Show all posts

Monday, March 26, 2012

generic user versus named user

Good morning,
We are having a debate here about development. We in
programming would like to create a generic user account
for example TransDev and have the users use that to create
an ODBC connection to the database versus having it
reference a person. The network admin group says that's a
horrible thing to request. I didn't find such an instance
on your website which answered a lot of my other
questions. Do have a perspective from a security
standpoint where such a request would be a bad thing?
Thanks in advance.
Laura MayerThe advantage of individual accounts is that you can control security per
user. Otherwise, you have no way to restrict access because the
login/password is well known.
A best practice is to create roles and grant permissions only to roles. You
can then control security via user role membership. This works especially
well with Windows authentication since you can login to SQL Server with
individual user credentials without bothering with a separate account.
Hope this helps.
Dan Guzman
SQL Server MVP
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:bbda01c4082c$49c97820$a401280a@.phx.gbl...
> Good morning,
> We are having a debate here about development. We in
> programming would like to create a generic user account
> for example TransDev and have the users use that to create
> an ODBC connection to the database versus having it
> reference a person. The network admin group says that's a
> horrible thing to request. I didn't find such an instance
> on your website which answered a lot of my other
> questions. Do have a perspective from a security
> standpoint where such a request would be a bad thing?
> Thanks in advance.
> Laura Mayersql

generic account vs. named account

Good morning,
We are having a debate here about development. We in
programming would like to create a generic user account
for example TransDev and have the users use that to create
an ODBC connection to the database versus having it
reference a person. The network admin group says that's a
horrible thing to request. I didn't find such an instance
on your website which answered a lot of my other
questions. Does anyone have a perspective from a security
standpoint where such a request would be a bad thing?
Thanks in advance.
Laura Mayer
Database Analyst
C&S Wholesale Grocers
802-257-6855first, if everyone is using the same id, it's much more difficult to
tell who screwed something up. also much easier for that login and
password to fall into the wrong hands.
since your network admins seem to hate your idea, force them to do some
work (if they run your authentication servers). have them create an
ntgroup and add all the users that need access to the db. then you give
the ntgroup the permissions it needs in the db. then when someone new
needs access to the db or someone leaves, the network admins will have
to go change the ntgroup and you won't have to do anything.
Laura wrote:

> Good morning,
> We are having a debate here about development. We in
> programming would like to create a generic user account
> for example TransDev and have the users use that to create
> an ODBC connection to the database versus having it
> reference a person. The network admin group says that's a
> horrible thing to request. I didn't find such an instance
> on your website which answered a lot of my other
> questions. Does anyone have a perspective from a security
> standpoint where such a request would be a bad thing?
> Thanks in advance.
> Laura Mayer
> Database Analyst
> C&S Wholesale Grocers
> 802-257-6855|||Thanks for that. What do you think about the ODBC
connection? Currently we are setting up the connection
using a persons name with the appropriate connections. I
would like to have a generic account. Could I have them
create that and put it in the group? Would that be an
appropriate middle ground?
Thanks
Laura
>--Original Message--
>first, if everyone is using the same id, it's much more
difficult to
>tell who screwed something up. also much easier for that
login and
>password to fall into the wrong hands.
>since your network admins seem to hate your idea, force
them to do some
>work (if they run your authentication servers). have
them create an
>ntgroup and add all the users that need access to the
db. then you give
>the ntgroup the permissions it needs in the db. then
when someone new
>needs access to the db or someone leaves, the network
admins will have
>to go change the ntgroup and you won't have to do
anything.
>
>Laura wrote:
>
create
a
instance
security
>.
>|||not really. you would put the ntusers into the ntgroup. then when
setting up the odbc, you'd choose windows authentication instead of
sqlserver authentication.
for example, you have users joe and john both members of domain1.
you create a group in domain1 called sqlgrp.
add domain1\joe and domain1\john to domain1\sqlgrp.
assign db permissions to domain1\sqlgrp.
set up odbc to use windows authentication.
that way there are no additional passwords to manage, only one set of db
permissions to manage, greater security with windows accounts versus
sqlserver accounts.
Laura wrote:
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security|||Adding to the comments of 'ch', from your ODBC connection string you can use
the:
Trusted_Connection=yes;
option to force the authentication to be "trusted". Stay away from generic
access accounts they can not be monitored, creating a huge security hole
(IMO).
Steve
"Laura" <anonymous@.discussions.microsoft.com> wrote in message
news:bb1501c40834$75b42b80$a501280a@.phx.gbl...
> Thanks for that. What do you think about the ODBC
> connection? Currently we are setting up the connection
> using a persons name with the appropriate connections. I
> would like to have a generic account. Could I have them
> create that and put it in the group? Would that be an
> appropriate middle ground?
> Thanks
> Laura
> difficult to
> login and
> them to do some
> them create an
> db. then you give
> when someone new
> admins will have
> anything.
> create
> a
> instance
> security

Friday, March 23, 2012

Generating wrapper code for SQL Server Stored Procedure

Hi

How Can I Generating wrapper code for SQL Server Stored Procedure ??

If You will Go To The Following link you will see an example for Generating wrapper code for Oracle Database .. And Also the author say there is built in tool for Generating wrapper code for SQL Server
http://www.codeproject.com/vb/net/OracleSPWrapper.asp
my question .. where is this tools ?

and thanks with my regarding

FraasYou are probably talking about the Data Adapter wizard in the design view. See this walkthrough:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon/html/vbwlkWalkthroughUsingDataGridWebControlToReadWriteData.asp

Monday, March 19, 2012

generating an unique number within SSIS - looking for good examples

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Here is some script to generate a new GUID.

Code Snippet

Public Sub Main()

' Generate a globally unique identifier

Dim myGuid As Guid = Guid.NewGuid()

MsgBox(myGuid.ToString())

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Sergio wrote:

Does anybody know how to generate a new identity value from within SSIS. Can anybody point me to a good example using a script component?

Thanks you very much!!!!

Sergio

Like this? http://www.ssistalk.com/2007/02/20/generating-surrogate-keys/

What do you mean, "generate a new identity value"?

Friday, March 9, 2012

Generate values between a starting and stopping point (time values).

[RS 2005]

Given the starting and stopping points (time values), how do I generate values between these points.

For example, if I have 08 (representing Hour) as a starting point and 12 as a stopping point.

From this I would like to generate a data sequence like 08, 09, 10, 11, and 12.

So how do I accomplish this? In SQL or in the RS?

The only thing I can think of is using a WHILE loop and a temporary table in SQL (not to keen on doing this).

//H?kan

Use custom code in the report to achieve this. Take the start and end points as input parameters as you might be already doing. Then pass them to the function in custom code and write VB code inside that function to generate all intermediate values. Then store that array in a global variable in the custom code itself which you could refer from anywhere in your report.

Shyam

|||I would either use a function or a stored procedure or a plain query for this.

CREATE FUNCTION dbo.NumberRange

(

@.Starting INT,

@.End INT

)

RETURNS @.Numbers TABLE

(

Number INT

)

AS

BEGIN

WHILE @.Starting <= @.End

BEGIN

INSERT INTO @.Numbers VALUES (@.Starting)

SET @.Starting = @.Starting + 1

END

RETURN

END

SELECT * FROM dbo.NumberRange(1,10)

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||Hakan is not keen on using SQL and WHILE loops and tables.|||I know, thats why I write the function for him.|||

H?kan,

do you need the data to be included in the dataset so that it can be rendered on the report? If so, you will have to generate it in the SQL as mentioned by Jens. If all you need to do is refer to the intermediate values from other expressions then a global array is fine, but make sure you have null and bounds checks in your custom code before trying to access the array.

|||

I guess the function also has the WHILE loop and tables.

Instead he can have a master table with just one column and values from 00 to 100 and just select the necessary numbers from that table between the given start and end.

Shyam

Sunday, February 19, 2012

generate all possible subsets from master set?

Hi.

I have a master set of values, lets say

(1,2,3)

for example. I want to use T-sql to generate all possible subsets of this master set. Order of values is unimportant, what I want is unique sets, i.e.

(1)
(2)
(3)
(1,2)
(1,3)
(2,3)
(1,2,3)

thx.Can I ask why?

In a table or a string?

I'm thinking CROSS JOIN...|||Back up a sec', manster.

Do 1, 2, and 3 represent columns? In that case, you would write a cross-join query as suggested by Brett.

But if 1, 2, 3, ... to N represent values in a single column, and you want all the permutations, you will need to write a stored proc that loops through the dataset N times.

blindman|||thanks, all.

Cross join, most likely. I'm working this up from scratch to complete a current project. The higher-ups are handing me a list of fields from our DB, fifteen max, and then asking for all possible subsets of these fifteen, after which we test the subsets to see which offer the most "value."

if someone can offer a cross join example using numbers 1,2,3 as above, that would be great. I'll just key the number back to the actual field names.

thx.|||That sounds bizzare!

Can you post the DDL of the table?

And some sample data?

And are you're higher ups high?|||yes, I know it sounds bizarre, but you'd have to see the data in the table to understand why they're asking for this, and I can't show it.

bottom line is this: I have a list of 15 fields in a single table and I need to generate all possible subsets of these 15 fields.

table1

F1 F2 F3 etc.

output is all possible subsets of these fields:

F1
F2
F3
F1 F2
F2 F3
F1 F3
F1 F2 F3

thx!|||Have a look at Arnolds Reply...you'd need to marry rows to numbers somehow

You'll also need
CREATE TABLE Numbers(n int)
GO
DECLARE @.x int
SET NOCOUNT ON
SELECT @.x = 1
WHILE @.x < 101 BEGIN
INSERT INTO Numbers (n) SELECT @.x
SELECT @.x = @.x + 1
END
SET NOCOUNT OFF

To play with it...vary cool though...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30752|||What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman|||I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx|||Originally posted by blindman
What kind of processing power are your higher-ups prepared to pay for?

...because you are looking at something over a QUADRILLION permutations, depending on how many values are in your columns.
Start it running, and then go get yourself a cup of coffee...in Brazil.

blindman

LOL...

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=30646

Some Calculations here...

Say we have a table of 10,000 measly rows...

You want to join col1 to each of the other 14 columns to represent permutations

OK Col1 + Col2 = 100,000

So i't like Col1 , Row 1 + Col2, Every row

Do that 14 more times for all the other columns...1.4 million rows

And we haven't yet begun! That's still just 2 dimensions of the data

Now lets see, Three dimensions

I guess that would be Col1, Row 1, Col2, Row 1, Col3, All the Rows
Then Col1, Row1, Col2, Row2, Col3 All the rows of data

So what that would be 10,000 * 10,000 * 10,000

Blindman help me out here, sound right?

The for all other cols * 14, or 14 million?

4 Dimensions...

140,000,000? Just a guess...

That about right?|||here's a link to a web page that does what I'm trying to do, but this routine truncates at 500 entries returned...

mine is n=15, k=15

and I'm looking for lex order, list of elements.

http://www.theory.cs.uvic.ca/~cos/gen/comb.html|||Originally posted by manster
I'm sorry if I haven't been clear on this. I only need all possible combinations of the FIELD NAMES, not the actual per-record data in the fields.

Once I get all possible combinations of the field names, I can then look at which fields I'll actually use in succeeding queries.

thx

Was busy doing calcs when you posted...

Are you trying to build a "pick list" of what fields you want to select for an end user..

Even still that's a lot of combinations..its 15 factorial..more actually the way you want it..

DECLARE @.x BIGINT
SELECT @.x = 1*2*3*4*5*6*7*8*9*10*11*12--*13*14*15
SELECT @.x

I get an arithmetic overflow at 13...|||I guess you could call it a pick list as long as all subsets are represented.

I'm looking back at the original field list and see that the main required fields number about 10, so 15 was an over-estimation on my part.

thx.|||But that's only bit data...is that what you're looking for?|||yes, just the subsets taken from the "master" list of fields. I'm only interested in the fieldname subsets, not the records in the db at this point.|||You are still talking about over 4 million permutations.

blindman

Generate a cube in Standard Edition

I would like to know if there are some example of how to generate a cube by c#. I am using MS SQL Standard Edition. The AMOAdventureWorks example, just run in Enterprise edition, because of Semi-Additive measures.

Tks!!

Hello! In tools and samples you also have a standard edition version of the Adventure Works cube project

You can probably find it here: c:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project

HTH

Thomas Ivarsson

|||

Ok, i found it, but I want another thing...

I am working on a project that require me to build cubes programmatically i.e., using AMO object and C#. I am trying to find a sample that builds cubes using C# in Standard Edition.

Tks!!!

|||

I do not know if there is a difference in programatically creating a cube between Standard and Enterprise, but look here:

http://technet.microsoft.com/en-us/library/ms345091.aspx

|||

This example is perfect, I am using it, but when I try to execute it, or another example in SQL Standard Edition, the same error occurred in this line, problemas wuth the Semi-Additive Measures :

//--

cube.Update (UpdateOptions.ExpandFull);

//--

The Semi-Additive Measures feature of MS SQL Server 2005 is not supported in its Standard Edition SKU. How do solve this issue? Will I have to by Enterprise Edition SKU ?

This is documented here: http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
Tks!

|||

I just figured this out.

Comment out this stuff in CreateCurrencyRatesMeasureGroup()

//average of children is not allowed.

//meas.AggregateFunction = AggregationFunction.AverageOfChildren;

//last non empty is not allowed.

//meas.AggregateFunction = AggregationFunction.LastNonEmpty;

Generate a cube in Standard Edition

I would like to know if there are some example of how to generate a cube by c#. I am using MS SQL Standard Edition. The AMOAdventureWorks example, just run in Enterprise edition, because of Semi-Additive measures.

Tks!!

Hello! In tools and samples you also have a standard edition version of the Adventure Works cube project

You can probably find it here: c:\Program Files\Microsoft SQL Server\90\Tools\Samples\AdventureWorks Analysis Services Project

HTH

Thomas Ivarsson

|||

Ok, i found it, but I want another thing...

I am working on a project that require me to build cubes programmatically i.e., using AMO object and C#. I am trying to find a sample that builds cubes using C# in Standard Edition.

Tks!!!

|||

I do not know if there is a difference in programatically creating a cube between Standard and Enterprise, but look here:

http://technet.microsoft.com/en-us/library/ms345091.aspx

|||

This example is perfect, I am using it, but when I try to execute it, or another example in SQL Standard Edition, the same error occurred in this line, problemas wuth the Semi-Additive Measures :

//--

cube.Update (UpdateOptions.ExpandFull);

//--

The Semi-Additive Measures feature of MS SQL Server 2005 is not supported in its Standard Edition SKU. How do solve this issue? Will I have to by Enterprise Edition SKU ?

This is documented here: http://www.microsoft.com/sql/editions/enterprise/comparison.mspx
Tks!

|||

I just figured this out.

Comment out this stuff in CreateCurrencyRatesMeasureGroup()

//average of children is not allowed.

//meas.AggregateFunction = AggregationFunction.AverageOfChildren;

//last non empty is not allowed.

//meas.AggregateFunction = AggregationFunction.LastNonEmpty;