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?
>
sql

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?
>

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?
>

Problem with Search on Newsgroup

When searching, I only get results back for this month (March 2005)!! Has
anyone else encountered problems with the search tool on the newsgroup'Use google instead.
http://groups-beta.google.com/group/microsoft.public.sqlserver.reportingsvcs?hl=en&lr=&ie=UTF-8&oe=UTF-8
"Aparna" <Aparna@.discussions.microsoft.com> wrote in message
news:3AFAB874-5334-436C-98A0-B8A28C6241E9@.microsoft.com...
> When searching, I only get results back for this month (March 2005)!! Has
> anyone else encountered problems with the search tool on the newsgroup'

problem with restore

hi,
i am trying to resore a database on sql --the back up of
which was taken in sql 2000
and i am getting the following error
[SQL-DMO] must be a part of sa, sysadmin or.......group
to complete this operation
i have given the server nt authentication and i am logged
in to the account with sys admin privalges ...
i am running a service pack 4 alredy
can someone help me on this one
You need to be sa to restore the db and it sounds like the account EM is
registered under for that server is not. Try using QA and the RESTORE
command with a proper login and see what happens.
Andrew J. Kelly SQL MVP
"ggupta" <ggupta@.cs.odu.edu> wrote in message
news:124801c4a331$d22dd300$a501280a@.phx.gbl...
> hi,
> i am trying to resore a database on sql --the back up of
> which was taken in sql 2000
> and i am getting the following error
> [SQL-DMO] must be a part of sa, sysadmin or.......group
> to complete this operation
> i have given the server nt authentication and i am logged
> in to the account with sys admin privalges ...
> i am running a service pack 4 alredy
> can someone help me on this one