I'm working with a existing database and trying to write a sql query to get out all the account information including permission levels. This is for a security audit. We want to dump all of this information out in a readible fashion to make it easy to compare. My problem is that there is a bridge/link table for the permissions so there are multiple records per user. I want to get back results with all the permission for one user on one line. Here is an example:
Table_User: UserId UserName 1 John 2 Joe 3 James Table_UserPermissions: UserId PermissionId Rights 1 10 1 1 11 2 1 12 3 2 11 2 2 12 3 3 10 2
PermissionID links to a table with the name of the Permission and what it does. Right is like 1 = view, 2 = modify, and etc.
What I get back from a basic query for User 1 is:
UserId UserName PermissionId Rights 1 John 10 1 1 John 11 2 1 John 12 3
What I would like something like this:
UserId UserName Permission1 Rights1 Permission2 Right2 Permission3 Right3 1 John 10 1 11 2 12 3
Ideally I would like this for all users. The closest thing I've found is the Pivot function in SQL Server 2005. http://geekswithblogs.net/lorint/archive/2006/08/04/87166.aspx The problem with this from what I can tell is that I need to name each column for each user and I'm not sure how to get the rights level. With real data I have about 130 users and 40 different permissions.
Is there another way with just sql that I can do this?