permissions to hit sysobjects

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

thanks
LVL 6
anushahannaAsked:
Who is Participating?
 
aelliso3Connect With a Mentor Commented:
the just need SELECT permissions:
 
GRANT SELECT ON sys.sysobjects to anushahanna,
0
 
anushahannaAuthor Commented:
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

0
 
tvPrasadConnect With a Mentor Commented:
account with sysadmin permissions
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
aelliso3Commented:
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

0
 
anushahannaAuthor Commented:
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;
0
 
anushahannaAuthor Commented:
tvPrasad, other than sysadmin, any other lower level roles?
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
actually, to READ ( SELECT) sysobject/sys.objects, you need no rights, because public role has read permissions to it implicitly, and any login mapped to the database (aka user) has public role implicitly.this is anyhow required, otherwise the user would not be able to "see" which objects he has actually permissions to and which not ...
0
 
tvPrasadCommented:
you can try

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

please run the code in # 33640028 above.
0
 
aelliso3Connect With a Mentor Commented:
Try this ...

CREATE LOGIN testlogin WITH PASSWORD='password1!'
ALTER LOGIN testlogin ENABLE
GO

create user testuser FOR LOGIN testlogin;
GO

CREATE PROC test AS SELECT 1
GO
GRANT EXECUTE ON master.dbo.test TO testuser
GRANT VIEW DEFINITION ON master.dbo.test TO testuser

GO

--GRANT select on sysobjects to testuser;
GO
EXECUTE AS user = 'testuser';
SELECT * FROM sysobjects WHERE TYPE = 'P' AND NAME = 'test';
SELECT system_user;
GO 
REVERT
GO
drop user testuser;
GO
drop login testlogin;
GO
drop proc test;
GO

Open in new window

0
 
aelliso3Connect With a Mentor Commented:
I wanted to take a second and go through and eliminate what was not needed in the script above. Below is a little cleaner version ...
CREATE LOGIN testlogin WITH PASSWORD='password1!'
CREATE USER testuser FOR LOGIN testlogin
GO

CREATE PROC test AS SELECT 1
GO

GRANT EXECUTE ON master.dbo.test TO testuser

EXECUTE AS user = 'testuser'
SELECT * FROM sysobjects WHERE TYPE = 'P' AND NAME = 'test'
SELECT system_user
GO 

REVERT
DROP USER testuser
DROP LOGIN testlogin
DROP PROC test
GO

Open in new window

0
 
Anthony PerkinsCommented:
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.
0
 
anushahannaAuthor Commented:
aelliso3, i see your point. thanks.
0
 
anushahannaAuthor Commented:
acperkins, this pertained to SQL 2005, and helped. thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.