SQL 2008 R2 – SQLMint

T-SQL: How to export image field data to file(s)?

See below code:

SQL Server Fixed Server Role Permissions

SQL Server Fixed Server Role Permissions – run – sp_srvrolepermission

ServerRole Permission

bulkadmin Add member to bulkadmin

bulkadmin BULK INSERT

dbcreator Add member to dbcreator

dbcreator ALTER DATABASE

dbcreator CREATE DATABASE

dbcreator DROP DATABASE

dbcreator Extend database

dbcreator RESTORE DATABASE

dbcreator RESTORE LOG

dbcreator sp_renamedb

diskadmin Add member to diskadmin

diskadmin DISK INIT

diskadmin sp_addumpdevice

diskadmin sp_diskdefault

diskadmin sp_dropdevice

processadmin Add member to processadmin

processadmin KILL

securityadmin Add member to securityadmin

securityadmin Grant/deny/revoke CREATE DATABASE

securityadmin Read the error log

securityadmin sp_addlinkedsrvlogin

securityadmin sp_addlogin

securityadmin sp_defaultdb

securityadmin sp_defaultlanguage

securityadmin sp_denylogin

securityadmin sp_droplinkedsrvlogin

securityadmin sp_droplogin

securityadmin sp_dropremotelogin

securityadmin sp_grantlogin

securityadmin sp_helplogins

securityadmin sp_password

securityadmin sp_remoteoption (update)

securityadmin sp_revokelogin

serveradmin Add member to serveradmin

serveradmin dbcc freeproccache

serveradmin RECONFIGURE

serveradmin SHUTDOWN

serveradmin sp_configure

serveradmin sp_fulltext_service

serveradmin sp_tableoption

setupadmin Add member to setupadmin

setupadmin Add/drop/configure linked servers

setupadmin Mark a stored procedure as startup

sysadmin Add extended procedures

sysadmin Add member to bulkadmin

sysadmin Add member to dbcreator

sysadmin Add member to diskadmin

sysadmin Add member to processadmin

sysadmin Add member to securityadmin

sysadmin Add member to serveradmin

sysadmin Add member to setupadmin

sysadmin Add member to sysadmin

sysadmin Add/drop to/from db_accessadmin

sysadmin Add/drop to/from db_backupoperator

sysadmin Add/drop to/from db_datareader

sysadmin Add/drop to/from db_datawriter

sysadmin Add/drop to/from db_ddladmin

sysadmin Add/drop to/from db_denydatareader

sysadmin Add/drop to/from db_denydatawriter

sysadmin Add/drop to/from db_owner

sysadmin Add/drop to/from db_securityadmin

sysadmin Add/drop/configure linked servers

sysadmin All DDL but GRANT, REVOKE, DENY

sysadmin ALTER DATABASE

sysadmin BACKUP DATABASE

sysadmin BACKUP LOG

sysadmin BULK INSERT

sysadmin CHECKPOINT

sysadmin Complete SETUSER SQL user

sysadmin Constraints on System tables

sysadmin CREATE DATABASE

sysadmin Create indices on system tables

sysadmin Create/delete/modify system tables

sysadmin dbcc change ‘on’ rules

sysadmin dbcc checkalloc

sysadmin dbcc checkdb

sysadmin dbcc checkfilegroup

sysadmin dbcc checkident

sysadmin dbcc checktable

sysadmin dbcc cleantable

sysadmin dbcc dbreindex

sysadmin dbcc dropcleanbuffers

sysadmin dbcc freeproccache

sysadmin dbcc inputbuffer

sysadmin dbcc outputbuffer

sysadmin dbcc pintable

sysadmin dbcc proccache

sysadmin dbcc setcpuweight

sysadmin dbcc setioweight

sysadmin dbcc show_statistics

sysadmin dbcc showcontig

sysadmin dbcc showoptweights

sysadmin DBCC ShrinkDatabase

sysadmin dbcc shrinkfile

sysadmin dbcc traceon/off

sysadmin dbcc updateusage

sysadmin DELETE permission on any object

sysadmin DENY

sysadmin DISK INIT

sysadmin DROP DATABASE

sysadmin EXECUTE any procedure

sysadmin Extend database

sysadmin GRANT

sysadmin Grant/deny/revoke CREATE DATABASE

sysadmin INSERT permission on any object

sysadmin KILL

sysadmin Mark a stored procedure as startup

sysadmin Raiserror With Log

sysadmin Read the error log

sysadmin RECONFIGURE

sysadmin REFERENCES permission on any table

sysadmin RESTORE DATABASE

sysadmin RESTORE LOG

