Re: exec sp_grantdbaccess in a sproc
From: BP Margolin (bpmargo@attglobal.net)
Date: 10/24/02
- Next message: John Cobb: "I'm so confused"
- Previous message: Kimberly L. Tripp: "Re: SQLMail security"
- In reply to: John Cobb: "Re: exec sp_grantdbaccess in a sproc"
- Next in thread: John Cobb: "I'm so confused"
- Reply: John Cobb: "I'm so confused"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
From: "BP Margolin" <bpmargo@attglobal.net> Date: Wed, 23 Oct 2002 21:59:26 -0400
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: John Cobb: "I'm so confused"
- Previous message: Kimberly L. Tripp: "Re: SQLMail security"
- In reply to: John Cobb: "Re: exec sp_grantdbaccess in a sproc"
- Next in thread: John Cobb: "I'm so confused"
- Reply: John Cobb: "I'm so confused"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]
Relevant Pages
|
