Link to home
Start Free TrialLog in
Avatar of vivek_andre
vivek_andre

asked on

3-part naming qualifiacation problem ...how to solve it???

i want such that any valid user in the database could have the permisssions to view the table

i created a database DEV using an account perd_dbo in a called server TEST and it has valid users called bob,masila... now the problem is that these users r getting the error-(table_name not found.Specify owner.objectname or use sp_help........), the permissions were granted as (all) while creating the tables still i have this problem. i am using Sybase ASE 12.5 and in a win2000 environment.i need bob,masila... to have access as perd_dbo has to the database.pls help me.

thank you for ur efforts
Avatar of amitpagarwal
amitpagarwal
Flag of India image

let us know the results of the query ..

select * from sysobjects where name = "yourtablename" and type = "U" ..

are you sure that you are looking into the same database ..

you can change databases using "USE dbname" ..
Avatar of alpmoon
I think you have created that table as a user other than dbo. In that case you should mention the table owner name, otherwise you would get "table_name not found". If you create the table as dbo (database owner) you wouldn't get that message.
Avatar of vivek_andre
vivek_andre

ASKER

what alpmoon said was true but i need a solution for that it is big databse that i moved i need a group of people to have all rights......any solution ????i want teh tables to be used without the user name.....there r around 700 tables which i migrated from the unix to win2k and around 110 views and 84 triggers and around 5504 stored procudres using the id perd_dbo.
there is this 3-part naming variable in the Sybase central which, while creating the new server , i selected...., if there is anything in the sp_config or somthing else that could solve the problem ...pls do give the syntax....thx
I think the only way to do this is modifying sysobjects table, however, you must be very carefull because you may corrupt the whole database --even dataserver may crash. But you can try to update uid and loginame columns of sysobjects in a test server after creating similar tables. If it works in test server then you can apply the same update statements in the actual database.

The other way may be to create proxy tables, but it might have some implications.
i got this error for the stmt that amitagarwal gave me and asked me for to do.....well any solution that someone can find on and pass to me???


1> use undcp        
2> go
1> select * from sysobjects where name="user_pref"and type="U"
2> go
Msg 10332, Level 14, State 1:
Line 1:
SELECT permission denied on column audflags of object sysobjects, database
undcp, owner dbo
----------------------------------------------
thank u all for ur efforts i think we r close to solve

maybe you are not the owner ..

why not say ..


select 1 from from sysobjects where name="user_pref"and type="U"


if you get 1 as the output that means your table exists else not ..
i got this error for the stmt that amitagarwal gave me and asked me for to do.....well any solution that someone can find on and pass to me???


1> use undcp        
2> go
1> select * from sysobjects where name="user_pref"and type="U"
2> go
Msg 10332, Level 14, State 1:
Line 1:
SELECT permission denied on column audflags of object sysobjects, database
undcp, owner dbo
----------------------------------------------
thank u all for ur efforts i think we r close to solve

amit i got this . Man u r fast to get a reply from....i am going to like this site and i am badly in need to setup this server up and running by the end of this year....

1>
2> select 1 from sysobjects where name="user_pref"and type="U"
3> go
             
 -----------
           1

(1 row affected)
amit i got this . Man u r fast to get a reply from....i am going to like this site and i am badly in need to setup this server up and running by the end of this year....

1>
2> select 1 from sysobjects where name="user_pref"and type="U"
3> go
             
 -----------
           1

(1 row affected)
i am the owner of the table that is , and the database not when i did go as in another user  for this sytx.

note: this is another user that i want to access the table from without the form databse.owner.table_name

DCPD2/export/home/landt>isql -Ulandt -STEST

1> use undcp
2> go

1> select * from sysobjects where name="user_pref"and type="U"
2> go
Msg 10332, Level 14, State 1:
Line 1:
SELECT permission denied on column audflags of object sysobjects, database
undcp, owner dbo

1> select 1 from sysobjects where name="user_pref"and type="U"
2> go
             
 -----------
           1

