Fixing orphaned database users

How to fix orphaned database users:

ALTER USER Username WITH LOGIN = LoginName

Commonly happens when you backup then restore a database to a different SQL server.  Any users in the database are orphaned even thought a matching login exists on the new server.  The problem is that the SID of the login and the database user does not match.  Alter User ... can fix this.

Reference:
http://sqlsolace.blogspot.com/2011/05/alter-user-with-login-to-fix-orphaned.html

WSUS Console crashes when browsing for updates

To resolve this issue, delete the WSUS application cache from the location below:
For Win 2008 R2 server:
C:\Users\<user profile>\appdata\roaming\microsoft\mmc

where user profile is the currently logged in user profile.

Reference:
http://support.microsoft.com/kb/2761925

Removing server from SSRS 2008 Scale-Out Deployment

While looking the configuration of our SSRS 2008 server, I found that the old SSRS WFE (web-front end) server had been joined to our current SSRS.  I don't know how this happened or why, but wanted to remove it. 

1. Open command line and go the the path C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\ (on a 64-bit SQL 2008 Reporting Services )
2. List the report servers currently in the database type the command:
   
   RSKeyMgmt -l
3. You will see the server listed with their GUID.  Note the GUID of the server that you want to remove.
4. To remove the instance of the Report server type the command below:
   
   RSKeyMgmt -r
   
5. Press Y to remove the server.
6. Type the command:
   
   RSKeyMgmt -l
   
   and verify that the server has been removed.

Reference:
http://milehighdba.blogspot.com/2011/06/remove-server-from-ssrs-scale-out.html
https://stevenrobert.wordpress.com/2011/06/27/remove-server-from-ssrs-2008-scale-out-deployment/

vihostupdate does not work for vSphere 5

Tried to install patches to our ESXi hosts with vihostupdate and got this error...

 "This operation is NOT supported on 5.0.0 platform"

Found answer in the reference article below... need to use esxcli and have the ZIP file uploaded to a location that the hosts can access directly, ie. a vmfs volume.

Reference:
https://communities.vmware.com/people/vmroyale/blog/2011/09/15/updating-esxi-5--single-use-esxcli-how-to

Error Scripting database with only dbo rights

With only dbo permissions to a database, you will get this error when you try to script the database.

"Could not read metadata, possibly due to insufficient access rights. (Microsoft.SqlServer.Smo)"

Try this:

1. From SSMS
2. Right click on the server name and click properties
3. Click on the Permissions tab
4. Click the user name
5. On the Explicit Permissions almost at the bottom there is "View Any Definition" grant that.

Reference:
http://www.sqlservercentral.com/Forums/Topic355850-149-1.aspx

Allowing a DBO to manage SSIS packages (SQL 2008)

We have developers that manage their own application's databases (dbo) and SSIS jobs.  In order to give them "least priv" to SQL 2008, this is what I've been doing to give them rights to manage their database(s) and their SSIS jobs on SQL server.

• Add user admin account to local "Distributed COM Users" group
• Give "Distributed COM Users" group remote access to the DCOM config for SQL; see http://systems808.blogspot.com/2010/07/access-denied-to-ssis-remotely.html
• Give user admin account access to msdb database with the following rights:
• DatabaseMailUserRole
• db_ssisltduser
• public (default)
• SQLAgentOperatorRole
• SQLAgentReaderRole
• SQLAgentUserRole

Hope that helps!


For SQL 2012+, see:
https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-2016

syspolicy_purge_history job failing

Basically, had to fix powershell command line to use the cluster virtual server name for each instance vs the server name...

Reference:
http://support.microsoft.com/kb/955726