sysadmin REVOKE

sysadmin SELECT permission on any object

sysadmin SHUTDOWN

sysadmin sp_addapprole

sysadmin sp_addlinkedsrvlogin

sysadmin sp_addlogin

sysadmin sp_addrole

sysadmin sp_addrolemember

sysadmin sp_addumpdevice

sysadmin sp_adduser

sysadmin sp_altermessage

sysadmin sp_approlepassword

sysadmin sp_change_users_login

sysadmin sp_changedbowner

sysadmin sp_changeobjectowner

sysadmin sp_configure

sysadmin sp_dbcmptlevel

sysadmin sp_defaultdb

sysadmin sp_defaultlanguage

sysadmin sp_denylogin

sysadmin sp_diskdefault

sysadmin sp_dropapprole

sysadmin sp_dropdevice

sysadmin sp_droplinkedsrvlogin

sysadmin sp_droplogin

sysadmin sp_dropremotelogin

sysadmin sp_droprole

sysadmin sp_droprolemember

sysadmin sp_dropuser

sysadmin sp_fulltext_catalog

sysadmin sp_fulltext_column

sysadmin sp_fulltext_database

sysadmin sp_fulltext_service

sysadmin sp_fulltext_table

sysadmin sp_grantdbaccess

sysadmin sp_grantlogin

sysadmin sp_helplogins

sysadmin sp_password

sysadmin sp_recompile

sysadmin sp_refreshview

sysadmin sp_remoteoption

sysadmin sp_remoteoption (update)

sysadmin sp_rename

sysadmin sp_renamedb

sysadmin sp_revokedbaccess

sysadmin sp_revokelogin

sysadmin sp_tableoption

sysadmin sp_updatestats

sysadmin TRUNCATE TABLE

sysadmin UPDATE permission on any object

sysadmin USE to a suspect database

Transferring logins and passwords between separate SQL server Instances

Transferring logins and passwords between separate SQL Server instances eg SVR1\DEV to SVR1\DEV2 can be done by scripting out the encrypted passwords using the stored procedures sp_hexadecimal and sp_help_revlogin which can be found by following this detailed link by clicking here.

SQL Server 2014 Upgrade Advisor

Running the SQL Server 2014 upgrade advisor is a good idea when say planning an in-place upgrade from a previous version of SQL Server.

To do this, run setup from the installation disc, go to planning, run install upgrade advisor.

You might get a pop up such as below, this is resolved by downloading and installing the SQL 2014 feature pack components from this link. Click here.

Resolving SQL Server Agent Job Issue – System.InvalidOperationException: Unable to generate a temporary class (result=1)

Below is the final error message l kept getting when setting up a SQL Server Agent Job which ran an SSIS package. The SSIS package is designed to create a folder in a network share as well as copy some files into that folder.

Started: 11:03:22 AM
Error: 2014-10-07 11:03:23.35
Code: 0x00000001
Source: Create Reports
Description: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.InvalidOperationException: Unable to generate a temporary class (result=1).
error CS2001: Source file ‘C:WindowsTEMPp6e47evu.0.cs’ could not be found
error CS2008: No inputs specified

at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns, XmlSerializerCompilerParameters xmlParameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, Evidence evidence, XmlSerializerCompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings, Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.GetSerializersFromCache(XmlMapping[] mappings, Type type)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[] mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at ReportExecutionService..ctor()
at ST_960eb2bb9aa341f4a31d1409aa0aadbb.csproj.ScriptMain.Main()
— End of inner exception stack trace —
at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started: 11:03:22 AM
Finished: 11:03:23 AM
Elapsed: 0.546 seconds

Resolution:

Create a credential on the security level of the sql server instance
USE [master]
GO

CREATE CREDENTIAL [CredOne] WITH IDENTITY = N’DomainCredOneUser’, SECRET = N’Password’

GO
Create a proxy on the job module of the sql server instance
USE [msdb]
GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N’CredOne’,@credential_name=N’CredOne’,

@enabled=1,

@description=N’My description’

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=2

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=3

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=10

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=9

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=11

GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’CredOne’, @subsystem_id=12

GO
Ensure the network share folder has full control permission given to the user domainuser
Ensure modify permission is given to that same domain account to the folder c:windowstemp on the server box
The SQL Job should now run without issues

Restore to SQL 2005 from SQL 2008

You cant restore a database backwards from SQL Server 2008 to a SQL 2005 Instance.

One trick you can use is to script the entire database, and ensure you select in the advanced options as shown below, SQL 2005.

Then run the script at the destination SQL 2005 instance.

Remember to change the database file paths in the output SQL script as needed.