January 14, 2009 at 8:26 am
I am using SQL Server 2005 and connecting to it to do some web developement but when Dreamweaver connects to the database it takes forever to list the stored procedures and tables and ends up with a mighty long list of objects most of which belong to sys.
Is there a way I can get this user to see only the objects created by him?
January 17, 2009 at 5:44 am
You could if you can specify a WHERE clause. Unfortunately, I'm not familiar with Dreamweaver, so I don't know if that's possible. What's going on with sys.objects is in SQL Server 2005 and above, the user will be able to see all objects he/she has permissions to. So, for instance, if the user does not have any permissions against a particular table, sys.objects won't report any information on it. This is different from the SQL Server 2000 system table sysobjects, which returned information for all objects.
Now, if you can filter, remember that in SQL Server 2005 and above that users no longer own objects directly. Instead, objects are in schema and schema are owned by users. As a result, it's a slightly more complex query. Here's an example:
SELECT o.type_desc, s.NAME [schema], o.[name] [object]
FROM sys.objects o
JOIN sys.schemas s
ON o.[schema_id] = s.[schema_id]
WHERE s.principal_id = USER_ID('<Username>')
ORDER BY type_desc, [schema], [object];
K. Brian Kelley
@kbriankelley
January 17, 2009 at 12:35 pm
Interesting....
Many thanks Brian, I do really appreciate your reply.
I have just discovered how to do it. 😀
In Dreamweaver connection definition there is an advanced button which exposes a dialog where you can enter a schema name, all I did was put the user in there and bingo! only the users objects are returned :satisfied:
Cheers
Andy
January 17, 2009 at 3:26 pm
Outstanding. I didn't know Dreamweaver had that capability.
K. Brian Kelley
@kbriankelley
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply