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/

 

 

Netbackup jobs failing with 48 status code

To resolve, check the hostname resolution from media server to client and vice versa.

Try below from media to client and client to media:

• Bpclntcmd -hn clientname
• Bpclntcmd -ip client ip
• Bptestbpcd -client clientname

If there is name resolution issue, resolve it.  Ie. put entries in HOSTS file, etc...

If changes have been made recently, remember to clear NBU host cache - even on client:

• bpclntcmd -clear_host_cache
• also clear ipconfig /flushdns

SQL 2008 Cluster installation fixes

Spent all day working on trying get one named instance of SQL 2008 installed on a Win 2008 R2 cluster.

Anyway, these are the things I encountered:

• SQL server 2008 failover cluster 'RequireKerberos' error;  The fix was to slipstream SQL 2008 SP1 into the installation process
• SQL cluster resource would not startup at end of installation; Name resource could not come online.  The fix was to give cluster computer name FULL CONTROL of the virtual instance names in Active Directory using ADUC.
• Verification check prior to installation would fail and not allow me to install SQL.   After manual verification that things should be ok, I used this command "setup /SkipRules=Cluster_VerifyForErrors /Action=InstallFailoverCluster /PCUSource=c:\SP1" to finally start up the installation process.

References:
http://trilist.blogspot.com/2010/02/fix-sql-server-2008-failover-cluster.html