Database access permissions issue (sql server 2008)

Question

I have created a C# app that reads a config file, gets connection strings, and for each connection string it reads list of stored proc for some further processing. Now, I'm facing some permissions issues. When developing the app I was using connection strings with dbo level users. Real life connection strings are for sql users with only datareader and datawriter permissions, but when I execute

select user_name(uid) as owner, name from dbo.sysobjects where type='P' order by name asc

to get a list of procs using such a user, the query returns no records (though it returns the procs if I execute as dbo)

I don't want to give extra permissions to users in connection strings for security reason, so my question is - how can a user who only has datareader/datawriter permissions get a list of stored procs, or if it's not possible, what's the minimal permissions the user must have to have access to that info?

I'm using SQL Server 2008

as an aside: on SQL Server 2005 and up, I'd use sys.procedures rather than the deprecated "sysobjects". See the MSDN docs on sysobjects here: msdn.microsoft.com/en-us/library/ms177596.aspx — marc_s

Jacob G Jacob G · Accepted Answer · 2010-02-05T19:57:10

I would create a stored procedure attached to credentials owned by my application that would take in the User parameters and query sysobjects to return the available procs. Subsequent execution of those procs could be done by the specific connection strings you mention.

Database access permissions issue (sql server 2008)

3 Answers