Is this a permissions issue?

Hi All I have created a Stored proc to get the all user rights on a server b
y
database. I am going to use this SP in my reporting services 2005. I
created a user with public rights in all Databases on my server. For some
reason unless I grant this user SA rights, it will not return any rows. whe
n
I give him SA rights all rows return correctly. I am attaching my code can
someone see why SA rights are the only rights that seem to work?
TIA,
Joe
Here is the code!
/*
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_DBO_Audit') )
DROP table SOX_Audit..Temp_DBO_Audit
create table SOX_Audit..Temp_DBO_Audit (DBNAme varchar(50),DbFixedRole
varchar(25),MemberName varchar(50),MembersIS Varbinary(85))
if exists (select * from sysobjects where id =
object_id('SOX_Audit..Temp_Users_Audit') )
DROP table SOX_Audit..Temp_Users_Audit
create table SOX_Audit..Temp_Users_Audit (DbFixedRole varchar(25),MemberName
varchar(50),MembersIS Varbinary(85))
*/
delete Temp_DBO_Audit
delete Temp_Users_Audit
declare @.DBName varchar(50), @.str varchar(100)
Declare crsCall cursor for
select name from master..sysdatabases
open crsCall
fetch Next from CrsCall
into @.DBName
while @.@.fetch_status=0
begin
select @.str = 'use ['+@.DBNAMe+']'+ Char(13) +
'insert into SOX_Audit..Temp_Users_Audit exec sp_helprolemember '
--select @.str
exec (@.str)
insert into SOX_Audit..Temp_DBO_Audit
select @.DBNAme, DbFixedRole, MemberName, MembersIS
from SOX_Audit..Temp_Users_Audit
delete SOX_Audit..Temp_Users_Audit
fetch Next from CrsCall
into @.DBName
end
close crsCAll
deallocate crsCAll
select DBName, dbFixedRole, MemberName from SOX_Audit..Temp_DBO_Auditjaylou (jaylou@.discussions.microsoft.com) writes:
> Hi All I have created a Stored proc to get the all user rights on a
> server by database. I am going to use this SP in my reporting services
> 2005. I created a user with public rights in all Databases on my
> server. For some reason unless I grant this user SA rights, it will not
> return any rows. when I give him SA rights all rows return correctly.
> I am attaching my code can someone see why SA rights are the only rights
> that seem to work?
That is indeed a permissions issue. In SQL 2005, you are not permitted
to see metadata in the same way as you were in previous versions of SQL
Server. Essentially, you need permissions to view metadata. Sometimes
that is implicit. If you have SELECT permissions on a table, you also
have VIEW DEFINITION on the table.
The hour is late, and I don't know on the top of my head which exact
privileges that are needed to view this kind of information. Then
again, to be permitted to the information your procedure retrieves,
you should have fairly hefty privileges.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx