Permission issue

Hello,
Some facts:
- I am using SQL Server 2000 on W2K3
- I have a SQL Login called 'Rlogic' with dbo permission rights on every
database. It is also a member of the Systems Administrators Group.
In Query Analyzer, when I run the following:-
use Rlogic_Training
go
select *
from users
I get this error:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'users'.
---
However, when I append 'Rlogic.' before the dbname, it works fine: -
use Rlogic_Training
go
select *
from Rlogic.users
Is there a way for me NOT to append 'Rlogic.' everytime?
Thanks,
Sameer
--
Message posted via http://www.sqlmonster.comHello,
In SQL 2005 you can create a SYNONYM and query the Synonym. But for SQL
2005, you need to either login as object owner or you need to change the
object owner to DBO using
SP_CHANGEOBJECTOWNER system stored procedure. If the object owner is DBO you
do not want to prefix the object owner even if you login as a different
user.
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello Hari,
I tried sp_changeobjectowner but got this error:
Server: Msg 15001, Level 16, State 1, Procedure sp_changeobjectowner, Line 38
Object 'USERS' does not exist or is not a valid object for this operation.
When I see the tables in the 'Object Browser' left pane, I see them starting
with prefix 'Rlogic.' and there are literally hundreds of these tables and
there are few tables that start with 'dbo.' which I can query them without
any problem.
Is there a way to reflect this change to all these tables?
Thanks.
--
Message posted via http://www.sqlmonster.com|||Sorry, ignore the above error but the exact error I got was: -
Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
The proposed new database owner is already a user in the database.
--
Message posted via http://www.sqlmonster.com|||Hi
Probably you cereated this table before the login was added to SysAdmin
server role, right?
Well , create a new table ,move the data from the old one to the new one,
drop the old table then rename the new table with original name
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd4c5051b6c0@.uwe...
> Hello,
> Some facts:
> - I am using SQL Server 2000 on W2K3
> - I have a SQL Login called 'Rlogic' with dbo permission rights on every
> database. It is also a member of the Systems Administrators Group.
> In Query Analyzer, when I run the following:-
> use Rlogic_Training
> go
> select *
> from users
>
> I get this error:
> Server: Msg 208, Level 16, State 1, Line 1
> Invalid object name 'users'.
> ---
>
> However, when I append 'Rlogic.' before the dbname, it works fine: -
> use Rlogic_Training
> go
> select *
> from Rlogic.users
> Is there a way for me NOT to append 'Rlogic.' everytime?
> Thanks,
> Sameer
> --
> Message posted via http://www.sqlmonster.com
>|||Hello,
PLease do not execute SP_CHANGEDBOWNER. You may need to use
SP_CHANGEOBJECTOWNER system procedure to change the object owner to DBO.
Use the below sample:-
Use <DBNAME>
GO
exec sp_changeobjectowner 'Rlogic.Users', 'dbo'
Thanks
Hari
"spremji via SQLMonster.com" <u4996@.uwe> wrote in message
news:6fd64dd4931f0@.uwe...
> Sorry, ignore the above error but the exact error I got was: -
> Server: Msg 15110, Level 16, State 1, Procedure sp_changedbowner, Line 47
> The proposed new database owner is already a user in the database.
> --
> Message posted via http://www.sqlmonster.com
>|||Uri,
No, the tables have been previously created by the Software installer and I
don't have the luxury to recreate the tables.
I have a db user 'Rlogic' which is associated with SQL Login 'Rlogic' as well
and they are both associated fine.
For some reason, the user 'Rlogic' is not able to query the tables despite
being the db owner.
Any help is appreciated as clients are waiting to work and no application is
able to login.
Thanks,
Sameer
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1|||OK....phew, problem was solved because the 'Rlogic' login was a member of
'Systems Administrators' group.
Hence, this login would log into the database with 'dbo' rights rather than
"user" named 'Rlogic'.
Thanks for the responses :)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200703/1