No data returned due to security

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

No data returned due to security

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-18 : 22:57:22

I have the following procedure that returns the data fine in a report when run as the owner. However if I run it from another computer logged in as a domain user the report runs and displays the layout but no data is returned. Below is the procedure and the permissions I have tried so far with no luck? I guess the user does not have access to correct views or tables? Also can the proxy account be set to a user group like builtin\users with no password specified?

ALTER PROCEDURE [dbo].[Database_Info]

AS

SET NOCOUNT ON

Select b.[Name],a.database_ID,a.DBSizeMB,*
From
(
Select aa.database_ID,Sum(cast(aa.[Size] as float)/128) as [DBSizeMB]
From
sys.master_Files aa
--Where Type=0 (0 = exclude log file)
Group by aa.DataBase_ID
) a
Inner Join
Sys.Databases b
on a.Database_ID = b.DataBase_ID
ORDER BY a.DBSizeMB DESC

SET NOCOUNT OFF

RETURN

EXEC sp_xp cmdshell_proxy_account 'adpro.com.au\test', 'mypassword'
GO
USE [MASTER]
GO
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users]
GO
EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
GO
GRANT EXECUTE ON [sys].[xp cmdshell] TO [BUILTIN\Users]; (This is for another SP)
GO
GRANT SELECT ON [sys].[databases] TO [BUILTIN\Users]
GO
GRANT SELECT ON [sys].[master_Files] TO [BUILTIN\Users]
GO

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2008-11-24 : 06:44:11

Are you using Reporting Services or another method to run report?

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2008-11-24 : 17:38:59

I am using reporting services with report manager 2005. 95% of my reports work without any extra security settings. It's just the few that use cmd shell and sysytem views that I need to work out a simple way of allowing the BUILTIN\Users group to be able to run by just adding permissions to these functions and views. If I set the BUILTIN\Users to server role = sysadmin then it all works. Looking for a simple way of allowing all BUILTIN\Users to run my reports that use exec cmd shell functions and master views etc without giving them full sysadmin rights.

I tried this which allowed my cmd shell to run under this specific user. I really need the BUILTIN\Users group as I do not know who all the users will be.

EXEC sp_xp cmdshell_proxy_account 'adpro.com.au\test', 'Passwordhere'
GO

USE [MASTER]
GO
CREATE USER [BUILTIN\Users] FOR LOGIN [BUILTIN\Users]
GO
EXEC sp_addrolemember N'db_datareader', N'BUILTIN\Users'
GO
GRANT EXECUTE ON [sys].[xp cmdshell] TO [BUILTIN\Users];
GO
GRANT SELECT ON [sys].[databases] TO [BUILTIN\Users]
GO
GRANT SELECT ON [sys].[master_Files] TO [BUILTIN\Users]
GO

quote:
Originally posted by darkdusky

Are you using Reporting Services or another method to run report?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources