granting public permissions to another role

We need to revoke all insert/update/delete access from public. So this won't
affect users I wanted to create a new role and grant it all of these excess
permissions from public, then revoke the permissions from public. It looks
like there are thousands of grants that need to be revoked - can anyone thin
k
of a way to script this?
Thank you in advance.One method is to generate the script using Transact-SQL. You can tweak the
example below to generate the desired script. This example script ignores
system objects and doesn't handle column permissions.
SET NOCOUNT ON
SELECT
CASE [p].[protecttype]
WHEN 204 THEN 'GRANT '
WHEN 205 THEN 'GRANT '
WHEN 206 THEN 'DENY '
END +
CASE [p].[action]
WHEN 193 THEN 'SELECT'
WHEN 195 THEN 'INSERT'
WHEN 196 THEN 'DELETE'
WHEN 197 THEN 'UPDATE'
WHEN 224 THEN 'EXECUTE'
WHEN 26 THEN 'REFERENCES'
END + ' ON ' +
QUOTENAME(USER_NAME([o].[uid])) + '.' +
QUOTENAME([o].[name]) + ' TO ' +
QUOTENAME([u].[name]) +
CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
FROM
[sysobjects] AS [o]
JOIN
[sysprotects] AS [p] ON
[p].[id] = [o].[id]
JOIN
[sysusers] AS [u] ON
[p].[uid] = [u].[uid]
WHERE
OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
[u].[name] = 'public'
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
> We need to revoke all insert/update/delete access from public. So this
> won't
> affect users I wanted to create a new role and grant it all of these
> excess
> permissions from public, then revoke the permissions from public. It looks
> like there are thousands of grants that need to be revoked - can anyone
> think
> of a way to script this?
> Thank you in advance.|||Thanks for the help - and when it comes to revoking the permissions from
"public" I could use a similar script using "revoke" instead of "grant"?
"Dan Guzman" wrote:

> One method is to generate the script using Transact-SQL. You can tweak th
e
> example below to generate the desired script. This example script ignores
> system objects and doesn't handle column permissions.
> SET NOCOUNT ON
> SELECT
> CASE [p].[protecttype]
> WHEN 204 THEN 'GRANT '
> WHEN 205 THEN 'GRANT '
> WHEN 206 THEN 'DENY '
> END +
> CASE [p].[action]
> WHEN 193 THEN 'SELECT'
> WHEN 195 THEN 'INSERT'
> WHEN 196 THEN 'DELETE'
> WHEN 197 THEN 'UPDATE'
> WHEN 224 THEN 'EXECUTE'
> WHEN 26 THEN 'REFERENCES'
> END + ' ON ' +
> QUOTENAME(USER_NAME([o].[uid])) + '.' +
> QUOTENAME([o].[name]) + ' TO ' +
> QUOTENAME([u].[name]) +
> CASE WHEN [p].[protecttype] = 204 THEN ' WITH GRANT OPTION' ELSE '' END
> FROM
> [sysobjects] AS [o]
> JOIN
> [sysprotects] AS [p] ON
> [p].[id] = [o].[id]
> JOIN
> [sysusers] AS [u] ON
> [p].[uid] = [u].[uid]
> WHERE
> OBJECTPROPERTY([o].[id], 'IsMSShipped') = 0 AND
> [u].[name] = 'public'
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
> news:745289F7-F2CF-4079-8A35-66974CFA73F3@.microsoft.com...
>
>|||Yes, Bobsie, the script I posted was developed to script existing
permissions. You'll need to modify it so that modified permission scripts
are generated instead.
Run the script once to extract the public permissions with your new role
hard-coded as the grantee instead of public'. The generated script will
looks something like:
GRANT SELECT ON MyTable TO MyNewRole
GRANT EXECUTE ON MyProc TO MyNewRole
Then run the script again with a hard-coded REVOKE instead of the GRANT/DENY
CASE statement so the second script generated will be like:
REVOKE SELECT ON MyTable TO public
REVOKE EXECUTE ON MyProc TO public
Be sure to review the generated scripts before running in your environment.
Hope this helps.
Dan Guzman
SQL Server MVP
"Bobsie" <Bobsie@.discussions.microsoft.com> wrote in message
news:3949EB00-7D35-4EC1-9920-249ABF69BBEC@.microsoft.com...
> Thanks for the help - and when it comes to revoking the permissions from
> "public" I could use a similar script using "revoke" instead of "grant"?
>
> "Dan Guzman" wrote:
>sql

GRANT permission to lots of tables and sp to db user

Is it possible to grant permissions (select, insert, delete, update, exec)
to a db user to all tables and all stored procedures in a specific db in an
easy (lazy!) way?
I mean, except for clicking in all permission checkboxes in Enterprise
Manager or writing a huge sql script like
grant select, insert, delete, update
on mytable1
to myuser
grant select, insert, delete, update
on mytable2
to myuser
...
grant exec
on mySP1
to myuser
grant exec
on mySP2
to myuser
...
?
Is there another way, like
GRANT select, insert, delete, update
on AllMyTables
to myuser
GRANT exec
on AllmySP
to myuser
?You can use a script like to example below to grant mass permissions
according to your requirements.
SET NOCOUNT ON
DECLARE @.GrantStatement nvarchar(500)
DECLARE @.LastError int
DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
SELECT
N'GRANT ' +
CASE
WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
N'SELECT, INSERT, UPDATE, DELETE'
WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
N'SELECT'
WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
N'EXECUTE'
END +
N' ON ' +
QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].[nam
e]) +
N' TO MyRole'
FROM
sysobjects ob
WHERE
OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
(OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
OPEN GrantStatements
WHILE 1 = 1
BEGIN
FETCH NEXT FROM GrantStatements INTO @.GrantStatement
IF @.@.FETCH_STATUS = -1 BREAK
RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
EXECUTE sp_ExecuteSQL @.GrantStatement
END
CLOSE GrantStatements
DEALLOCATE GrantStatements
Hope this helps.
Dan Guzman
SQL Server MVP
"Siri" <Siri@.discussions.microsoft.com> wrote in message
news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
> Is it possible to grant permissions (select, insert, delete, update, exec)
> to a db user to all tables and all stored procedures in a specific db in
> an
> easy (lazy!) way?
> I mean, except for clicking in all permission checkboxes in Enterprise
> Manager or writing a huge sql script like
> grant select, insert, delete, update
> on mytable1
> to myuser
> grant select, insert, delete, update
> on mytable2
> to myuser
> ...
> grant exec
> on mySP1
> to myuser
> grant exec
> on mySP2
> to myuser
> ...
> ?
> Is there another way, like
> GRANT select, insert, delete, update
> on AllMyTables
> to myuser
> GRANT exec
> on AllmySP
> to myuser
> ?
>|||Thank you very much! This really helped!
Siri
"Dan Guzman" wrote:

> You can use a script like to example below to grant mass permissions
> according to your requirements.
> SET NOCOUNT ON
> DECLARE @.GrantStatement nvarchar(500)
> DECLARE @.LastError int
> DECLARE GrantStatements CURSOR LOCAL FAST_FORWARD FOR
> SELECT
> N'GRANT ' +
> CASE
> WHEN OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 THEN
> N'SELECT, INSERT, UPDATE, DELETE'
> WHEN OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 THEN
> N'SELECT'
> WHEN OBJECTPROPERTY([ob].[id], 'IsScalarFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 THEN
> N'EXECUTE'
> END +
> N' ON ' +
> QUOTENAME(USER_NAME([ob].[uid])) + '.' + QUOTENAME([ob].&#
91;name]) +
> N' TO MyRole'
> FROM
> sysobjects ob
> WHERE
> OBJECTPROPERTY([ob].[id], 'IsMSShipped') = 0 AND
> (OBJECTPROPERTY([ob].[id], 'IsProcedure') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsUserTable') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsView') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsInlineFunction') = 1 OR
> OBJECTPROPERTY([ob].[id], 'IsTableFunction') = 1)
> OPEN GrantStatements
> WHILE 1 = 1
> BEGIN
> FETCH NEXT FROM GrantStatements INTO @.GrantStatement
> IF @.@.FETCH_STATUS = -1 BREAK
> RAISERROR (@.GrantStatement, 0, 1) WITH NOWAIT
> EXECUTE sp_ExecuteSQL @.GrantStatement
> END
> CLOSE GrantStatements
> DEALLOCATE GrantStatements
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Siri" <Siri@.discussions.microsoft.com> wrote in message
> news:51FFF23E-3543-4A4C-B6FE-8FCE4026CCA3@.microsoft.com...
>
>

