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.
| Author |
Topic |
|
tikus
Starting Member
31 Posts |
 Posted - 2007-01-31 : 19:14:01
|
| When somebody schedule report in Report Manager, that person becomes the owner of the subscription. The problem is, when that person left the company and we disable his windows login, all his subscriptions failed...... 1. How do we schedule a job by using a standard login instead of individual windows login? 2. Is there a way I can change the owner of all the existing subscriptions?Thanks! |
|
|
jhermiz
3564 Posts |
Posted - 2007-02-01 : 09:50:35
|
Morning tikus,By default SSRS uses Windows Authentication. The only other way around this is to use Forms Authentication. I've posted a link herehttp://www.sql-server-performance.com/absolutenm/templates/?a=1111&z=0On how to accomplish what you want to do using forms authentication. It is the third Question down.Your second question is you need to tap into the ReportServer database and UPDATE the table called Subscriptions. The field you want to update is the OwnerID field. The issue is the OwnerID field is of GUID type, sample data might look like this:{FF723231-1057-4628-B406-2E1B8685BE44}Of course there is a simple solution to this :). You will need to view the Users table in RS.SELECT UserID, UserName FROM UsersThis will give you the UserID (which is the OwnerID) in the Subscriptions table. A simple join will tell you who the owner is of each report:SELECT s.OwnerID, u.UserName FROM Subscriptions s INNER JOINUsers u ON u.UserID = s.OwnerIDDoes that help ?Take a look at my article on the power of the report server for more info:http://jhermiz.googlepages.com/powerofreportserver
Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url] |
 |
|
|
tikus
Starting Member
31 Posts |
Posted - 2007-02-01 : 13:16:41
|
| Very, very helpful! Thanks a lot!!!When I checked Users table, I saw a user called BUILTIN\Administrators, is it a good idea to use this one for all the subscripitons? |
|
|
|
tikus
Starting Member
31 Posts |
Posted - 2007-02-06 : 15:51:28
|
| I changed all the Subscriptions.OwnerID to BUILTIN\Administrators's UserID. All the subscriptions work fine after that. However, after we disabled Windows login for the old owner, the ReportServerService log file report the same error again: ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.UnknownUserNameException: The user or group name 'XXXXX\XXXXX' is not recognizedIs there anywhere else i need to modify? Thanks. |
|
|
|
tikus
Starting Member
31 Posts |
Posted - 2007-02-06 : 16:07:35
|
| One more thing, even though the log file reported error, the subscriptions did run successfully. And in Report Manager the Status of the reports showed "Pending". |
|
|
|
|
|
|
|
|
subscription owner