Setting up a Three-Tier SharePoint Farm Including PowerPivot

This post will detail some of the challenges I encountered while setting up a three tier SharePoint 2010 farm that included PowerPivot.  Hopefully these notes will help somebody else.

Our three-tier farm will include:

    1. One server for the SQL Server 2008 R2 relational database that SharePoint will use.  (SQL)
    2. One application server that will run the central administration web site, the web application web site which will house our site collections, and a PowerPivot installation which includes an Analysis Services database running in Vertipaq mode.  –Best practice would be to have a separate server for the PowerPivot installation.  (APP)
    3. One web front end server that will render the html web pages.  (WFE)

These instructions will be based on Microsoft’s documentation, Multiple Servers for a Three-tier farm (SharePoint Server 2010.)  I will point out some of the things that confused me.  I will also share some links to blog posts that helped me overcome my confusion.  The instructions are a little confusing as there are certain hotfixes that are needed if you are running SQL Server 2008 that you do not need if you are running SQL Server 2008 R2.

Pay special attention to the Before you begin section in the documentation.  Especially the hardware and software requirements.


Gotcha #1

My first gotcha occurred with the permissions of the active directory account(s) we were using for the setup administrator and farm account.  When I ran the the SharePoint 2010 products and configuration wizard I received the following error at step 3:

Failed to create the configuration database.
An exception of type System.Collections.Generic.KeyNotFoundException was thrown. Additional exception information: The given key was not present in the dictionary.
System.Collections.Generic.KeyNotFoundException: The given key was not present in the dictionary.
at System.ThrowHelper.ThrowKeyNotFoundException()
at System.Collections.Generic.Dictionary`2.get_Item(TKey key)


Here is an excerpt from the link that led me down the path to the solution:


“I installed 2010 once and got this problem fixed using this URL – which at one time gave me the answer, but no longer does.

This is what fixed my challenges.  I was creating a new environment with new IDs, SPInstallTest, SPFarmTest.  They had all the correct permissions on the SharePoint boxes and the SQL boxes (Server 2008 R2 and SQL 2008 R2); however, every time I tried to configure the farm I got the error mentioned above.  The problem is that the AD attributes for these new IDs do not have the correct security in AD that they need.  (this will bite you with the people picker too with multiple domains).  Go into the AD accounts you created for your farm (Admin and Farm, except I used Install), make sure Advanced Features are selected in AD.  Click on the Security tab of these SharePoint accounts, highlight “Authenticated Users” and give them “read” access.  This is also important for Search to work properly!”


I was able to find the solution in this thread SharePoint 2010 – Error while running Configuration wizard for SP 2010 server farm.

You have to read until the end where it is suggested to go to Active Directory and to edit the properties of the following accounts:

  • Setup administrator (account used to install SharePoint)
  • Farm account (account used in the SharePoint Products Configuration wizard)
  • Look at administrative and service accounts for a description of accounts you need to install SharePoint

In the security tab you need to give the authenticated users group read permission:


After that the install has worked as expected.


Gotcha #2

The first time I ran the SharePoint 2010 Product Preparation Tool on each server (APP and WFE) it failed.  The error message indicated that the .Net framework 3.5 service pack 1 failed.  After wasting about an hour, I realized that 3.5.1 was already installed before I even began.  Running the Product Preparation Tool again on each server showed success.  So in short, if the Product Preparation Tool fails the first time, just run it again.


Gotcha #3

Documentation tells you to install several hotfixes that wouldn’t install.  From the documentation:

After you install the prerequisites on each farm server, you will need to install Error message when you use the “IMSAdminBase::CopyKey” method as a part of the IIS 6.0 Compatibility components in IIS 7.0: “Exception from HRESULT: 0x80070003” on the application server first, and then on the two Web servers.

This update seems to only apply to Vista.


After you complete the Microsoft SharePoint Products Preparation Tool, you must install KB 949516 ( and KB 971831 ( You might also need to restart the server after installing this hotfix.

These also wouldn’t install.


My recommendation is to try and install these anyway as your experience may vary.  This installers for these hotfixes are apparently smart enough to know when they don’t apply.




Gotcha #4


Should you choose Windows Classic authentication or Windows Claims authentication for your first web application?  I initially chose Windows Claims authentication because it included Windows Classic plus two other authentication methods.  I figured this would give me the most flexibility.  We could start w/ NTLM and if we ever needed to switch to Kerberos we could.  Big mistake when it came to the PowerPivot installation.  I spent many hours trying to figure out what was wrong when I noticed this quote from a MSDN article:

When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.  

For existing web applications, use the following instructions to verify the applications are configured to use Windows authentication. When creating new web applications, be sure to select the Classic mode authentication option in the Create New Web Application page.

1. In Central Administration, in Application Management, click Manage web applications.

2. Select the web application.

3. Click Authentication Providers.

4. Verify that you have one provider for each zone, and the Default zone is set to Windows.

Ref: Plan PowerPivot Authentication and Authorization


Another thing I messed up was kind of silly looking back on it now.

In the instructions for creating your web application using Classic Mode, item #10, the public URL section I replaced the APP server with the WFE server.  Strangely enough, this worked fine for everything in PerformancePoint.  PowerPivot not so much.


Here is the excerpt from #10:


In the Public URL section, type the URL for the domain name for all sites that users will access in this Web application. This URL will be used as the base URL in links shown on pages within the Web application. The default URL is the current server name and port, and is automatically updated to reflect the current SSL, host header, and port number settings on the page. If you are deploying SharePoint Server 2010 behind a load balancer or proxy server, then this URL may need to be different than the SSL, host header, and port settings on this page.

The Zone value is automatically set to Default for a new Web application.


You can change the zone when you extend a Web application. For more information, see Extend a Web application (SharePoint Server 2010).


You want to leave the default application server in the public URL section.  Later on, once you have PowerPivot installed and working, extend your web application in the first link directly above.



Create a Web application that uses Windows-classic authentication (SharePoint Server 2010)



Gotcha #5

Don’t enable anonymous access when creating the web application!  Excel services won’t work and the documentation doesn’t tell you that!  From the doc:


If you want users to be able to access any site content anonymously, you must enable anonymous access for the entire Web application zone before you enable anonymous access at the SharePoint site level; later, site owners can configure how anonymous access is used within their sites. If you do not enable anonymous access at the Web application level, you cannot enable anonymous access later, at the site level. For more information, see Choose security groups (SharePoint Server 2010).

After reading that, I thought I should enable it in case we ever needed it.  I was sorely mistaken.  Don’t do it.


I was trying to create a new excel services data connection. Item name drop down wouldn’t populate and test connection wouldn’t enable. Came across this:


On this same point… you don’t need to create an Excel Services data connection within Dashboard Designer to test Excel Services.  Create an Excel Services report instead.



Gotcha #6


The PowerPivot installation documentation stinks!  (PowerPivot for sharepoint existing farm installation)  It obviously hasn’t been kept up to date.

For example, #12 Create the PowerPivotService appilcation states:


b. Click Application Management, Manage Service Applications, New, and SQL Server Analysis Services Mid-Tier Service.

Well, that’s great; but, it doesn’t exist…. I used SQL server PowerPivot service application instead.


That’s just one of many examples.  A better source for how to install PowerPivot for an existing farm can be found here:  How to: Install PowerPivot for SharePoint in a Three-tier SharePoint.




Other tips…


When you are setting up Excel Services go ahead and uncheck the warn on refresh.  While you’re at it, increase the maximum workbook size to something more reasonable like 2000MB and the max size for charts to 100MB.  You’ll also want to configure the maximum file size for a SharePoint web application.  See this link:

Configure Maximum File Upload Size (PowerPivot for SharePoint)



Related Links

SharePoint 2010: Missing Server Side Dependencies

Configure SharePoint Server 2010 in a Three-Tier Farm

Creating a PowerPivot Gallery

SQL Server 2008 R2 and SharePoint 2010 Products: Better Together (white paper) (SharePoint Server 2010)

SharePoint 2010 Feature Gallery

PerformancePoint Demo – Click Demonstration.  To get the right click functionality you have to click the compatibility icon in Internet Exploder.

PowerPivot Demo

Please let me know in the comments if you have anything to add or if I have stated anything that is incorrect.


SQL Server Management Studio (SSMS) Tips and Tricks

1.     Change the status bar in the “New Query” window to red when connected to production or green when connected to development.

I recently uninstalled the SSMS Tools Pack ( because it was crashing whenever I connected to an Analysis Services server.  One feature I missed was the ability to change the query window to red when connected to production and green when connected to Development.  I discovered this feature is built in for SQL Server 2008!

Open SSMS and in the “Connect to Server” dialog click the “Options” button.  Select the “Connection Properties” tab and check the box next to “Use custom color”.  Select your desired color and that’s it!  While you’re in there it might be a good idea to set your default database to something other than “Master.”


2.     Install a Tool to Automatically Format your T-SQL Code

I’m cheap.  I use Poor Man’s T-SQL Formatter.

3.     Set up some Keyboard Shortcuts

In SSMS click Tools, and then Options.  Select Keyboard from the Environment tree and set up the things you are like to type very often.  These shortcuts execute as soon as you press the hotkey.  This makes their usefulness somewhat limited in my opinion.  A good one to add is CTRL + F1 as sp_helptext.  This allows you to highlight a stored procedure, function, or view and quickly look at it’s code.


Highlight an entity within the Query window and press ALT+F1 to execute sp_help on the object. This will give you a breakdown of any columns, indexes, parameters, etc.


Note: If you need to specify an object in a different schema, you have to use the two part name “schema.Object” and put it in single quotes.  For example, ‘dbo.MyTable’.

I use a program called called Clavier + to assign keyboard shortcuts to things I want typed but not executed right away.


These keyboard shortcuts are built-in to Management Studio:

Keyboard Shortcuts

  • F5, CTRL + E or ALT + X – execute currently selected TSQL code
  • CTRL + R – show/hide Results Pane
  • CTRL + N – Open New Query Window
  • CTRL + L – Display query execution plan
  • CTRL + SHIFT + Q – Display the query designer (noob.)
  • CTRL + I – Incremental search.  After pressing Ctrl=I, look at the bottom left of your SSMS window.  Here you type what you want to search for.  Press F3 to cycle.

Editing Shortcuts

  • CTRL + K + C and CTRL + K + U – comment/uncomment selected block of code
  • CTRL + SHIFT + U and CTRL + SHIFT + L – changes selected text to UPPER/lower case
  • ALT + Select text with mouse and drag – select/cut/copy/paste a rectangular block of text

Another big typing saver is to right click a table within object explorer and selecting “Script Table As” and choosing one of the many options.


3.    Modify Startup Options

In SSMS click Tools, and then Options.  Select Environment in the navigation tree.  Here I like to change the first setting to always “Always Open Object Explorer and new query.”  I also crank up the recently used files setting to the max and hide the system objects in Object Explorer.  If you have never tried the MDI environment, you might like it better than the tabbed documents. This screen is also where you change that setting.


I also like to disable the splash screen.  This cuts the time it takes SQL Server Management Studio to load in half.  Right click your shortcut to SSMS and select properties.  Enter the text “-nosplash” (without the quotes) right after the ending quote in the path.


Finally, it is useful to create a solution of commonly used SQL scripts to always load at start-up.  To do this, first display the Solution Explorer by pressing Control+Alt+L or clicking View, Solution Explorer.  Now, right click the “Solution ‘Solution1’ (0 projects)” text and select Add, New Project.


Use the default “SQL Server Scripts” template and give your solution a clever name.


Rename all of your SQL Code Snippets so the extension is .SQL.  Drag them into the queries folder within the Solution Explorer.


Click File Save All and save your solution.  Note the location.  Next, open Windows explorer and browse to the location of your solution.  Copy file location address to your clipboard.  Go back to your SSMS shortcut properties and add within double quotes the location and file name of your solution before the “–nosplash”.

This is the complete text within my shortcut properties:

“C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe” “C:\Users\mmilligan1\Documents\SQL Server Management Studio\Projects\MySQLServerScripts.ssmssln” –nosplash

4.     Refresh IntelliSense

IntelliSense saves time by helping you type table, database, and column names.  First, make sure it is enabled.  Tools, Options.  Text Editor, Transact-SQL, IntelliSense.


If you create a new table and IntelliSense just doesn’t seem to be working then press control+shift+R or click Edit, IntelliSense, Refresh Local Cache.


5.    Drag the Word “Columns” to the Query Window to Type all Column Names Quickly


6.     Implicit Transactions by Default

If you can never remember to start a transaction before your deletes you can go into options and turn IMPLICIT TRANSACTIONS ON by default.


7.     Isolation Level

Again with the options…  set the isolation level to READ_UNCOMMITED by default.  Now you don’t need to type WITH (NOLOCK) in all of your ad-hoc queries!


8.     Object Explorer Details

Use Object Explorer Details to quickly delete many objects at once.  Also you can use it to view your tables as you can press the first letter of the table name and it jumps to that table.

9.     Template Explorer

Become familiar with the items in the template explorer.  Bring it up by pressing CTRL + ALT + T or clicking View, Template Explorer.  These act as a quick reference for getting the proper syntax for many common functions.


10.     Display Line Numbers

Click Tools, Options.  Expand Text Editor and select Transact-SQL.  Check the box next to Line numbers.


11.     Filter your Objects in Object Explorer

Right click the Tables folder (or any folder for that matter) and select Filter, Filter Settings.  Now you can simplify your view of the objects in the Object Explorer by showing only the objects you need to see.


12.     Automagically include column headers when copying and pasting results

Tools, Options, Query Results, SQL Server, Results to Grid.  Check the box to always include column headers when copying or saving results.  You may also like to try having it always display results in a separate tab and to switch to the results tab after the query executes.


13.     Do NOT Prevent Saving Changes that Require Table Re-Creation

I uncheck the “Prevent saving changes that require table re-creation” option.  Tools, Options.  Designers, Table and Database Designers.


That’s all for now.  I’ll add more as I think of them or come across new tips.  Please leave a comment if you have a useful tip you’d like to share.

Related Links

DAT315 – Session Review for SSMS Tips & Tricks Session

SQL Server Management Studio Tips

Business Intelligence Music Mix!

First EVER Business Intelligence Music Mix! SQL, OLAP, Data Mining, Computers, Geek Music. It doesn’t get any nerdier than this.  89 Tracks!  Includes great hits like:

Sequel – SQL
Data Mining – depth.charge
Binary – clammyhands
Byte – knightsofficial
bit – BIT 8
Sad – Programmer
President Kimball – Caesar
A French Winter – Database
DMX! – SameOIG
Press My Start Button, Please – TimothyPatrickBird
Software Check – nottall
Code Monkey – Jonathan Coulton
Bytes in Motion – DesertcoastMediaGroup
Microsoft Vista “Cuzco” – Steven Ray Allen
Microsoft Sam – Jesse Tippit
Aggregation – wgramer
Estranged Apartmeant “Another Dimension (Integration)”
Slice & dics – Smokescreen dubstep
Different measure – d16group
Attribute – Philter
DSV – Mazz+X
Disaster Recover – Abnomally Sound Group
Jericho – DBA

… and many, many more!


Data Mining Presentation and Links

I presented at Jax Code Camp 2012 today so I posted these links on Data Mining and embedded a video of the presentation here.


What a great time! I really enjoyed Brian and Sherri McDonald’s session on SQL Server Reporting Services. I learned a few things that I did not know before and was very entertained. Brian and Sherri are incredible speakers. I work with Sherri now and I worked with Brian and Sherri at Pragmatic Works. Brian just published a book on SQL Server 2012 Reporting Services which I just purchased. I hope to get it signed soon.

I also bought the Star Schema book by Christopher Adamson (First 2 chapters are free in the link!) I liked his book Data Warehouse Design Solutions although I never got a chance to finish it before I had to return it. (Christmas gift idea….)


I’ve already received an email requesting the slide deck! Click here to download the slide deck and speaker cheat sheet. I was amazed to find that the Android store didn’t have an app for a speaker flash card program. If I were an application developer I think I would get started on that quickly


Even better…

Here is the embedded video of the presentation. You can see the screen and hear me and the audience. Count yourself lucky you don’t get to watch my feeble attempt at sign language while speaking. (When I don’t know the right hand signal I tend to just throw my hands up in the air or go through my shadow figures catalog really fast…)

SQL Server 2012 Data Mining Add-ins for Office 2010 Presentation from Mike Milligan on Vimeo.

This is the presentation I did during the Jax Code Camp 2012 in Jacksonville Florida.
Data Mining Links

Microsoft® SQL Server® 2012 Data Mining Add-ins for Microsoft® Office® 2010

Microsoft Data Mining Algorithms

FRED – Federal Reserve Economic Data

Pragmatic Works Past Webinars (Search for Data Mining)

MarkTab.Net –

Creating and Querying Data Mining Models with DMX: Tutorials (Analysis Services – Data Mining)

Data Mining Forum

Intermediate Data Mining Tutorial (Analysis Services – Data Mining)

SQL Server Data Mining

SQL Server Analysis Services (SSAS) Data Mining

and many many more! <– this one is dynamic! This one points to MY LIVE Data Mining folder on X-Marks. As I add new links in the FUTURE they will appear hear. How cool is that?

How to Migrate an Instance of SQL Server 2008 to a New Server

The following is a plan I recently used when we needed to migrate an instance of SQL Server 2008 to another server.  I am posting it on the internet to help anyone else in a similar situation. 

Our migration went pretty well all things considered.  I forgot to include a step to set up the services to use domain level service accounts so we had to scramble a bit to track down those passwords.  That was minor compared to the other issue…

An even bigger gotcha occurred when we attempted to throw a huge load at the new server.  Once the machine went above 50% CPU everything freaked out.  We couldn’t even access the database through SSMS and were forced to reboot.  The event log had the following error:  “There is insufficient system memory in resource pool ‘default’ to run this query.”

It was a real nail-biter and we even discussed the possibility of a rollback.  One of the guys on our team did some Googling and thought the problem might be that SQL Server 2008 only supports up to 64 logical processors and we had 80 on the new server.  We had two choices at that point: upgrade to R2, which does support more processors, or turn off hyper-threading to reduce our logical processor count.  We chose to disable hyper-threading because it introduced the least amount of risk and that seemed to do the trick. 

In hindsight, we should have tried to throw a big load at the server during testing; but, we are a small team with loads of responsibilities.  All things considered I thought we did a great job with the resources we had.  We met our objective with-in the required time period.




A more powerful server has been acquired and we want to move a production instance of SQL Server 2008 to the new server.  In our environment, the SSRS, SSIS, and SSAS instances are on separate servers.  Some of our databases are used by a public internet website.  We intend to copy the instance to the new server, rename the old server, and then rename the new server to what the old server had previously been named. 

Technical Considerations

1.       Is Encryption being used on any of the databases?  First, ask everyone.  Second, check for yourself to be sure.  If it is being used you will probably need to transfer the keys.

a.       Three Types

                                                               i.      Cell-level encryption

I believe this query will return more than one row if any of your databases are using cell-level encryption:

SELECT  * FROM    sys.symmetric_keys

                                                             ii.      TDE (Transparent Data Encryption)

If this query doesn’t return any results you are probably not using TDE:

SELECT db_name(database_id), encryption_state FROM sys.dm_database_encryption_keys

                                                            iii.      Windows File Level encryption

1.       Click Start, Run, and type SECPOL.MSC

2.       Expand Public Key Policies

3.       Click on Encrypting File System

4.       If it says “No Encrypting File System Policies Defined” than the instance is not using an Encrypting File System (EFS.)

2.       Is SQL Server instance on source server at the same patch level as the target?  The version number is displayed next to the instance in Management Studio.

3.       In our case, we intend to backup, copy, and restore one database, and then detach, copy, and attach the other databases.  The advantage of the detach/copy/attach method over the backup/restore method is that it will be faster to copy large files across the network than to wait for the backup/restore operations to complete.

4.       We have decided NOT to attempt to migrate any of the system databases (master, model, msdb.)

5.       We will NOT be migrating maintenance plans and intend to recreate them on the new server.

6.       Is Database Mail enabled on the old server?  If so, you will want to enable it on the new server and set it up the same way.

7.       Two SQL Agent Jobs need to exist on the new server.  One will be created as part of the creation of the maintenance plan; the other is the syspolicy_purge_history job.

syspolicy_purge_history  What is it?

8.       The new server should have the same number of hard drives mapped to the same letters as the old server.  The databases and log files will reside in the same locations on the new server as they were on the old server.

9.       Are there any shared drives that need to be recreated on the new server?

10.   Are there any proxy accounts that need to be recreated on the new server?

11.   You should enable the “Lock Pages in Memory” security right in Windows to your SQL Server Service account if you are on Enterprise edition.

Enable the Lock Pages in Memory Option (Windows)

12.   Set the max memory size for SQL Server.

Suggested Max Memory Settings for SQL Server 2005/2008


1.       Test detach/copy/attach method on Development server

a.       Can development website access the databases as expected?

b.      Can the ETL processes push data into the server?

c.       Can the SSRS Server read data from the server?

d.      Can the SSAS Server read data from the server?

e.       Simulate a heavy workload

2.       Test the backup/copy/restore method on the Development server

a.       Can the ETL processes push data into the server?


1.       Script resource pools from old server and recreate on new server.

a.       Script resource pools from old server.  SSMS, Management, Resource Governor

b.      Execute script on new server

c.       Setup resource governor using function

2.       Script SQL Agent operators from old server and recreate on new server.

a.       Script SQL Agent operators from old server.  SSMS, SQL Server Agent, Operators

b.      Execute script on new server

3.       Document maintenance plans on old server

a.       SSMS, Management, Maintenance Plans

b.      These are actually SSIS packages on the server.  You could export the packages and script out the SQL agent jobs; but, they wouldn’t appear under the Maintenance Plans.

4.       Document domain level account user ids and passwords that are used to run the SQL Server Services.  (Administrative Tools, Services. SQL Server & SQL Server Agent.  Log On As.)


1.       Script logins from old server

                                 i.      How to transfer logins and passwords between instances of SQL Server

                               ii.      Follow the instructions from the link above to create a script from the old server.  Take the resulting script and save a copy.  Then either:

1.       change everyone’s default database to MASTER (change it back to what is was after all databases have been re-attached or restored using SSMS)

2.       OR separate the logins by the default database.  You only want to create the logins after the database exists otherwise the creation will fail.

                              iii.      Script Server Permissions and Role Assignments from old server and execute on new server.

2.       Disable SQL Agent jobs on old server and the ETL Server

3.       Disable external processes that access the old server

4.       Backup the single database in which we are using the backup/copy/restore method.

5.       Restore the single database in which we are using the backup/copy/restore method.

6.       Verify restored database is accessible.

7.       Disable website access.

8.       Detach all databases from the old server (except system databases.)

9.       Copy detached databases and log files from the old server to the new server using the same file paths.

10.   Attach all databases on the new server.

11.   Logins and permissions

a.       Execute Logins script

b.      Execute roles and permissions script

12.   Rename old server

13.   Rename new server to same name as old server

14.   Set up the domain level services accounts using the user-ids and passwords that were documented (administrative tools, services.  SQL Server & SQL Server Agent.  Log On As.)

15.   Verify that everything is working properly. 

a.       Website

b.      ETL processes

c.       External processes

d.      SSRS

e.       Heavy workload

16.   Checkpoint:  If everything is not working properly decide if there is time to diagnose issue otherwise, begin rollback procedures.

17.   Enable SQL Agent on SSIS server

18.   Enable external processes

19.   Create Maintenance Plans on new server

20.   Run any jobs missed during migration.


More Information

How can I copy a SQL Server 2008 instance to another server? *Missing step for roles and permissions*

Copy Database from Instance to another Instance – Copy Paste in SQL Server

Moving SQL Server 2008 R2 to another Machine

Moving System Databases *Not Recommended *

Moving User Databases

Move SQL Server Instances from one data center to another data center

Maximum Number of Processors Supported by the Editions of SQL Server

How many logical processors does SQL Server 2008 R2 Enterprise Edition support?



How to: Migrate a Reporting Services Installation

How to Transfer (Copy) Maintenance Plans from One Server to other

How to move SSAS from one server to another

Automate Database Restore to Remote Instance with SSIS

Automatically Transfer and Synchronize SQL Server logins, SIDs, Passwords and Permissions



Query to find instance patch level:



Query to list database files and their paths:

select name,physical_name fromsys.master_filesorderby 1


Hardware information from SQL Server 2008 and 2008 R2:

— (Cannot distinguish between HT and multi-core)


       cpu_count AS [Logical CPU Count]

      ,hyperthread_ratio AS [Hyperthread Ratio]

      ,cpu_count / hyperthread_ratio AS [Physical CPU Count]

      ,physical_memory_in_bytes / 1048576 AS [Physical Memory (MB)]




Scripting Server Permissions and Role Assignments

/* Generate statements to create server permissions for SQL logins, Windows Logins, and Groups */

SELECT 'USE' + SPACE(1) + QUOTENAME('MASTER') AS '--Database Context'

-- Role Members
SELECT 'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
+ QUOTENAME(, '''') + ', @loginame =' + SPACE(1)
+ QUOTENAME(, '''') AS '--Role Memberships'
FROM sys.server_principals AS usr1
INNER JOIN sys.server_role_members AS rm ON usr1.principal_id = rm.role_principal_id
INNER JOIN sys.server_principals AS usr2 ON rm.member_principal_id = usr2.principal_id
ORDER BY rm.role_principal_id ASC

-- Permissions
SELECT server_permissions.state_desc COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' ' + server_permissions.permission_name COLLATE SQL_Latin1_General_CP1_CI_AS
+ ' TO [' + COLLATE SQL_Latin1_General_CP1_CI_AS
+ ']' AS '--Server Level Permissions'
FROM sys.server_permissions AS server_permissions WITH ( NOLOCK )
INNER JOIN sys.server_principals AS server_principals WITH ( NOLOCK ) ON server_permissions.grantee_principal_id = server_principals.principal_id
WHERE server_principals.type IN ( 'S', 'U', 'G' )