Grant on all tables

Hi,

is there a way to grants object privileges on
all tables of database to an user?

like this:

grant select, insert, update, delete on <all tables> to usernamedb
go

thanks!!!!

No. You can add user to db_datareader and db_datawriter roles. This will however provide SELECT, INSERT, UPDATE and DELETE permission on tables/ views / table-valued functions etc. See Books Online for more details on the permissions / roles. Also, you should create a group/role and grant permissions to it instead of directly to the user. This is easier to manage and control.

Alternatively, you can write few lines of code that loops through the desired objects and grants necessary permissions on each object using dynamic SQL.

|||

In 2005 you can grant access to a schema's set of objects:

grant select, insert, update, delete on schema::dbo to bob

For example, in the AdventureWorks DB:

use adventureWorks
go
create user bob without login
go
--first prove no accss
execute as user='bob'
go
select * from production.product --will error
go
revert
go
grant select, insert,update, delete on schema::production to bob
go
execute as user='bob'
go
select * from production.product --will work
go
revert
go

Note that this gives access to table valued user-defined functions also...

|||Very cool... thank u

Grant insert, but only allow entering vals in some fields

I have a table with 3 fields:
val1
val2
val3
Is there a way using a role to allow users to create a new record in
the table, but only allowing them to populate the val1 field during the
insert? They should not be allowed to put data in fields val2 and val3
during the insert and they should only be allowed to modify the val1
field.
Thanks!
Chris(cbtechlists@.gmail.com) writes:
> I have a table with 3 fields:
> val1
> val2
> val3
> Is there a way using a role to allow users to create a new record in
> the table, but only allowing them to populate the val1 field during the
> insert? They should not be allowed to put data in fields val2 and val3
> during the insert and they should only be allowed to modify the val1
> field.
You could a create view that exposes the permitted column and let the
users insert into that view rather than directly to the table. Or you
could expose all columns in the table, and have an INSTEAD OF trigger
ignores the non-permitted columns.
... or you could use stored procedures.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Grant Insert Permission Freezing

I have one table that I cannot grant permissions on.
I have tried through Enterprise Manager and by executing at statement
like:
GRANT SELECT on dbo.YYY to ThisRoleName
I am db owner. I kicked everyone out of the database so I am the only
person in there. I can grant permissions to any other table in the
database. But when I try to grant permissions to table YYY the
request eventually times out.
Any ideas would be most appreciated!Run SQL Server Profiler to see what is going on while you grant permission
<juustagirl@.yahoo.com> wrote in message
news:1186787274.633524.104620@.q3g2000prf.googlegroups.com...
>I have one table that I cannot grant permissions on.
> I have tried through Enterprise Manager and by executing at statement
> like:
> GRANT SELECT on dbo.YYY to ThisRoleName
> I am db owner. I kicked everyone out of the database so I am the only
> person in there. I can grant permissions to any other table in the
> database. But when I try to grant permissions to table YYY the
> request eventually times out.
> Any ideas would be most appreciated!
>|||Execute sp_who to list current activity. Perhaps there is a rogue process
blocking your request.
Hope this helps.
Dan Guzman
SQL Server MVP
<juustagirl@.yahoo.com> wrote in message
news:1186787274.633524.104620@.q3g2000prf.googlegroups.com...
>I have one table that I cannot grant permissions on.
> I have tried through Enterprise Manager and by executing at statement
> like:
> GRANT SELECT on dbo.YYY to ThisRoleName
> I am db owner. I kicked everyone out of the database so I am the only
> person in there. I can grant permissions to any other table in the
> database. But when I try to grant permissions to table YYY the
> request eventually times out.
> Any ideas would be most appreciated!
>

