Problem with sp_grantdbaccess

When the I execute the following back to back in the SQL Query Analyzer,
I get an error:
Line 2: Incorrect syntax near 'sp_grantdbaccess'.
sp_revokedbaccess auser;
sp_grantdbaccess auser;
However, when I execute them individually, they work fine. Where's the
syntax error?
Or is there a better way remove a user as db_owner but still grant them
access to the database?The two commands need to be executed in separate batches. So the syntax
should be:
sp_revokedbaccess auser
go
sp_grantdbaccess auser
However, it appears that you just want to remove a user from the 'db_owner'
role. So following command should do your work:
EXEC sp_droprolemember 'db_owner', 'YourUserName'
"O.B." wrote:

> When the I execute the following back to back in the SQL Query Analyzer,
> I get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?|||If a call to a stored procedure is not the first thing in a batch, you must
use the word EXECUTE (or EXEC).
So you can either separate the two calls into separate batches:
sp_revokedbaccess auser;
go
sp_grantdbaccess auser;
go
OR
you can use EXEC:
sp_revokedbaccess auser;
EXEC sp_grantdbaccess auser;
You might want to consider always using EXEC to call a procedure, then you
never need to worry about whether it's the first thing in the batch or not.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"O.B." <funkjunk@.bellsouth.net> wrote in message
news:11om94vm6grltd3@.corp.supernews.com...
> When the I execute the following back to back in the SQL Query Analyzer, I
> get an error:
> Line 2: Incorrect syntax near 'sp_grantdbaccess'.
> sp_revokedbaccess auser;
> sp_grantdbaccess auser;
> However, when I execute them individually, they work fine. Where's the
> syntax error?
> Or is there a better way remove a user as db_owner but still grant them
> access to the database?
>