Re: Getting a list of list permissions for WSS
- From: "Alex" <samalex@xxxxxxxxx>
- Date: 7 Jun 2006 07:15:34 -0700
Philip Lanier wrote:
You could probably figure out the SQL to do this, but you would want to do
this on a backup copy of the database so you're not hitting the live DB.
(See Mike Fitz's blog entry, "Please Stay out of the Database!!!",
http://blogs.msdn.com/mikefitz/archive/2005/04/01/404802.aspx).
Your best bet would probably be to use the Object Model to crawl those
sites/lists to get the info you're looking for... would require some light
programming, though.
Philip Lanier
Ascentium
Hi Philip,
But isn't this exactly what these third party vendors are doing???
Aren't they writing code that simply modifies the database, throwing a
web-based front-end on it, and selling it for thousands of dollars? I
understand how someone can easily fub-up their DB by throwing a ton of
junk in there, but if the database is laid out logically and the person
making edits understands the flow of how the DB works, the changes can
be made with ease, just like editing the ASP code itself.
The stored procedure I wrote that rolls-up the Events into one single
event was quite simple to add once I figured out how Microsoft setup
the logic in the DB. I'll attach below my process for doing this, and
it's working quite well. Granted it's not pretty, but it works, and
it's a great alternative for those $1600 plugins. I'd assume Microsoft
wouldn't mind publishing the layout of their database so others can
make similar updates, and the problem comes in not making the updates,
but not knowing HOW the database is laid out when they make their
updates.
-------- Start Stored Procedure ---------
-- Roll-up code I use in Stored Procedure. Runs hourly.
-- In all WSS sites I have added a new option in each Event list called
-- "Public Event" and if selected then it copies the event from the
-- WSS Event List to an SPS Event List, which in my database
-- has the ListID 034E17E0-CB94-47D8-B09C-D9926B072A68.
-- The only table change I made was creating a temp table called
-- rollup_userdata, which is identical layout to Userdata table
-- Define some variables to use within Stored Procedure
DECLARE @NewRowNumber int
DECLARE @MyUUID AS uniqueidentifier
-- First step, clear the contents of the temp table rollup_userdata
Delete from rollup_userdata
-- Second, delete all items in the SPS Event List i'm rolling-up to
Delete from Userdata where tp_listid
='034E17E0-CB94-47D8-B09C-D9926B072A68'
-- Third, find all List entries where the user has seleted Public Event
-- and insert them into the temp table rollup_userdata
Insert into rollup_userdata
select * from userdata where nvarchar5 = 'Public Event'
-- Fourth, Change the List ID to match the SPS Event List ID I'm
rolling-up to
Update rollup_userdata
set tp_listid = '034E17E0-CB94-47D8-B09C-D9926B072A68'
-- Fifth, Renumber TP_ID from 1 to whatever in the temp table. This
number
-- needs to be unique for each item in the List
set @NewRowNumber = 0 /* Starting Number - 1 */
update rollup_userdata
set @NewRowNumber = tp_id = @NewRowNumber + 1
-- Six, now change the GUID so it's different. Every List item in the
userdata
-- table must have a unique GUID.
SET @MyUUID = newid()
update rollup_userdata
set @MyUUID = tp_guid = newid()
-- And finally Seven, Re-insert into UserData table
Insert into userdata
select * from rollup_userdata
---- end of SP ---
Now when you run the script and refresh the SPS Event List which this
rolls up to, all the WSS Lists that have Public Event selected will be
in here. This List can not be edited directly as it deletes the
contents of the list when this script runs.
It works quite well, and having it scheduled to run hourly makes sure
my departmental Event Lists roll-up flawlessly to a single Portal list.
I see no problem writing scripts like this to modify the database, and
done with care they can add TONS of functions to Sharepoint. Why spend
hours trying to write such a script in C# or VB script when it can be
done from the database IMO much easier. Granted, this takes some
knowledge of MS SQL plus knowing how the database is logically laid
out, which if this were documented better would make such modifications
SO much simpler. It took me some time to figure out what data was in
what table, and after careful testing this script was written.
So for me, bottom line, I'm learning C# and VB script to do more
asp.net framework programming, but with my skills in MS SQL being very
much more proficient, it's easier for me to code from that angle now.
And as simple as it is (16 lines of code to do this roll-up), why spend
hours and MANY more lines of code doing the same thing via asp.net?
Take care --
Alex
.
- References:
- Getting a list of list permissions for WSS
- From: Alex
- Re: Getting a list of list permissions for WSS
- From: Philip Lanier
- Getting a list of list permissions for WSS
- Prev by Date: Portal vs. WSS version of Sharepoint
- Next by Date: Survey Permissions
- Previous by thread: Re: Getting a list of list permissions for WSS
- Next by thread: Database Layout for Sharepoint DB -- why not published?
- Index(es):
Relevant Pages
|