Got err: There is already an object named '#TEMP' in the datab

Thank you for the reply. I did figured it out last Friday after I keep
messing with it. I have a if -else code where the if xxx then insert yyy to
#temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to be
used in this fashion which is usually allowed in development coding. I
changed the else to insert to #temp2 and it worked.
Thanks, Alpha
"Aaron Bertrand [SQL Server MVP]" wrote:

> Maybe you could show your code...
>
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:0F8B1D0C-6CD7-42D5-BE0E-4D473E39C188@.microsoft.com...
>
>> #temp else insert zzz to #temp. I didn't know SQL doesn't allow #temp to
> be
> used in this fashion which is usually allowed in development coding.
Well, it doesn't actually EXECUTE the code, so it doesn't understand that
your IF/ELSE can only result in one path. It merely sees you trying to
create the same #temp table twice...|||I see. Thank you for your help.
"Aaron Bertrand [SQL Server MVP]" wrote:

> Well, it doesn't actually EXECUTE the code, so it doesn't understand that
> your IF/ELSE can only result in one path. It merely sees you trying to
> create the same #temp table twice...
>
>

Good sp patterns

I am looking for a good document or whatever to learn how to create
storedprocedure(select,Insert,...) effectively?
I am going to create some storeprocedure and I want them to be nice in
performance and scalability.
thanks
If you are asking about a nicely-formatted template or some general dos and
donts, I am sure there are folks out here who have something to offer. But
to have a guide line for writing good performing and easy scaling procs, it
can cost a book and a few years of experience ...
Besides, it is always dependent on YOUR system. In addition, the
performance and scalability of stored procedures are not just in themselves.
It starts with logical DB design, physical implementation, etc.
Some books may be helpful. SQL Server 2000 Performance Tuning by Ed Whalen
et al, Professional SQL Server 2000 Programming by R. Vieira can be among
them.
"ALI-R" <ali@.microsoft.com> wrote in message
news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> I am looking for a good document or whatever to learn how to create
> storedprocedure(select,Insert,...) effectively?
> I am going to create some storeprocedure and I want them to be nice in
> performance and scalability.
> thanks
>
|||Is there somehwhere like www.codeprojects.com ,where I can find some
storedprocedure samples to get some idea how to write my stored procedures.
thanks for your help.
Ali
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:ujmD$lSwEHA.2316@.TK2MSFTNGP15.phx.gbl...
> If you are asking about a nicely-formatted template or some general dos
and
> donts, I am sure there are folks out here who have something to offer.
But
> to have a guide line for writing good performing and easy scaling procs,
it
> can cost a book and a few years of experience ...
> Besides, it is always dependent on YOUR system. In addition, the
> performance and scalability of stored procedures are not just in
themselves.
> It starts with logical DB design, physical implementation, etc.
> Some books may be helpful. SQL Server 2000 Performance Tuning by Ed
Whalen
> et al, Professional SQL Server 2000 Programming by R. Vieira can be among
> them.
>
> "ALI-R" <ali@.microsoft.com> wrote in message
> news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
>

Good sp patterns

