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

Reverse lookup of AD SID

Netapp's data ONTAP OS has this nice command...

cifs lookup textual_sid_S-x-y-z

The cifs lookup command translates a Windows NT user or group name into its corresponding textual Windows NT SID (Security ID), or a textual NT SID into its corresponding Windows NT user or group name.
domain\name is the name of an account in a specified Windows domain. If the domain is omitted, then the name is looked up in the domain in which the Filer is a member server. Conversely, given a Windows Security ID (SID), cifs lookup will return the corresponding account name.

Reference:
http://www.wafl.co.uk/cifs_lookup/

The EXECUTE permission was denied on the object ‘xp_prop_oledb_provider’, database ‘mssqlsystemresource’

How to Fix this?

1.Map the User to MASTER Database( By Def. It will be added to Public Role)

2.USE MASTER

GO

GRANT EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER TO LOGIN

 

Reference:

http://mssqltrek.com/2011/12/05/the-execute-permission-was-denied-on-the-object-xp_prop_oledb_provider-database-mssqlsystemresource/