(1 row affected)


1> sp_help user_pref
2> go
Msg 17461, Level 16, State 1:
Procedure 'sp_help', Line 213:
Object does not exist in this database.
(return status = 1)
i am the owner of the table that is , and the database not when i did go as in another user  for this sytx.

note: this is another user that i want to access the table from without the form databse.owner.table_name

DCPD2/export/home/landt>isql -Ulandt -STEST

1> use undcp
2> go

1> select * from sysobjects where name="user_pref"and type="U"
2> go
Msg 10332, Level 14, State 1:
Line 1:
SELECT permission denied on column audflags of object sysobjects, database
undcp, owner dbo

1> select 1 from sysobjects where name="user_pref"and type="U"
2> go
             
 -----------
           1

(1 row affected)


1> sp_help user_pref
2> go
Msg 17461, Level 16, State 1:
Procedure 'sp_help', Line 213:
Object does not exist in this database.
(return status = 1)
so that is for sure that the table exists in the "undcp" database ..

i think either the users are connecting to the wrong database or they are trying to connect to this table from another database ..

why not ask them to modify the select statement to include the db name along with the table name or first switch to the above db and then run teh query ..
hai Amit

     the problem is that we r useing an application called Uniface which the Clients use it in our intranet in various places and this should work for the given clients listed.

    changing the select stmt. , then i will hve to change for so many table one by one it is too much to do.....isnt there any other way???
then we may need to find out which database do the clients connect actually and then create the db there ...

but this could be a bad approach as the client should be driven by where the db is and not vice versa ..
but the people who r working r also not able to view these table that i created using my account
i am damm sure that the rights r given and all.. i will give u a sample code for the user table
DB is UNDCP
table name is===== user_pref
owner(thats the account i used to create) is=== undcp_dbo

 create table user_pref
(user_id char(32) not null, name char(40) null,
language char(3) null, uniface_lang char(3) null,
warn bit not null, ext_user bit not null,
active bit not null, last_login datetime null,
last_login_fail datetime null, expire_date datetime null,
u_version char(1) null, cre_date datetime null,
rev_date datetime null, cre_user char(32) null,
rev_user char(32) null, description varchar(255) null)
grant all on user_pref to public
go

create unique clustered index user_prefI1
on user_pref(user_id)
execute sp_primarykey user_pref,user_id
go

create index user_prefI2
on user_pref(language)
go

create index user_prefI3
on user_pref(uniface_lang)
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_UPD"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_UPD
end
go

create procedure user_pref_UPD
@update1 char(32), @update2 char(40),
@update3 char(3), @update4 char(3),
@update5 bit, @update6 bit,
@update7 bit, @update8 datetime,
@update9 datetime, @update10 datetime,
@update11 char(1), @update12 datetime,
@update13 datetime, @update14 char(32),
@update15 char(32), @update16 varchar(255),
@where1 char(32), @where2 char(1),
@use_uversion char(30)
as begin
      /* check if u_version is used */
      if (@use_uversion = "T")
      begin
            update user_pref
            set name = @update2, language = @update3,
            uniface_lang = @update4, warn = @update5,
            ext_user = @update6, active = @update7,
            last_login = @update8, last_login_fail = @update9,
            expire_date = @update10, u_version = @update11,
            cre_date = @update12, rev_date = @update13,
            cre_user = @update14, rev_user = @update15,
            description = @update16
            where  (user_id = @where1 and u_version = @where2)
      end
      else
      begin
            update user_pref
            set name = @update2, language = @update3,
            uniface_lang = @update4, warn = @update5,
            ext_user = @update6, active = @update7,
            last_login = @update8, last_login_fail = @update9,
            expire_date = @update10, u_version = @update11,
            cre_date = @update12, rev_date = @update13,
            cre_user = @update14, rev_user = @update15,
            description = @update16
            where  (user_id = @where1)
      end
end
go

grant execute on  user_pref_UPD to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_INS"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_INS
end
go

