-- select the desired database to fix the user accounts
--Use ;
EXEC sp_change_users_login 'report'--See all orphaned users in the database.
DECLARE @OrphanedUsers TABLE
(
IndexKey Int IDENTITY(1,1) PRIMARY KEY,
UserName SysName,--nVarChar(128)
UserSID VarBinary(85)
)
INSERT INTO @OrphanedUsers
EXEC sp_change_users_login 'report'
DECLARE @CRLF as nVarChar
SET @CRLF = CHAR(10) + '&' + CHAR(13)--NOTE: Carriage-Return/Line-Feed will only appear in PRINT statements, not SELECT statements.
DECLARE @Sql as nVarChar(MAX)
SET @Sql = N''
DECLARE @IndexKey as Int
SET @IndexKey = 1
DECLARE @MaxIndexKey as Int
SET @MaxIndexKey = (SELECT COUNT(*) FROM @OrphanedUsers)
DECLARE @Count as Int
SET @Count = 0
DECLARE @UsersFixed as nVarChar(MAX)
SET @UsersFixed = N''
DECLARE @UserName as SysName--This is an orphaned Database user.
WHILE (@IndexKey <= @MaxIndexKey)
BEGIN
SET @UserName = (SELECT UserName FROM @OrphanedUsers WHERE IndexKey = @IndexKey)
IF 1 = (SELECT COUNT(*) FROM sys.server_principals WHERE Name = @UserName)--Look for a match in the Server Logins.
BEGIN
SET @Sql = @Sql + 'EXEC sp_change_users_login ''update_one'', [' + @UserName + '], [' + @UserName + ']' + @CRLF
SET @UsersFixed = @UsersFixed + @UserName + ', '
SET @Count = @Count + 1
END
SET @IndexKey = @IndexKey + 1
END
PRINT @Sql
EXEC sp_executesql @Sql
PRINT 'Total fixed: ' + CAST(@Count as VarChar) + '. Users Fixed: ' + @UsersFixed
SELECT ('Total fixed: ' + CAST(@Count as VarChar) + '. Users Fixed: ' + @UsersFixed)[Fixed]
EXEC sp_change_users_login 'report'--See all orphaned users still in the database.
I have been looking around the net for some decent code which would explain how I could render a report, using SSRS 2008 web services as a PDF. The need was to extract reports sitting on a SSRS 2008 server sitting on a NT domain on a trusted network, whereas my web server was sitting in a DMZ. Where the only communication allowed by the network admin was port 80. To do this you will need to use the SSRS2008 ReportExecution2005.asmx web service. This could be accesses using the following URL assuming your SSRS server was installed using the default settings. http://YourServerIP/reportserver/reportexecution2005.asmx?wsdl 1. Create a user on your AD domain with the least amount of privileges (say ReportUser) 2. Give this account browse access on the reporting server for the desired reports. 3. To get this working in visual studio 2010 (I am using the Premium edition) Right click on your project and add a service reference to the above web serv
Comments
Post a Comment