I am looking for a good document or whatever to learn how to create
storedprocedure(select,Insert,...) effectively?
I am going to create some storeprocedure and I want them to be nice in
performance and scalability.
thanksIf you are asking about a nicely-formatted template or some general dos and
donts, I am sure there are folks out here who have something to offer. But
to have a guide line for writing good performing and easy scaling procs, it
can cost a book and a few years of experience ...
Besides, it is always dependent on YOUR system. In addition, the
performance and scalability of stored procedures are not just in themselves.
It starts with logical DB design, physical implementation, etc.
Some books may be helpful. SQL Server 2000 Performance Tuning by Ed Whalen
et al, Professional SQL Server 2000 Programming by R. Vieira can be among
them.
"ALI-R" <ali@.microsoft.com> wrote in message
news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> I am looking for a good document or whatever to learn how to create
> storedprocedure(select,Insert,...) effectively?
> I am going to create some storeprocedure and I want them to be nice in
> performance and scalability.
> thanks
>|||Is there somehwhere like www.codeprojects.com ,where I can find some
storedprocedure samples to get some idea how to write my stored procedures.
thanks for your help.
Ali
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:ujmD$lSwEHA.2316@.TK2MSFTNGP15.phx.gbl...
> If you are asking about a nicely-formatted template or some general dos
and
> donts, I am sure there are folks out here who have something to offer.
But
> to have a guide line for writing good performing and easy scaling procs,
it
> can cost a book and a few years of experience ...
> Besides, it is always dependent on YOUR system. In addition, the
> performance and scalability of stored procedures are not just in
themselves.
> It starts with logical DB design, physical implementation, etc.
> Some books may be helpful. SQL Server 2000 Performance Tuning by Ed
Whalen
> et al, Professional SQL Server 2000 Programming by R. Vieira can be among
> them.
>
> "ALI-R" <ali@.microsoft.com> wrote in message
> news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> > I am looking for a good document or whatever to learn how to create
> > storedprocedure(select,Insert,...) effectively?
> >
> > I am going to create some storeprocedure and I want them to be nice in
> > performance and scalability.
> >
> > thanks
> >
> >
>

Good sp patterns

I am looking for a good document or whatever to learn how to create
storedprocedure(select,Insert,...) effectively?
I am going to create some storeprocedure and I want them to be nice in
performance and scalability.
thanksIf you are asking about a nicely-formatted template or some general dos and
donts, I am sure there are folks out here who have something to offer. But
to have a guide line for writing good performing and easy scaling procs, it
can cost a book and a few years of experience ...
Besides, it is always dependent on YOUR system. In addition, the
performance and scalability of stored procedures are not just in themselves.
It starts with logical DB design, physical implementation, etc.
Some books may be helpful. SQL Server 2000 Performance Tuning by Ed Whalen
et al, Professional SQL Server 2000 programming by R. Vieira can be among
them.
"ALI-R" <ali@.microsoft.com> wrote in message
news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
> I am looking for a good document or whatever to learn how to create
> storedprocedure(select,Insert,...) effectively?
> I am going to create some storeprocedure and I want them to be nice in
> performance and scalability.
> thanks
>|||Is there somehwhere like www.codeprojects.com ,where I can find some
storedprocedure samples to get some idea how to write my stored procedures.
thanks for your help.
Ali
"Quentin Ran" <removethis.qran2@.yahoo.com> wrote in message
news:ujmD$lSwEHA.2316@.TK2MSFTNGP15.phx.gbl...
> If you are asking about a nicely-formatted template or some general dos
and
> donts, I am sure there are folks out here who have something to offer.
But
> to have a guide line for writing good performing and easy scaling procs,
it
> can cost a book and a few years of experience ...
> Besides, it is always dependent on YOUR system. In addition, the
> performance and scalability of stored procedures are not just in
themselves.
> It starts with logical DB design, physical implementation, etc.
> Some books may be helpful. SQL Server 2000 Performance Tuning by Ed
Whalen
> et al, Professional SQL Server 2000 programming by R. Vieira can be among
> them.
>
> "ALI-R" <ali@.microsoft.com> wrote in message
> news:%2328NHORwEHA.3416@.TK2MSFTNGP09.phx.gbl...
>

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global variable value lost during insertion in a table

Hi,

This problem is connected with the query i posted yesterday regarding insertion of global variables. I was able to insert the variable in a table to check its value.

This value is mapped to the global variable in a previous Execute SQL Task. But when I use the same global variable to insert in a table, default value 0 is inserted.

My query is does the global variable declared at the package level does not store the value mapped across multiple tasks in control flow?

How can i insert the value stored in a variable in a table from previous SQL Task.

Can anyone suggest some solution,links to try a workaround?

Thanks in advance.

Regards,

Aman

Hello,

Im not sure i understand your question, but even so, i think that you want a variable to exist during all the execution of your DTS, so If you variable has a dts scope not a function or a package scope, should fix your problem.

Or you are telling that you have a dts that calls several packages and in one package you do your sql insert task and then you will call another package, inside the same dts?

Regards,

|||

Hi,

I had declared my variables with global scope(at package level). In my 1st 2 Execute SQL Tasks i was saving those variables as input(which was wrong!). When I changed the variable property as Output things worked fine. In my 3rd SQL Task I wanted to insert those variables.

Moreover I was working on a single package. Hopefully you got my requirement now?

anyway thanks for your reply.

Regards,

Aman

|||

Hello Aman,

I assume that you still have the problem altough you marked the thread as awnsered.

In the variables tab activate the show user variables button, check the information for that.

Afterwards check if the sql task has, in the properties, the result set to single row. If you are expecting to have more than 1 line, then you would need a foreach loop to read every value of the variable.

If all that im saying isnt helping you, use the breakpoints and check the locals tab for the value of the variables.

Because, the variables, shouldn't loose the value, something is wrong.

Hope you can get your problem sorted out.

Good luck

Global temp tables in SQL Server

Hi!
I have a stored proc that creates a global temp table. How can I have multiple users select records from and insert records in that table without overwriting and/or deleting data?
Thanks so much for your help!
-ParulA global temp table is like any other table.
How would you prevent users from overwriting data in a permanent table?
A better question is why are you using a global temporary table, and would local temporary tables suffice?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)

Is it possible to somehow lock these tables so the records inserted by one are not affected by another. Is there a way to use some kind of a userID?|||I am using a global temp table because I have the following:

Stored Proc A calls Stored Proc B calls Stored Proc C
(both Stored Proc B and C need global temp tables)This does note require the use of "GLOBAL" temporary tables (prefixed with "##"). It only requires "LOCAL" temporary tables (prefixed with "#").|||Won't the variables lose scope if local temp tables are used?|||Not if one procedure calls the next. Then they are nested, and any temporary table declared in the outermost procedure exists until that procedure completes, making it available to the nested procedures.|||The downside to any temp table being used by nested procs is that each and every nested proc will recompile each time it is called. This could cause severe overhead in the execution time.|||ehhhhh...I've yet to see compile time become a factor outside of processes that are run hundreds of times per hour. And you'd have the same problem with global temporary tables.|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls|||That should work, thanks!|||You should make a permanent table though...what for example, do you think would happen if you had a global table in existance and then tried to create it again?

Maybe you can explain to use what you are trying to do instead of trying to sledge hammer a solution with technology

Simple is usually the best answer|||while i am using a global table, if the stored proc is running in multiple sessions, it errors out saying "##temp already exists" or "it cannot be dropped."|||In any case....

Add a column to your table..and this could be employed using a permanent table...that carries the spid of the transaction

Use @.@.SPID to insert the value into the column, then each sproc would use the SPID in the predicate. This in effect would isolate the data from the other sproc calls
We gotta agree to disagree on this one. I doubt that a permanent table is an appropriate solution tohis problem, and adding the SPID to a global temporary table is just a hack for treating it like a local temporary table.|||so what else do you recommend i should try?|||Local.

Temporary.

Tables.|||Who's we?

And you mean respectfully diagree...you wouldn't want to get banned

In any case, I'd rather know what you are really trying to accomplish in the first place.

Also, it would be interesting to know how much data we are talking about.|||You lookin' fer a fight, Joisey-boy?

You are free to "diagree" with me, while'st I shall "disagree" with you (respectfully, as always).

Giving Value for Identity column while inserting

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Quote:

Originally Posted by sajithamol

I have a table with an Identity column. The latest Identity value is 1298. A row with Identity 324 was deleted by mistake and I need to insert it now. But if I insert it now it will have Identity value of 1299. I tried giving the value 324 for the Identity column in the Insert statement but it throws error. How can I insert this row with the same Identity value as before??

Are you not able to insert using a plain insert statement on query analyser?|||You can do this by
SET IDENTITY_INSERT [table_name] ON
and then turn it off

Giving Table name as a Variable

Hi guys,

I want to insert record to the table, the table name is a variable. I was tried like this,