create procedure user_pref_INS
@insert1 char(32), @insert2 char(40),
@insert3 char(3), @insert4 char(3),
@insert5 bit, @insert6 bit,
@insert7 bit, @insert8 datetime,
@insert9 datetime, @insert10 datetime,
@insert11 char(1), @insert12 datetime,
@insert13 datetime, @insert14 char(32),
@insert15 char(32), @insert16 varchar(255)
as begin
      insert into user_pref (user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description)
      values (@insert1, @insert2, @insert3, @insert4, @insert5,
      @insert6, @insert7, @insert8, @insert9, @insert10,
      @insert11, @insert12, @insert13, @insert14, @insert15,
      @insert16)
end
go

grant execute on  user_pref_INS to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_DEL"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_DEL
end
go

create procedure user_pref_DEL
@where1 char(32), @where2 char(1),
@use_uversion char(30)
as begin
      /* check if u_version is used */
      if (@use_uversion = "T")
      begin
            delete from user_pref
            where  (user_id = @where1 and u_version = @where2)
      end
      else
      begin
            delete from user_pref
            where  (user_id = @where1)
      end
end
go

grant execute on  user_pref_DEL to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_FET"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_FET
end
go

create procedure user_pref_FET
@where1 char(32)
as begin
      select user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description
      from user_pref
      where  (user_id = @where1)
end
go

grant execute on  user_pref_FET to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_LOC"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_LOC
end
go

create procedure user_pref_LOC
@where1 char(32)
as begin
      select user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description
      from user_pref holdlock
      where  (user_id = @where1)
end
go

grant execute on  user_pref_LOC to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_1S"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_1S
end
go

create procedure user_pref_1S
@select_pk smallint
as begin
      if (@select_pk = 1)
      begin
            select user_id
            from user_pref(1)
      end
      else
      begin
            select user_id, name, language, uniface_lang, warn,
            ext_user, active, last_login, last_login_fail, expire_date,
            u_version, cre_date, rev_date, cre_user, rev_user,
            description
            from user_pref(1)
      end
end
go

grant execute on  user_pref_1S to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_1SN"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_1SN
end
go

create procedure user_pref_1SN
@select_pk smallint,
@step_size smallint,
@index1 char(32)
as begin
      if (@select_pk = 1)
      begin
            declare @counter1 int
            select @counter1 = 0
            select user_id
            from user_pref(1)
            where (user_id > @index1)
end
else
begin
      declare @counter int
      select @counter = 0
      select user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description
      from user_pref(1)
      where (user_id > @index1)
end
end
go

grant execute on  user_pref_1SN to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_2S"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_2S
end
go

create procedure user_pref_2S
@where1 char(3)
as begin
      select user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description
      from user_pref(2)
      where  (language = @where1)
end
go

grant execute on  user_pref_2S to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_3S"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_3S
end
go

create procedure user_pref_3S
@where1 char(3)
as begin
      select user_id, name, language, uniface_lang, warn,
      ext_user, active, last_login, last_login_fail, expire_date,
      u_version, cre_date, rev_date, cre_user, rev_user,
      description
      from user_pref(3)
      where  (uniface_lang = @where1)
end
go

grant execute on  user_pref_3S to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_2SO"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_2SO
end
go

create procedure user_pref_2SO
@select_pk smallint,
@where1 char(3)
as begin
      if (@select_pk = 1)
      begin
            select user_id
            from user_pref
            where  (language = @where1)
            order by user_id
      end
      else
      begin
            select user_id, name, language, uniface_lang, warn,
            ext_user, active, last_login, last_login_fail, expire_date,
            u_version, cre_date, rev_date, cre_user, rev_user,
            description
            from user_pref
            where  (language = @where1)
            order by user_id
      end
end
go

grant execute on  user_pref_2SO to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_2SON"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_2SON
end
go

