Non-sa access to SQL 2000 Jobs

Add users to role TargetServersRole in MSDB database for SQL 2000 SP3+.

Reference:
http://www.sql-server-performance.com/2006/sqlagent-scheduled-jobs/

Change DTS Owner (SQL 2000)

Good tip found below... helpful during migration from SQL 2000 to SQL 2008 to allow developers to see DTS owned by other programmers.

Reference:
http://vittoriop77.blogspot.com/2006/01/sql-2000-change-dts-owner.html

P2V SQL Cluster nodes

Using P2V to convert a cluster nodes to virtual server instances.

Reference: http://www.mssqltips.com/tip.asp?tip=2355

Upgrading to SQL 2008

Issue: "Rule "Security Group SID (Security Identifier)" failed." from Upgrade Rules check.

Found solution at referenced link, post by nctmyers...

Basically, you need to make sure the group SID of SQLServer... related local groups match with is in the registry. ie.

SQLServer2005MSFTEUser$ServerName$MSSQLSERVER
SQLServer2005MSSQLUser$ServerName$MSSQLSERVER

Download the free PSTools utilities and use "psgetsid.exe" to display the SIDs for the SQL user groups.
http://technet.microsoft.com/en-us/sysinternals/bb897417.aspx

psgetsid

Then use "regedit" and go to [HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup] to see the SIDs for each group...

For example...
AGTGroup="S-1-5-21-2065109431-430523663-1844936127-1037"
FTSGroup="S-1-5-21-2065109431-430523663-1844936127-1039"
SQLGroup="S-1-5-21-2065109431-430523663-1844936127-1038"
ASGroup="S-1-5-21-2065109431-430523663-1844936127-1040"

Replace any SIDs that do not match the local group SIDs found with psgetsid and re-run on the SQL 2008 Upgrade Rules page.

Reference: http://social.msdn.microsoft.com/forums/en-US/sqlsetupandupgrade/thread/0ca885d2-cd8d-4815-a258-d2962d477c35

Microsoft SQL Build versions

Build versions of various MS SQL versions.

Reference: http://sqlserverbuilds.blogspot.com/

SQL 2000 DTS Security

Some excerpts from link below:

• If you want to alter this default behavior and restrict the ability to create new or modify existing packages, you can modify permissions on the sp_add_dtspackage, sp_enum_dtspackages, and sp_get_dtspackage stored procedures in the MSDB database. The first one contains T-SQL code for creating DTS packages, the second one provides ability to list them and the third one is used for retrieving them. By removing permissions to run them from the public role, you can restrict these activities to specific fixed or custom database roles.
• Another possibility to secure packages saved to SQL Server or a Structured Storage file (but not to Meta Data Services or a Visual Basic file) is by assigning owner and user passwords. This is done from the DTS Designer interface, by selecting the Save As... item from the Package top level menu. In the resulting "Save DTS" Package dialog box, you can type in the owner and user password (note that you cannot set a user password without defining the owner password). An owner password secures opening and modifying the content of a package; knowledge of the user password allows its execution (you are prompted for them whenever you try to execute or open the package to which they were assigned). Keep in mind that there is no direct way to change these passwords. The only way to accomplish this is to save the package using a different name, then delete original package, and finally rename back the new one (this is clearly much more complicated than it should be).
• Each package has an owner, which is the login that created it (or saved an existing one to a new server - when transferring packages between computers). Identifying information is recorded in the syspackages table of the MSDB databases in the owner and owner_sid columns. Only package owners (and members of Sysadmin fixed server role) have the ability to alter packages and save them with the same name (they are still, however, required to provide passwords in order to open or execute their packages).

Reference: http://www.databasejournal.com/features/mssql/article.php/3404791/SQL-Server-2000-Security---Part-11---DTS-Security.htm

Netbackup Restore Jobs

I was trying to do a SQL database restore job and in the Netbackup (NBU) Admin Console, the job showed that it successfully completed. However, on the SQL agent client console the job status showed that it failed.

The solution was that the MAXTRANSFERSIZE had one value "0" in the backup script and a different value "6" in the restore move script. Once I changed the "6" to "0", the job worked.

For more info on this, see Netbackup SQL Administrator's Guide at http://support.veritas.com/docs/290212

Reference: http://seer.entsupport.symantec.com/docs/311524.htm

Performance tuning SQL 2000/2005 on Windows 2003

Maximize Data Throughput for Network Applications

If the Maximize data throughput for network applications network connection option is selected, the operating system gives priority to applications that perform buffered I/O operations by caching their I/O pages in file system cache. This option may limit memory available to SQL Server for normal operation. Use "Minimize memory used".

Reference: http://technet.microsoft.com/en-us/library/ms178067(SQL.90).aspx

Proper SQL server settings

Do not use priority boost for clustered servers that are running SQL Server 7.0, SQL Server 2000, or SQL Server 2005.

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

SQL Server upgrade versions

Reference: http://msdn.microsoft.com/en-us/library/ms143393.aspx

SQL Service account requirements

If you don't want to just make your service account(s) part of the local administrators group, here are the user rights that you need to know...

For SQL 2000, a service account requires these user rights:

• Act as Part of the Operating System = SeTcbPrivilege
• Bypass Traverse Checking = SeChangeNotify
• Lock Pages In Memory = SeLockMemory
• Log on as a Batch Job = SeBatchLogonRight
• Log on as a Service = SeServiceLogonRight
• Replace a Process Level Token = SeAssignPrimaryTokenPrivilege

For SQL 2005, it's somewhat more complicated, depending on what service the account is for:

SQL Server

Default instance: SQLServer2005MSSQLUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSSQLUser$ComputerName$InstanceName

• Log on as a service
• Act as part of the operating system (only on Windows 2000)
• Log on as a batch job
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process
• Permission to start SQL Server Active Directory Helper
• Permission to start SQL Writer

SQL Server Agent

Default instance: SQLServer2005SQLAgentUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005SQLAgentUser$ComputerName$InstanceName

• Log on as a service
• Act as part of the operating system (only on Windows 2000)
• Log on as a batch job
• Replace a process-level token
• Bypass traverse checking
• Adjust memory quotas for a process

Analysis Services

Default instance: SQLServer2005MSOLAPUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSOLAPUser$ComputerName$InstanceName

• Log on as a service

Reporting Services

Default instance: SQLServer2005ReportServerUser$ComputerName$MSSQLSERVER and SQLServer2005ReportingServicesWebServiceUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005ReportServerUser$ComputerName$InstanceName and SQLServer2005ReportingServicesWebServiceUser$ComputerName$InstanceName

• Log on as a service

Integration Services

Default or named instance: SQLServer2005DTSUser$ComputerName

• Log on as a service
• Permission to write to application event log
• Bypass traverse checking
• Create global objects
• Impersonate a client after authentication

Full-Text Search

Default instance: SQLServer2005MSFTEUser$ComputerName$MSSQLSERVER
Named instance: SQLServer2005MSFTEUser$ComputerName$InstanceName

• Log on as a service

SQL Server Browser

Default or named instance: SQLServer2005SQLBrowserUser$ComputerName

• Log on as a service

SQL Server Active Directory Helper

Default or named instance: SQLServer2005MSSQLServerADHelperUser$ComputerName

• No special rights

References: http://support.microsoft.com/kb/283811

http://msdn.microsoft.com/en-us/library/ms143504(SQL.90).aspx

Configuring SQL to use more than 2GB RAM

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