Can I have Exec(...) statements within a transcation ?

Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(...) statements are there in the transaction ? Each stored procedure is made up of an action query.

begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:

 begin tran
 statement1
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 statement2
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 commit tran

Can I grant a group access to my db

I would like to be able to do something like this:
use mydb
exec sp_grantdbaccess 'localhost\Users'
go
When I do, I get this error msg:
Windows NT user or group 'localhost\Users' not found. Check the name
again.
The error message implies that I should be able to add a group.
A co-worker read on the web somewhere that this should work:
exec sp_grantdbaccess 'localhost\domain Users'
but it also fails the same way.
Any help would be appreciated,
TIA,
DaveDid you add that group as a login to SQL Server?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Dave" <kaatzd@.hotmail.com> wrote in message
news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
>I would like to be able to do something like this:
> use mydb
> exec sp_grantdbaccess 'localhost\Users'
> go
> When I do, I get this error msg:
> Windows NT user or group 'localhost\Users' not found. Check the name
> again.
> The error message implies that I should be able to add a group.
> A co-worker read on the web somewhere that this should work:
> exec sp_grantdbaccess 'localhost\domain Users'
> but it also fails the same way.
> Any help would be appreciated,
> TIA,
> Dave
>|||Users is a predefined group, my understanding was that SQL would
recognize it.
Be that as it may, someone else in my company gave me the solution:
exec sp_grantdbaccess 'NT AUTHORITY\Authenticated Users'
and then I also needed to add above users to a role for my db, like so:
exec sp_addrolemember 'db_datareader', 'NT AUTHORITY\Authenticated
Users'
Thanks,
Dave
Tibor Karaszi wrote:
> Did you add that group as a login to SQL Server?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Dave" <kaatzd@.hotmail.com> wrote in message
> news:1155062014.520009.304330@.b28g2000cwb.googlegroups.com...
> >I would like to be able to do something like this:
> >
> > use mydb
> > exec sp_grantdbaccess 'localhost\Users'
> > go
> >
> > When I do, I get this error msg:
> > Windows NT user or group 'localhost\Users' not found. Check the name
> > again.
> >
> > The error message implies that I should be able to add a group.
> >
> > A co-worker read on the web somewhere that this should work:
> >
> > exec sp_grantdbaccess 'localhost\domain Users'
> >
> > but it also fails the same way.
> >
> > Any help would be appreciated,
> > TIA,
> > Dave
> >

Can a user exec a DTS Package...

...in SQL 2000 without having xp_cmdshell permissions.The best (easiest) way to allow that is to create a job that runs the DTS package(s), then use sp_start_job (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_11uq.asp) to start that job.

-PatP|||Good idea, thanks.