create procedure user_pref_2SON
@select_pk smallint,
@where1 char(3),
@index1 char(32)
as begin
      if (@select_pk = 1)
      begin
            select user_id
            from user_pref
            where  (language = @where1)
            and ((user_id > @index1))
            order by user_id
      end
      else
      begin
            select user_id, name, language, uniface_lang, warn,
            ext_user, active, last_login, last_login_fail, expire_date,
            u_version, cre_date, rev_date, cre_user, rev_user,
            description
            from user_pref
            where  (language = @where1)
            and ((user_id > @index1))
            order by user_id
      end
end
go

grant execute on  user_pref_2SON to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_3SO"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_3SO
end
go

create procedure user_pref_3SO
@select_pk smallint,
@where1 char(3)
as begin
      if (@select_pk = 1)
      begin
            select user_id
            from user_pref
            where  (uniface_lang = @where1)
            order by user_id
      end
      else
      begin
            select user_id, name, language, uniface_lang, warn,
            ext_user, active, last_login, last_login_fail, expire_date,
            u_version, cre_date, rev_date, cre_user, rev_user,
            description
            from user_pref
            where  (uniface_lang = @where1)
            order by user_id
      end
end
go

grant execute on  user_pref_3SO to public
go

if exists (select sysobjects.name from sysobjects
where sysobjects.name = "user_pref_3SON"
and sysobjects.uid = user_id() and type = "P")
begin
      drop procedure user_pref_3SON
end
go

create procedure user_pref_3SON
@select_pk smallint,
@where1 char(3),
@index1 char(32)
as begin
      if (@select_pk = 1)
      begin
            select user_id
            from user_pref
            where  (uniface_lang = @where1)
            and ((user_id > @index1))
            order by user_id
      end
      else
      begin
            select user_id, name, language, uniface_lang, warn,
            ext_user, active, last_login, last_login_fail, expire_date,
            u_version, cre_date, rev_date, cre_user, rev_user,
            description
            from user_pref
            where  (uniface_lang = @where1)
            and ((user_id > @index1))
            order by user_id
      end
end
go

grant execute on  user_pref_3SON to public
go
were you able to execute this script successfully ?

i guess there could be an error in the create proc statement .. paranthesis may be required while giving the parameter list ..
it is perfect and successfully thru.i did a BCP with a moiving shell script.....this table codes were generated by UNIFACE. these tables existed in another server called DEV planted in UNIX. and now the one that i am trying to do is to have the same undcp db that is in DEV to a server called TEST which is in Win2000.
i havnt lost any tables in moving from DEV to TEST i checked it out myself manually with the views and the triggers and the stored procedures all of it exists in TEST now the only problem is tht only undcp_dbo the account that i used with is albe to view it and execute anything in this undcp db.all the other users that were there in DEV were valid users for undcp db and so it is in TEST.it should have worked.
hai Amit

     the problem is that we r useing an application called Uniface which the Clients use it in our intranet in various places and this should work for the given clients listed.

    changing the select stmt. , then i will hve to change for so many table one by one it is too much to do.....isnt there any other way???
ASKER CERTIFIED SOLUTION
Avatar of alpmoon
alpmoon
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Silly question - are you sure the users  bob,masila... etc are the actual users bob,masila.. when logged in? - should be easy to check with sp_who. Some applications are build using a pseudo-login by security reasons, but the users are (and should be) unaware of this fact. Maybe they all use a common login with restricted permissons?

/Orjan
Hi vivek_andre,

maybe it's a bit late but did you try to make an alias of your user as the dbo?

I mean, your user can't use the table without naming the owner, right?

Try:

EXEC sp_dropuser 'bob'
EXEC sp_dropuser 'masila'


EXEC sp_addalias 'bob','undcp_dbo'
EXEC sp_addalias 'masila','undcp_dbo'


I also will check if all rights are granted on public group... who knows, maybe it's just a problem there!

BerX
i dont want the other users to access using the 3level qualification (db.owner.table).i just want the users to access the table directly.and i dont want to give the rights of the DBO to all users as berXpert said using sp_addalias.any sloution????