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 |
|
bridge
Yak Posting Veteran
93 Posts |
 Posted - 2005-04-06 : 04:45:38
|
| The user logs into the application using Windows NT authentication. The objects dynamically created by the application will be owned by the NT user. Now if other NT user runs report on this object then he gets error Invalid Object Name as the SELECT statements are referencing only the object name rather the fully qualified name i.e., username.objectname. In this scenario what would be the best solution to change the object UID (user id) in sysobjects to 1 (dbo), so as every one could access the object. I wanted to create an insert trigger on sysobjects for this purpose but trigger can not be created on sysobjects. The same thing can be done using Jobs. But I am looking for a solution that may run only once, whenever a new object is created. Please give me some idea on this |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2005-04-06 : 06:42:56
|
| when you create the object be sure you prefix it with dbo. so that the owner will be the dbo instead of the user that created the objects.in this instance you can explore sp_changeobjectowner...--------------------keeping it simple... |
 |
|
|
bridge
Yak Posting Veteran
93 Posts |
Posted - 2005-04-06 : 07:10:55
|
| The application is the EXE file and can not be changed, whatever needs to be done is required at server side. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-04-06 : 08:44:50
|
quote:
The application is the EXE file and can not be changed
That application is poorly designed then. Creating objects in this environment should never be a problem, it should either assume dbo or allow the user to specify it. Secondly, a well-designed application never modifies or needs to modify the database schema in its regular operation anyway.If you cannot modify this app yourself, I'd suggest contacting the vendor and explaining the problem to them. This is certainly something they should be responsible for fixing (they broke it in the first place)Failing that, as Jen suggested, you'll have to run sp_changeobjectowner to work around it. Running it as a scheduled job would do the trick too. |
|
|
|
|
|
|
Changing Owner - Solution Needed