Link to home
Start Free TrialLog in
Avatar of anushahanna
anushahannaFlag for United States of America

asked on

permissions to hit sysobjects

what permissions does a user need to be able to select sysobjects?

thanks
ASKER CERTIFIED SOLUTION
Avatar of aelliso3
aelliso3
Flag of United States of America 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
Avatar of anushahanna

ASKER

he does't hit it, still. can you see why?
create proc test as select 1
create login testlogin with password =  'password1!'
create user testuser FOR LOGIN testlogin
grant select on sysobjects to testuser
execute as user = 'testuser'
select * from sysobjects where type = 'P' and name = 'test'
revert
drop user testuser
drop login testlogin
drop proc test

Open in new window

SOLUTION
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
It's working great on mine ... Are you using a seperate session to execute the sproc?
create proc test as select 1
create login testlogin with password =  'password1!'
create user testuser FOR LOGIN testlogin
grant select on sysobjects to testuser
execute as user = 'testuser'
select * from sysobjects where type = 'P' and name = 'test'
revert
drop user testuser
drop login testlogin
drop proc test
GO 

EXEC test

Open in new window

aelliso3, i still could not make it happen. can you execute it without the 'EXEC test' line- that is not needed for this test, right?

I changed you code with lots of GOs to make it sure that it will err out... can you try this:
----------------------------
create proc test as select 1;
GO
create login testlogin with password =  'password1!';
GO
create user testuser FOR LOGIN testlogin;
GO
grant select on sysobjects to testuser;
GO
execute as user = 'testuser';
GO
select * from sysobjects where type = 'P' and name = 'test';
GO
select system_user;
GO
revert;
GO
select system_user;
GO
drop user testuser;
GO
drop login testlogin;
GO
drop proc test;
tvPrasad, other than sysadmin, any other lower level roles?
SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg 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
Avatar of tvPrasad
tvPrasad

you can try

GRANT SELECT, EXECUTE ON sys.sysobjects TO anushahanna
angelIII, tvPrasad - that does not seem to be sufficient..

please run the code in # 33640028 above.
SOLUTION
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
SOLUTION
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
Just for the record, are you still using SQL Server 2000, because all the solutions so far seem to assume you are using SQL Server 2005.
aelliso3, i see your point. thanks.
acperkins, this pertained to SQL 2005, and helped. thank you.