Showing posts with label specific. Show all posts
Showing posts with label specific. Show all posts

Tuesday, March 27, 2012

Executing packages in a specific sequence

I am facing some issues while working on SSIS in VS2005,

The Scenario is we have 35 excel files from which we have to import data

to Sql 2005.

We have to execute these packages in a specific sequence so we are not

using For Each Loop Control.

Now the Problem is this that we have 35 executables as an output that is

corresponding to each package, and from each executable we can Change

excel path and Sql Connection String.

The problem is that one has to open each executable to set the path; we

wanted to keep them Configurable like , as if we can have any variable

define in the packages for path, and in the packages we use that variable

and append the XLS file name to it like ..

@.PathVariable+ test.xls

And from the Configuration file at run time we can set the path to this

variable like

@.Path Variable="C: /Windows/ Test Folder/"

And then executable picks the path from there.

Is there any possibility to implement any thing similar, we have tried but

its not working, any help in this regard will be really appreciating.

Its really urgent, I will wait for feedback on this.

Yeah you can do that. just use an expression on the ConnectionStrig property of the connection managers. This should give you some clues:

SSIS Nugget: Dynamically set a logfile name
(http://blogs.conchango.com/jamiethomson/archive/2006/10/05/SSIS-Nugget_3A00_-Dynamically-set-a-logfile-name.aspx)

-Jamie

Monday, March 19, 2012

Execute trigger as a specific user

Hi,
Within my database I have some triggers on the tables (in database a)
which execute stored procedures and alter data in a separate database
(database b).
I have a security issue here as when ever I alter the tables in
database a, I have to make sure that the user exists in database b. If
not, the transaction will fail.
Does anyone know if it is possible to force a trigger to execute a
specified user? This would allow me to hardcode the user in the
trigger in database a to use a username with is already established
within database b....
Any suggestions would be great as I've been struggling with this for a
long long time.
All the best
AllanThere is no 'execute as' functionality in SQL 2000. A user must have a
security context in the other database in order to access objects therein.
If you don't want to add the user to databaseB too, an alternative is to
enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
that have not been granted access to databaseB explicitly can then access
the database using the guest user and are limited to those permissions
granted to the guest.user or public role.
However, you probably don't want to grant object permissions to public or
guest. In this case, you can enable 'db chaining' in both databases. This
will honor cross-database ownership chaining so that permissions are not
needed on objects in databaseB referenced by your proc as long as all
objects are owned by the same user. If your objects are owned by 'dbo',
both databases also need to be owned by the same login so that the 'dbo'
user ownership chain is unbroken..
Note that you should enable cross-database chaining only if you fully
understand the security implications. You need to fully trust users that
have permissions to create dbo-owned objects in those databases. Never
enable cross-database chaining in an sa-owned database unless only symin
role members can create dbo-owned objects.
Hope this helps.
Dan Guzman
SQL Server MVP
"Allan Martin" <allanmartin@.ntlworld.com> wrote in message
news:a6d765d6.0502050820.70385c2e@.posting.google.com...
> Hi,
> Within my database I have some triggers on the tables (in database a)
> which execute stored procedures and alter data in a separate database
> (database b).
> I have a security issue here as when ever I alter the tables in
> database a, I have to make sure that the user exists in database b. If
> not, the transaction will fail.
> Does anyone know if it is possible to force a trigger to execute a
> specified user? This would allow me to hardcode the user in the
> trigger in database a to use a username with is already established
> within database b....
> Any suggestions would be great as I've been struggling with this for a
> long long time.
> All the best
> Allan|||fantastic... this section worked for me. Thanks very very much.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
Allan
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:<e5fX4#6CFHA.2180
@.TK2MSFTNGP10.phx.gbl>...
> There is no 'execute as' functionality in SQL 2000. A user must have a
> security context in the other database in order to access objects therein.
> If you don't want to add the user to databaseB too, an alternative is to
> enable the guest user in databaseB (EXEC sp_adduser 'guest'). All logins
> that have not been granted access to databaseB explicitly can then access
> the database using the guest user and are limited to those permissions
> granted to the guest.user or public role.
> However, you probably don't want to grant object permissions to public or
> guest. In this case, you can enable 'db chaining' in both databases. Thi
s
> will honor cross-database ownership chaining so that permissions are not
> needed on objects in databaseB referenced by your proc as long as all
> objects are owned by the same user. If your objects are owned by 'dbo',
> both databases also need to be owned by the same login so that the 'dbo'
> user ownership chain is unbroken..
> Note that you should enable cross-database chaining only if you fully
> understand the security implications. You need to fully trust users that
> have permissions to create dbo-owned objects in those databases. Never
> enable cross-database chaining in an sa-owned database unless only symi
n
> role members can create dbo-owned objects.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Allan Martin" <allanmartin@.ntlworld.com> wrote in message
> news:a6d765d6.0502050820.70385c2e@.posting.google.com...|||I'm glad it help you out.
Dan Guzman
SQL Server MVP
"Allan Martin" <allan.martin@.gmail.com> wrote in message
news:7ef7970c.0502090104.7d4c2781@.posting.google.com...
> fantastic... this section worked for me. Thanks very very much.
> Allan
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:<e5fX4#6CFHA.2180@.TK2MSFTNGP10.phx.gbl>...

Execute StoreProcedue in SQL2000 every time i received a replay me

Hi,
how can i execute a StoreProcedue in SQL2000 every time i received a replay
message with a specific subject, and pass some arguments like the subject.
thank in advanced,
Renato Ribeiro
Hi,
1. Create a Task to execute stored procedure
2. Create a message (SP_ADDMESSAGE) to create the subject
3. Using the message ID and Task name create a alert using SP_ADDALERT
4. Trigger the message using RAISERROR statement.
This will automatically fire the Task to execute the stored procedure
Thanks
Hari
SQL Server MVP
"Renato Ribeiro" <RenatoRibeiro@.discussions.microsoft.com> wrote in message
news:FA8C6A92-CB30-46B1-A9B8-187F43927B4D@.microsoft.com...
> Hi,
> how can i execute a StoreProcedue in SQL2000 every time i received a
> replay
> message with a specific subject, and pass some arguments like the subject.
>
> thank in advanced,
> Renato Ribeiro
>

Execute StoreProcedue in SQL2000 every time i received a replay me

Hi,
how can i execute a StoreProcedue in SQL2000 every time i received a replay
message with a specific subject, and pass some arguments like the subject.
thank in advanced,
Renato RibeiroRenato
How do you receive a message?
Have a look into JOBS , for more details please refer to BOL.
"Renato Ribeiro" <RenatoRibeiro@.discussions.microsoft.com> wrote in message
news:33C23D3F-60D2-4591-874F-FA9FA730DED6@.microsoft.com...
> Hi,
> how can i execute a StoreProcedue in SQL2000 every time i received a
> replay
> message with a specific subject, and pass some arguments like the subject.
>
> thank in advanced,
> Renato Ribeiro
>

Sunday, February 26, 2012

execute permission for stored procedures

Hello SQL Experts,

we've got a Windows Server 2003 environment with SQL Server 2000 Sp 3.

A stored procedure selects specific data from a user-table which depend on the user executing it. The users are granted execute permission on the stored procedure. But execution fails, if the user is not granted select permission on the user-table, too.

The problem is, that the user must not have the permission on all data in the user-table but on the data concerning him.

In earlier versions of SQL Server and Windows the execute permission has granted sufficient rights to select from the underlying tables. How can this be re-established?

The Owner of sp and table is dbo.

Thanks for your replies!You can't enable the user to only have permission to some of the data in the table. What you would need to do is have a view, and a column by which you would differentiate between different users. There is little cost associated with a view, and you can make the view owned by the user, so no one else can use it, or no one else can use it by mistake.

IE. [user].[viewname]

Cheers,
-Kilka|||While you can restrict access to columns using permissions, I don't know of any way to restrict access to rows using permissions.

I think that Kilka101 has the right idea about using a view. You may be able to construct a single view that uses User_Id() or Suser_Sname(), or you may need to resort to separate views for each user.

Keep in mind that as you scale upward, this gets a lot more complicated to manage, especially if you introduce any "third tier" processing like an application server. For two-tier applications this isn't likely to be a problem, but as you grow it can become a real problem.

-PatP|||Thanks for your replies!

I thought about a workaround with views, too. But I'm rather sure to remember that before Windows 2003 and SQL Sp3 it has been sufficient to grant a user the right to execute a procedure which performs a select on a table without having the explicit right to select from the table.

For example:

create proc sp_showusers
as
select * from users
where name= 'abc'
go

grant execute on sp_showusers to [my_user]
go

Though 'my_user' doesn't have granted the select permission on table users, he's got all data from table users by executing the sp.

I suppose it has something to do with the stricter security settings coming up with Windows 2003. Anyway, there must be a way out of it... :confused:|||Yes, when you create a stored procedure, the statements in it are checked against the security permissions of the creator. Permission to execute the procedure can be given to users with different permissions, that can then execute the procedure even though it does things such as your example select that the user couldn't do directly. Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

-PatP|||Note that dynamic SQL is an exception to this observation, the executing user must have permission to execute any dynamic SQL.

Oh... okay, I guess this is exactly the point! The stored procedure I got the problems with contains some dynamic SQL... :( So I have to think about views then. ;)

Thank you very much for your help! :)

Friday, February 17, 2012

Execute dynamic generate SQL with length > 8000

I am now writing a stored procedure that will dynamically generate a
trigger base on a specific table structure. The generated trigger
script will have variable lenght, depends on how many columns are
defined inside the specific table.
I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
but I comes with a problem that, if the script generated is longer than
8000, VARCHAR is simply cannot handle it. While the length of the
script is undeterministic, I cannot split the trigger script into a
constant number of VARCHAR variable. May I know if there is any
workaround on it ?
Thx~>I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
And this will really exceed 8000 characters? I think you will find that
this will be impossible to manage. Before you go down this route, I
strongly recommend reading http://www.sommarskog.se/dynamic_sql.html, and
consider generating new triggers in your application as opposed to within a
stored procedure.
However note that you can execute longer strings quite simply.
DECLARE @.sql_1 VARCHAR(8000), @.sql_2 VARCHAR(8000)
SET @.sql_1 = '....'
SET @.sql_2 = '....'
EXEC(@.sql1 + @.sql2)|||John Shum (eurostar@.gmail.com) writes:
> I am now writing a stored procedure that will dynamically generate a
> trigger base on a specific table structure. The generated trigger
> script will have variable lenght, depends on how many columns are
> defined inside the specific table.
> I plan to generate the CREATE TIRGGER script on-the-fly and execute it,
> but I comes with a problem that, if the script generated is longer than
> 8000, VARCHAR is simply cannot handle it. While the length of the
> script is undeterministic, I cannot split the trigger script into a
> constant number of VARCHAR variable. May I know if there is any
> workaround on it ?
I don't know what the purpose is, but to me this sounds like something
I would prefer to do in Perl or Visual Basic. Least of all I would
like to do it in T-SQL.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp