I'm so confused
From: John Cobb (jcobb@acxiom.com)
Date: 10/24/02
- Next message: Kimberly L. Tripp: "Re: EXEC master..xp_cmdshell Prevention"
- Previous message: BP Margolin: "Re: exec sp_grantdbaccess in a sproc"
- In reply to: BP Margolin: "Re: exec sp_grantdbaccess in a sproc"
- Next in thread: Bill Hollinshead [MS]: "RE: I'm so confused"
- Reply: Bill Hollinshead [MS]: "RE: I'm so confused"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "John Cobb" <jcobb@acxiom.com> Date: Wed, 23 Oct 2002 22:02:37 -0500
The error occured when I was testing the sproc both in debug mode and
normally from Query Analyzer rather than calling it from any other code so I
don't see how it could be wrapped in a transaction. Of course, I'm an uber
newbie so me not understanding things is a normal. :-)
"BP Margolin" <bpmargo@attglobal.net> wrote in message
news:Opfc4BweCHA.688@tkmsftngp11...
> John,
>
> I don't believe that the error is necessarily in your posted code. The
> system stored procedure sp_grantdbaccess checks that there is not an
> enclosing transaction. BTW, the actual code for the sp_grantdbaccess
stored
> procedure is located in the master database, and you can take a look at
the
> code yourself. If you do so, you'll see that one of the first checks is
that
> there is not an enclosing transaction.
>
> Presumably the code calling your stored procedure is wrapped in a
> transaction. That's causing sp_grantdbaccess to "barf". Can you remove the
> enclosing transaction? If not, you might consider ... but only after
> considerable evaluation of the risks involved ... creating a copy of the
> sp_grantdbaccess stored procedure under another name, removing that
> particular check from your copy, and invoking your copy rather than the
> system stored procedure. Frankly, I don't recommend this option, but
> presumably you'll in a better position than I am to evaluate the risks.
>
> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.
>
> "John Cobb" <jcobb@acxiom.com> wrote in message
> news:#fMuK3veCHA.688@tkmsftngp11...
> > Jumped the gun. Still can't get it to work. Here's my sproc and the
error
> > message it returns. Can anyone see what I'm missing?
> >
> > CREATE PROCEDURE dbo.ccm_GrantDevTeamRights
> > --input variable for user name
> > @UserName nvarchar(8)
> >
> > AS
> >
> > --if user doesn't exist as server login then raise error and get out
> > if not exists(select * from master.dbo.syslogins where loginname =
> > @username)
> > raiserror ('something',1,1) --TODO CREATE CUSTOM ERROR
> >
> > --LOCAL VARIABLES
> > declare @strSQL nvarchar(300) -- hold t-sql string
> > declare @strDbName nvarchar(50) --holds db name
> > declare @tintCurrentDB tinyint --loop controller, how
many
> > db's have we done already
> >
> > set @tintcurrentdb = 1
> >
> > WHILE @tintcurrentdb <= 1 --loop while @tintcurrentdb is <= total number
> of
> > db's
> > begin--begin while loop
> > set @strDbName = case
> > when @tintcurrentdb = 1 then 'AcpConceptOne'
> > when @tintcurrentdb = 2 then 'AcpConceptTwo'
> > when @tintcurrentdb = 3 then 'AcpMetadata'
> > when @tintcurrentdb = 4 then 'AmConceptOne'
> > when @tintcurrentdb = 5 then 'AConceptTwo'
> > when @tintcurrentdb = 6 then 'AmMetadata'
> > end
> >
> > --see if user already has dbaccess in database @strdbname
> > select @strsql = 'use ' + @strdbname + ' select * from sysusers where
> name
> > = ''' + @UserName +''''
> > exec (@strsql)
> >
> > if @@rowcount < 1 --if exec above didn't return a row then user doesn't
> > exist so create them
> > select @strSQL = 'use ' + @strdbname + ' exec sp_grantdbaccess ''' +
> > @username + ''''
> > else
> > select @strsql = null
> >
> > exec (@strSQL)
> > Immediate line above is where it blows up with the following error:
> >
> > Server: Msg 15002, Level 16, State 1, Procedure sp_grantdbaccess, Line
33
> > [Microsoft][ODBC SQL Server Driver][SQL Server]The procedure
> > 'sp_grantdbaccess' cannot be executed within a transaction.
> > Failed to get the call stack!
> >
> >
> >
> > "John Cobb" <jcobb@acxiom.com> wrote in message
> > news:u4R9dvueCHA.1652@tkmsftngp11...
> > > Got it. Sorry for the bother. In case anyone else was wondering, you
> can
> > > do it in an EXEC statement within your sproc.
> > >
> > > CREATE PROCEDURE dbo.ccm_GrantDevTeamRights
> > > @UserName nvarchar(8)
> > > as
> > > declare @strSQL nvarchar(100)
> > > declare @strDbName nvarchar(100)
> > >
> > > set @strDbName = "whatever"
> > > set @strSQL = 'use ' + @strdbname + 'exec sp_grantdbaccess ' +
> @username
> > > EXEC (@strsql)
> > > go
> > >
> > >
> > > "John Cobb" <jcobb@acxiom.com> wrote in message
> > > news:ul3w4cteCHA.3964@tkmsftngp08...
> > > > I have multiple databases (approx 30) that I want to grantdbaccess
to
> > for
> > > > members of the development team. I was trying to do this in a
master
> > > sproc
> > > > like
> > > >
> > > > create procedure dbo.sp_GrantDevTeamRights
> > > > @UserName nvarchar(8)
> > > > as
> > > > Use DB1
> > > > exec sp_grantdbaccess @username
> > > > use DB2
> > > > exec sp_grantdbaccess @username
> > > > use DB3
> > > > exec sp_grantdbaccess @username
> > > > and so on....
> > > >
> > > > but the use statement isn't allowed in sprocs. I need a method to
> grant
> > > > access to all the databases from a front end app which is why I was
> > trying
> > > > to do it in a sproc. Any ideas?
> > > >
> > > >
> > >
> > >
> >
> >
>
>
- Next message: Kimberly L. Tripp: "Re: EXEC master..xp_cmdshell Prevention"
- Previous message: BP Margolin: "Re: exec sp_grantdbaccess in a sproc"
- In reply to: BP Margolin: "Re: exec sp_grantdbaccess in a sproc"
- Next in thread: Bill Hollinshead [MS]: "RE: I'm so confused"
- Reply: Bill Hollinshead [MS]: "RE: I'm so confused"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