declare @.Table_Name as varchar(255)

declare @.MM as varchar(2)

declare @.YY as varchar(4)

set @.MM = month(getdate())

set @.YY = year(getdate())

set @.Table_Name = 'tb_XXXX_' + @.MM + @.YY

INSERT INTO @.Table_Name (column1,....) VALUES (@.Column1,...)

If I print the @.Table_Name variable it shows the corret Table name. But while executing the Procedure it shows a error message

'Must declare the sclar variable @.Table_Name'

How do I implement this? Please anyone had a experience like this tell me a way. Thanks in advance.

Arun.

You have to use the dynamic SQL here. Here the sample to use the dynamic SQL – using sp_executesql or exec

Code Snippet

Create Table #TestDynSQL

(

Id int,

Name varchar(100),

DOB Datetime

);

Declare @.P_Id int, @.P_Name varchar(100), @.P_dob datetime

Declare @.SQL as Nvarchar(4000)

Declare @.ParamDecl as Nvarchar(4000)

Set @.SQL = N'Insert Into #TestDynSQL Values(@.id, @.name, @.dob)'

Set @.ParamDecl = N'@.id int, @.name varchar(100), @.DOB datetime'

Set @.P_id=1

Set @.P_Name='Mani'

Set @.P_Dob='1979-07-26'

--Highly recommanded to use the sp_executesql

Exec sp_executesql @.SQL, @.ParamDecl, @.P_id, @.P_name, @.P_dob

--or

--Beware of SQL injection while using the EXEC(STATEMENT)

Set @.SQL = 'Insert Into #TestDynSQL values(' + cast(@.P_Id as varchar) + ',''' + @.P_Name + ''',''' + Cast(@.P_Dob as varchar) + ''')'

Exec (@.SQL)

Select * from #TestDynSQL

|||

Mani gave you a good solution using dynamic SQL.

However, there are some issues about using dynamic SQL that you should be aware. There may be significant performance penalties and there may also be security issues. The following articles are good resources to learn and explore the use (and mis-use) of dynamic SQL.

Dynamic SQL - The Curse and Blessings of Dynamic SQL
http://www.sommarskog.se/dynamic_sql.html
http://msdn2.microsoft.com/en-us/library/ms188332.aspx
http://msdn2.microsoft.com/en-us/library/ms175170.aspx

Giving access to outside web site to SQL Server stored procedure

Hello, I have a sql server 2000 production database that will need to have
multiple web sites insert contact information into a table. I plan to
create a new sql user and only give it permission to execute one stored
procedure (that will do the insert row into the contact table).
Then I will provide to any outside vendor the SQL Server name, database,
user id, password and stored procedure(and input parameters) name. What are
the security concerns with this?
Would it make any sense to create a database with only this table in it, or
is this just overkill. I see that when I assign a new user to just one
database and one stored procedure that they can see the master database. Is
this an issue? Should I give the user
db_denydatareader on the master database?
Thanks
DonDon (dons100@.ameritech.net) writes:
> Hello, I have a sql server 2000 production database that will need to have
> multiple web sites insert contact information into a table. I plan to
> create a new sql user and only give it permission to execute one stored
> procedure (that will do the insert row into the contact table).
> Then I will provide to any outside vendor the SQL Server name, database,
> user id, password and stored procedure(and input parameters) name. What
> are the security concerns with this?
> Would it make any sense to create a database with only this table in it,
> or is this just overkill. I see that when I assign a new user to just
> one database and one stored procedure that they can see the master
> database. Is this an issue? Should I give the user db_denydatareader
> on the master database?
I would rather not expose an SQL Server on the Internet at all, least of
all SQL 2000. SQL 2000 no protection against brute-force attacks whatsoever.
Even if you have a really strong password for sa, someone might be able to
crack it. The server would also be open for denial-of-service attacks.
I think you should better implement some sort of web service. Not
necessarily with XML. But something that listens on a TCP/IP port, possibly
authenticates the user, and then connects to SQL Server with this low-
priv user you described. Username and password would be hard-coded into
this service, or read from a file.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx