TFS 2008 : Permissions for Creating Team Projects

I recently finished my Team Foundation build and am quite pleased with it. With Team Foundation Server being new to me, I ran into a few issues with permissions.

The Team Foundation server I built will be managing my team's code as well as two other teams once everyone has moved over.

That being said, I need to give out permissions for the managers of each group so they can create team projects for their teams.





Let's get started

Permissions for this are somewhat complicated in that they have to be given in three different locations for the user(s) to be able to create Team Projects and all of the other services associated with one.




We'll start off with the TFS permissions.

By default, the service account has permissions to perform these actions so you should be logged in as that account.

Head to your Team Explorer in Visual Studio and connect to your Team Foundation Server. Right-click your server name, go to Team Foundation Server Settings, and select Group Membership. From within here you should see a group titled Team Foundation Administrators. Double-click this group and add your user/group to it and you're done with the TFS part.


*NOTE* To keep the Administrators group from becoming cluttered with usernames and group names, I created a TFS group for Administrators ( for instance) and simply added that group to Team Foundation Administrators.




Next up we'll tackle SQL Reporting Services permissions.

For this one you want to go to your SQL Reporting Services page (mine was http://servername/Reports ).

Once there, click the Properties tab. Click New Role Assignment on the page that loads. From here, enter the active directory username or group name you want to have permissions to create team projects in TFS and assign them at least the Content Manager role. Once you're done, click OK and you're done with the permissions for SQL Reporting Services.




Finally, Windows SharePoint Services permissions.

Head to your central administration site (Start -> Administrative Tools -> SharePoint 3.0 Central Administration). Once there, click the Operations tab at the top left. On that page, select Update farm administrators's group (it's under the Security Configuration group). From here, click New to add a new user (the button also has a drop down function so if you get that, click Add User). On the AddUser: Central Administration page, type in the username or groupname and add them to the Farm Administrators [Full Control] group.



There you have it. You should now be good to add as many Team Projects as you desire.


A closing thought: I would recommend that the majority of this be done through active directory groups. It makes usermanagement much easier. If someone quits or it let go, all you have to do is remove their account from the group in active directory and it takes care of everything in Sharepoint, TFS, and SQL Reporting services instead of having to manually go in and remove the person from every location.


On a side note, I'm going to get some lunch...

Migrating SQL Data

In my current project I have found a need to migrate data from one SQL server to another. For those of you SQL gurus out there, you know that there are many ways to migrate data from point A to point B in SQL, even when different versions is involved.

Here's my setup

Server 1:

SQL Server 2005 Standard (x86)

Windows Server 2008 (x86)

Server 2:

SQL Server 2008 Standard (x64)

Windows Server 2008 (x64)

As you can tell, I'm upgrading versions and processor architectures. Surprisingly enough, this didn't seem to cause any issues for me.

Here are a few options one has to migrate SQL data between servers for those who don't find this post too useful.

1.) SQL Copy Database Wizard

2.) Detach, copy to new server, and reattach

3.) Backup database, copy backup to the new server, convert backup to a database, attach the converted database

4.) Create a database mirror

5.) Duplicate the database structure on server two and import the data from server 1

For my environment, only 1, 3, and 5 would work since the others leave more possibility for data integrity issues during the transfer or require that the SQL server be temporarily taken offline. I tried out a few of my options and decided that 1, the SQL Copy Database Wizard, was the best option. It's relatively straightforward and very efficient.

For the last three days I have been struggling with it because of what looks like permissions issues, though I can't be sure since all the error says is that step 1 was the last step to run and that the job failed (give me ambiguity or give me something else!). All that being said, I decided I needed to find a new way to transfer the data.

Through all of my troubleshooting I found quite a few SQL facets that I pieced together to get what I needed. Here's how I chose to migrate my 12 databases without too much trouble.

To start, I used SQL Server's remarkable "Script Database as" functionality to write out the query that creates the database, all of the columns, and all of their constraints. For mine I just copied the script to the clipboard to make compiling all of the scripts together much faster. To sum it up, I had SQL generate the queries for each database I wanted to migrate and I pasted them all into notepad so I could run them all at the same time.

After running all of the afforementioned queries to create the all of the database structures on your destination server we're ready to start importing data. Right click the database to import data to. Go to Tasks -> Import Data...

If you haven't done this before, you should receive a "Welcome to the SQL Server Import and Export Wizard" screen. Click Next.

Here we are at the "Choose a Data Source" screen. For Server name type the hostname of the server you need to migrate from (the source server). After that, select the database you want to copy to the new server. Once done with that, click Next.

On the "Choose a Destination" screen, type in the name of the server to migrate the data to. Next, select the Database you want to copy the data to (this should corrospond to the DB name on the source server since we ran the create queries generated by SQL). In my case, I was running the Import and Export Wizard from the server I was importing the data to so SQL server already had the Server name and Database filled out. Click Next.

In my case, I wanted to copy the entire database to the destination server, so for the "Specify Table Copy or Query" screen, I elected to "Copy data from one or more tables or views". Click Next. On the following screen, check all the database tables you want to be copied (or just check the box at the top left for all of them).

Nearing the end the "Save and Run Package" screen comes up. If you don't need to save the package to be run later or again at a later time, just leave Run immediately checked and click Next. Finally we review our settings and what will be copied. If everything looks right, click Finish. Once the transfers have completed, click Close. If any transfers failed or threw a warning, you can click the "Messages" text next to the table that did not succeed to see the log entries about it.

Repeat the aforementioned steps until you have migrated every database you need.

SQL Server 2008 Memory Management

Once again, hello all:

Recently I had a problem with SQL server. I was sifting through the processes in Task Manager a few days ago ordered by memory consumption. At the top of the list for memory consumption was SQL Server (sqlserver.exe) weighing in at 200 megabytes of memory. I decided to look past that one since 200 megabytes isn't too unreasonable for SQL, especially when it's hosting the data for quite a few SharePoint web applications.

Today, I checked again. After my server had been online for two and a half days, SQL server had grown to over 650 megabytes of memory (653,224 KB specifically). Seeing as how I have not made any changes to my local SharePoint environment in that time (I'm currently developing a non-SharePoint related project), I decided to look into putting a cap on the memory consumption of SQL. Originally I had 2 gigabytes of ram for my server. I added an extra gigabyte to that and SQL took up the additional space.

As it turns out, one can put a maximun and a minimum limit on SQL. Here's how.

*NOTE* This is done in SQL Server 2008 so the specified actions may not be available if you are using an older version such as 2003.

- Open up SQL Server Management Studio 2008

- Type in the information to connect to the server that has SQL server running on it and click connect.

- Right click the server name

- Click Properties

- Select Memory on the left side of the window that comes up

- Under Server Memory Options, adjust the minimum and maxiumum memory settings to what you need.

*NOTE* If you are running a 32-bit operating system and need to allocate more than 4 gigabytes of RAM to SQL, be sure to check the "Use AWE to allocate memory" checkbox.

- Click OK

- Right Click the server name again

- Select Stop from the menu

- Click necessary buttons to get through the prompts

- Right Click the server name yet again

- Select Start from the menu

- Click the necessary buttons to get through the prompts

And that's it. Mine (as in the screenshots) has yet to go over 300 megabytes of memory consumption.

Thanks for reading.

Aaron Ball

SQL 2008 Re-install Errors

Hello again all,

Recently, after the server build was 'finished', I discovered that the SQL install was not configured to use the proper authentication method or service accounts (oops) and without mixed mode authentication enabled, windows authentication could not be used to log in to sql to fix these things. That being said, I had to uninstall SQL 2008 (standard edition) and do a reinstall to correct these issues. Time to grab some popcorn and a drink and sit back to watch that entertaining progress bar as it slowly creeps across the tiny 800x600 virtual console window.

I configured the SQL install and ran into an ambiguous error (how typical).

"This access control list is not in canonical form and therefore cannot be modified."

How quaint. Thankfully, after searching for a few minutes with our friend Google, I stumbled upon a Microsoft feedback article that seemed to contain my answer.

Here's what needs to be done.

Navigate in an explorer window to

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log\

The link at the end of this post to the Microsoft feedback article says that from there you open up the "details.txt" file. I found that that folder actually contained 11 folders (from the current install and the previous install) and a file called "summary.txt". I found the right "details.txt" file in the most recently created folder.

Once you've located the right "details.txt" file, open it up in notepad (or your editor of choice) and scroll to the end of the file (it's pretty big so use the scroller bar). Near the end, you should see some text that looks similar to...

2009-05-30 18:02:40 Slp: Sco: Attempting to set directory full path

2009-05-30 18:02:40 Slp: Sco: Attempting to normalize directory path C:\Program Files\Microsoft SQL Server\100\COM\

2009-05-30 18:02:40 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\100\COM\ exists

2009-05-30 18:02:40 Slp: Sco: Attempting to set security descriptor for directory C:\Program Files\Microsoft SQL Server\100\COM\, security descriptor D:(A;OICI;FRFX;;;S-1-5-80-3263513310-3392720605-1798839546-683002060-3227631582)

2009-05-30 18:02:40 Slp: Sco: Attempting to check if directory C:\Program Files\Microsoft SQL Server\100\COM\ exists

2009-05-30 18:02:40 Slp: Sco: Attempting to normalize security descriptor D:(A;OICI;FRFX;;;S-1-5-80-3263513310-3392720605-1798839546-683002060-3227631582)

2009-05-30 18:02:40 Slp: Sco: Attempting to replace account with sid in security descriptor D:(A;OICI;FRFX;;;S-1-5-80-3263513310-3392720605-1798839546-683002060-3227631582)

2009-05-30 18:02:40 Slp: ReplaceAccountWithSidInSddl -- SDDL to be processed: D:(A;OICI;FRFX;;;S-1-5-80-3263513310-3392720605-1798839546-683002060-3227631582)

2009-05-30 18:02:40 Slp: ReplaceAccountWithSidInSddl -- SDDL to be returned: D:(A;OICI;FRFX;;;S-1-5-80-3263513310-3392720605-1798839546-683002060-3227631582)

2009-05-30 18:02:40 Slp: Prompting user if they want to retry this action

The text you're looking for is the directory path listed after the text "Attempting to normalize directory path".

Open up another explorer window and navigate to (not inside) the directory that is specified after the previous quote. Right click the directory (in this case, the directory is COM within the directory 100) and select "Properties". From within the Properties window, click the "Security" tab. Windows should give you an error that says something along the lines of the permissions being out of order and might not be effective (sorry...I forgot to copy that error).

Please note right now that this is done in Windows Server 2008 and the specified buttons/tabs to click won't be the same in Server 2003 from this point.

Click "Edit" near the middle of the window. Windows should then throw up another dialog box saying that the permissions are out of order and need to be fixed. Click "Okay" and Windows will automatically fix the problem for you. Just click "Okay" in the window that comes up. Click "Okay" in the "Security" window to close it out as well.

Go back to your installer now and click "Retry" on the error window.

I had to fix two directories. The guy in the Microsoft feedback article said he had to fix five directories. That being said, this may need to be done more than once.

That about sums this up. The article I found that helped me get started fixing this can be found here:

http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=355216

Aaron Ball