March 12, 2007 at 12:27 pm
We're trying to move forward with our upgrade from SQL 2000 to SQL 2005. One of the issues that's holding us up, is that we support several legacy applications that have in line SQL querying against the system tables.
I was hoping that the Compatibility Views would work for these queries, allowing us to continue supporting these apps on SQL 2005. However, when you run a query against these views (particularly sysusers and sysmembers) with an account that isn't dbo, they return a subset of what you get when run by dbo. This behavior is different from the SQL 2000 system tables that these views were supposed to emulate.
I tried giving the application account explicit SELECT permission on these views, but no luck. Is there any way to change the behavior of these views without giving away the farm (i.e. Making our application login dbo).
March 12, 2007 at 12:47 pm
Execute As might work, not sure you can have a user impersonate DBO though. It is fairly well documented in Books Online under impersonation.
March 12, 2007 at 1:08 pm
Unfortunately, to add an Execute As clause, I would need to wrap the In Line SQL in a stored proc or function. If that's the case, I may as well update the SQL not to access the old system tables. Ideally, what you'd like to happen, but that will be very time intensive. And may not be able to fit into the schedule before our upgrade.
I did find that adding the application account to the db_securityadmin role causes these views to become fully transparent. However, this isn't a whole lot better than dbo.
March 12, 2007 at 1:55 pm
I tried to get a definition of sys.sysusers view by selecting:
SELECT
OBJECT_DEFINITION(OBJECT_ID('sys.sysusers'))
I got a definition, but the FROM list includes tables like sys.sysowners. If I try to select from sys.syowners, it says , it does not exist. If I try to select from sysobjecs, it does exist and the schema owner is sys (UID = 4)
select
* from sysobjects where name like '%sysowners%'
The goal was to see what it was in a view definition that prevent non-dbo to select all users. There is a line in a view
WHERE has_access('US', u.id) = 1
But the function has_access does not exist too if I try to run the select statement from the view definition manually. I tried connecting as SA too, same result.
Regards,Yelena Varsha
March 13, 2007 at 12:42 am
The security around the metadata has been changed from 2000 to 2005. Only if a user has some right to an object can they see it in the system views.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2007 at 11:33 am
to make compatible behaviours in terms of "metadata" you need to execute:
GRANT VIEW ANY DEFINITION to xxx
GRANT VIEW SERVER STATE to xxx
In terms of security it safer to make someone part of db_securityadmin than make it dbo. I know that is not what you want but those are the choises.
The security settings in 2005 have been revised but I for one am less than pleased with things that sometimes do not make sense ex: if you are member of db_owners you can run update statistics on one table but you can execute sp_updatestats ( which does it on all) and the procedure is hardcoded to do just that.
Sorry but I am having a bad day..
Cheers,
* Noel
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply