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
Actually I have figured it out - the user needs to be granted permissions to each stored proc we want listed