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
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
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.
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.
ASKER
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.
The other way may be to create proxy tables, but it might have some implications.
ASKER
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
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 ..
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 ..
ASKER
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
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
ASKER
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)
1>
2> select 1 from sysobjects where name="user_pref"and type="U"
3> go
-----------
1
(1 row affected)
ASKER
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)
1>
2> select 1 from sysobjects where name="user_pref"and type="U"
3> go
-----------
1
(1 row affected)
ASKER
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>is ql -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)
note: this is another user that i want to access the table from without the form databse.owner.table_name
DCPD2/export/home/landt>is
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)
ASKER
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>is ql -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)
note: this is another user that i want to access the table from without the form databse.owner.table_name
DCPD2/export/home/landt>is
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 ..
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 ..
ASKER
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???
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 this could be a bad approach as the client should be driven by where the db is and not vice versa ..
ASKER
but the people who r working r also not able to view these table that i created using my account
ASKER
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
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 ..
i guess there could be an error in the create proc statement .. paranthesis may be required while giving the parameter list ..
ASKER
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.
ASKER
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.
ASKER
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???
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
/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
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
ASKER
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????
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" ..