changing the structure of data in a table

I have a table that looks like this:
ID Type
123 Phone
123 Meeting
123 Phone
and I would like the data to look like this
ID phone Meeting
123 2 1
How do I do this?Search for crosstab, but I am sure someone will post you a code, just sit tight :)|||Code Please ?|||See this:

http://www.dbforums.com/showthread.php?threadid=978654sql

Changing HOME folder per user

I am wondering how I could do the following -

Right now the default folder is Home, what if I have two users User1 and User2, my folder structure is the following -
Home/User1
Home/User2

I want each user to only have access to their folder. That is easy to do.

Once both users have proper access to their folders they still don't have access to Home folder. In Report Builder when I do 'open file' it defaults to Home folder so I get an error. How can I specify the home folder to be User1 for that user and User2 for the second user? I know I can give them both browsing permissions to Home but it's not very elegant.

There must be a way to programmatically change the folder as soon as proper user logs in. Anyone knows how to do that?

Thanks.
Anybody?

How about this, is there a way to do custom folder access, for example a user who has a proper flag set in a custom DB can only have access to one set of models, another user to another and so on. None of this is in RS DB except for one big organizational user who has access to everything. I want to be able to restrict access on the fly so once the user logs in using custom DB credentials they can only have access to some things and not the others.

Thanks
|||

There's no way to change the home folder programmatically. What you can do is given everyone browser permission on the home folder and then content manager or publisher permission on their home/user1 folder.

Not sure if you're aware of the My Reports feature that essentially lets you not have to manage the creation of User1..N folders your self. Everyone would see a folder called "My Reports".

-Lukasz

|||Thanks for your answer. The solution you proposed is what I'm doing right now.

As to My Reports, I am trying to get away from RS users/roles completely. We have all this defined in a custom DB and it's a real pain for me to make all that security work with RS.

Right now I'm trying to figure out how to get a folder name from CheckAccess so I can see in my DB if I should allow access to this folder from this user. Not sure if that's possible at all. Ideally my RS would only have one user (main RS admin) and the rest of the users/roles/permissions would be taken from a custom DB and inside my authentication extension I would give out permissions depending on that.
|||You can build your custom web that integrates your custom security (DB) with that you will be 100% positive that you are listing / giving the right access to the users.|||

Paul.G. wrote:

You can build your custom web that integrates your custom security (DB) with that you will be 100% positive that you are listing / giving the right access to the users.

I am not even using the web at this point. All the client is given is a link to Report Builder. There is a lot of underlying stuff other than the website that I have to deal with, that's the issue.
|||

RS does not expose the folder/path of the item being accessed to the security extension. We don't really support this scenario very well, but it is possible with enough determination :-).

The key thing is that the security descriptor stored with a given item in RS is entirely in the control of the authorization extension. The authorization extension builds the ACL, sets the group names, etc. You could try using it to store some relation information associated with the item. Then you'd use this information in your checkaccess method implementation to go look up the corresponding values in your own database.

The consequence of this approach is that assigning role based security in the RS UI/tools is likely to result in mismatches with your database. You'll want to write your own code to manage the security rights and not allow users to change it on the RS side.

Hope that helps,

-Lukasz

|||So you are saying that somehow I can relate acl info to something in my database to add more information to every entry while creating a descriptor?

That sounds interesting. Is there some kind of a unique key I can tie this extra info to?

Thanks very much for the idea.

Code Snippet

public byte[] CreateSecurityDescriptor(
AceCollection acl,
SecurityItemType itemType,
out string stringSecDesc)
{
BinaryFormatter bf = new BinaryFormatter();
using (MemoryStream result = new MemoryStream())
{
bf.Serialize(result, acl);
stringSecDesc = null;
return result.GetBuffer();
}
}

|||Why doesn't CreateSecurityDescriptor execute on folder creation? Shouldn't the descriptor be created during that time?

And once again, is there any unique key (I guess it would be in acl) that I can tie custom stuff to (like have it as a uuid in my db which corresponds to whatever extra security I create)?
|||

Not sure of the specific reason, but the default is to inherit from parent.

Unique IDs - you should assigne a unique ID in your own database of items, for example you might use the rowguid. This will make it *much* eaiser to relate items in SRS to your database. It will also mean that the first step in item creation in SRS is to create the item in your own database. Natural consequences are 1) you need to build custom publishing/management interfaces in your application and 2) you'll need to ensure your applications service account has sufficient permission in RS to at least craete all items and 3) you don't want users publishing reports directly against SRS - this would invalidate your security extension model.

Hope that helps,

-Lukasz

Changing Database Structure After Application Deployment

Hi,
We're planning to rewrite a program of ours from ms-dos with a dbase
database to windows using ado and msaccess or sqlserver 2000. now we have
the problem of database maintenance. because the program is going to be
deployed to multiple customers, we don't want to manually change the
database structure. In dos we had a mechanism with a table which stored the
table definitions, and a table which stored the index definitions. now we
want something similiar to that for windows, and it would be even nicer if
the solution is crossdatabase. it's fine if the "new" database structure(the
structure which the customer doesn't have) is stored in an xml file or
similar.
thanks in advance,
Matthijs ter Woord
if you want to version your databases check out DB Ghost - www.dbghost.com
"M. ter Woord" wrote:

> Hi,
> We're planning to rewrite a program of ours from ms-dos with a dbase
> database to windows using ado and msaccess or sqlserver 2000. now we have
> the problem of database maintenance. because the program is going to be
> deployed to multiple customers, we don't want to manually change the
> database structure. In dos we had a mechanism with a table which stored the
> table definitions, and a table which stored the index definitions. now we
> want something similiar to that for windows, and it would be even nicer if
> the solution is crossdatabase. it's fine if the "new" database structure(the
> structure which the customer doesn't have) is stored in an xml file or
> similar.
>
> thanks in advance,
> Matthijs ter Woord
>
>
|||Can somebody think of a solution which i can build myself?
Matthijs
"mark baekdal" <markbaekdal@.discussions.microsoft.com> schreef in bericht
news:821F7892-552F-4784-9D0A-A3E36D615735@.microsoft.com...
> if you want to version your databases check out DB Ghost -
www.dbghost.com[vbcol=seagreen]
> "M. ter Woord" wrote:
have[vbcol=seagreen]
the[vbcol=seagreen]
we[vbcol=seagreen]
if[vbcol=seagreen]
structure(the[vbcol=seagreen]

Changing database at runtime

Hello,
I have 2 identical databases (identical structure) on the same sql server
correspoding to 2 companies.
Do I have to create 2 identical sets of reports or it is possible to choose
database on runtime?
Thank you,
LoriIn SQL 2005, you can make a dynamic connection, but not in sql 2000... about
the best you can do is use
Dynamic SQL, and OpenRowset or a linked server to do your queries ie.
If Parameters!.DataSource.value = 'a'
Select * from mytable
Else
Select * from openrowset(all the parameters that get you to the other
server...)
You could also put this in a SP... I am currently working on a project which
has about a dozen servers. We are making stored procedures which use the
openrowset function based on a parameter sent to the sp , which comes from
the Report Parameters.
As long as there are no cross server (multi server) joins etc. performance
has been alright...
I would prefer to use a linked server however... to centralize the login and
server info in one place instead of all over..
good luck...
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
(Please respond only to the newsgroup.)
I support the Professional Association for SQL Server ( PASS) and it's
community of SQL Professionals.
"Lori" <lhaiducescu@.seniorsoftware.ro> wrote in message
news:ev47$5ZoFHA.2920@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have 2 identical databases (identical structure) on the same sql server
> correspoding to 2 companies.
> Do I have to create 2 identical sets of reports or it is possible to
> choose database on runtime?
> Thank you,
> Lori
>