NT AUTHORITY\SYSTEM Usage:
SQL Server Full Text service, "NT AUTHORITY\SYSTEM" need SYSADMIN privileges on the SQL Server 2000 if Builtin\Adminitrator revoked SYSADMIN privileges.
On SQL Server 2005, do not delete this account or remove it from the SYSADMIN fixed server role.
The NTAUTHORITY\SYSTEM account is used by Microsoft Update and by Microsoft SMS to apply service packs and hotfixes to a SQL Server 2005 installation.
The NTAUTHORITY\SYSTEM account is also used by the SQL Writer Service for third party backup using VSS.
Question: and "why sysadmin role has been granted by default."
Answer: Since the "NT AUTHORITY\SYSTEM" is member of "Builtin\Administrator" SYSADMIN server role is granted by default.
By default, the Local System Account is a member of the local Administrators group and is therefore a member of the sysadmin group in SQL Server.
SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections,
loginame as LoginName
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame
sp_serveroption 'SQLServer\SQLInstanz', 'data access', 'TRUE'-- Abschneiden ohne Sichern der Transaktionen
BACKUP LOG DerDatenbankName WITH TRUNCATE_ONLY
DBCC SHRINKFILE (DerNameDerLogDatei,GrößeInMegaByte)DECLARE @name varchar(256)
SET NOCOUNT ON
DECLARE pc SCROLL CURSOR FOR
SELECT name FROM sysobjects WHERE (xtype = 'U')
OPEN pc
WHILE 1=1
BEGIN
FETCH pc INTO @name
IF @@fetch_status <> 0 BREAK
SELECT @name = 'guest.' + @name
SELECT @name
EXEC sp_changeobjectowner @name, 'dbo'
END
CLOSE pc
DEALLOCATE pc
SET NOCOUNT OFF
GOSELECT * FROM master.dbo.sysdatabases WHERE (status & 8 = 8)
ALTER DATABASE "@name" SET RECOVERY SIMPLE
EXECUTE sp_msforeachdb 'ALTER DATABASE [?] SET RECOVERY SIMPLE'RESTORE LOG HASI WITH RECOVERYexec sp_change_users_login 'Update_One','admin','admin'BACKUP Database "GATE" TO DISK='M:\SQLDUMP\FHHEWAM_20040309.bak' with stats = 1, compression
BACKUP Database "GATE" TO DISK='M:\SQLDUMP\FHHEWAM_20040309.bak' WITH INIT
BACKUP Database "GATE" TO DISK='M:\SQLDUMP\FHHEWAM_20040309.bak' WITH NOINIT, DIFFERENTIAL
BACKUP Database "FHHEWAM" TO DISK='D:\BACKUP\FHHEWAM_20040309.bak' WITH INIT
RESTORE DATABASE @DBName FROM DISK=@File WITH RECOVERYALTER DATABASE Orga_1001 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
RESTORE DATABASE Orga_1001 FROM DISK='\\server\m$\temp\Orga_1001_20101101.bak'
WITH
MOVE 'Orga_1001' to 'N:\MSSQL\DATA\Orga_1001.mdf',
MOVE 'Orga_1001_Log' to 'N:\MSSQL\DATA\Orga_1001_Log.ldf', REPLACE
ALTER DATABASE Orga_1001 SET MULTI_USER WITH ROLLBACK IMMEDIATE
EXEC xp_stopmail
EXEC xp_startmail
EXEC xp_sendmail @recipients = 'Thomas.hausdorf@Dataport.De', @message = 'The master database is full.'sp_dboption 'datenbank', 'single user', 'off'-- Backup of data
SELECT Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'd'
GROUP BY Database_Name
ORDER BY 3 DESC
Go
-- Find the backup of Transaction Log files
SELECT Database_Name,
CONVERT( SmallDateTime , MAX(Backup_Finish_Date)) as Last_Backup,
DATEDIFF(d, MAX(Backup_Finish_Date), Getdate()) as Days_Since_Last
FROM MSDB.dbo.BackupSet
WHERE Type = 'l'
GROUP BY Database_Name
ORDER BY 3 DESCexec sp_trace_setstatus , 0
exec sp_trace_setstatus 1, 0
-- Löschen von Traces
exec sp_trace_setstatus 1, 2
-- Starten von Traces
exec sp_trace_setstatus 1, 1
-- Kontrolle läuft ein Trace
SELECT * FROM :: fn_trace_getinfo(0) RESTORE DATABASE Hotline
FROM DISK = 'M:\SQLBACKUP\XX_db_200509140959.BAK'
WITH NORECOVERY,
MOVE 'XX_dat' TO 'M:\MSSQL8DB\MSSQL$Instanz\Data\XX.mdf',
MOVE 'XX_log' TO 'L:\MSSQL8DB\MSSQL$Instanz\Log\XX.ldf'sp_configure 'show advanced options', 1 -- ändern der SQL Server config auf erweiterte Darstellung
reconfigure with override -- aktivieren der geänderten config
sp_configure -- zeigt die aktuelle config an
sp_renamedb [ @dbname = ] 'old_name' , [ @newname = ] 'new_name'EXEC sp_attach_db @dbname = N'pubs',
@filename1 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs.mdf',
@filename2 = N'c:\Program Files\Microsoft SQL Server\MSSQL\Data\pubs_log.ldf'USE [master]
GO
CREATE DATABASE [MOSSDB] ON
( FILENAME = N'M:\MSSQLDB\MSSQL10.MOSSINST\MSSQL\DATA\MOSSDB.mdf' )
FOR ATTACH
GO"%PrgPath%\osql" -S %2 -d msdb -E -b -Q "exit(SELECT database_name, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last FROM backupset WHERE type = 'D' AND database_name IN (select name from master..sysdatabases) GROUP BY database_name ORDER BY 3 DESC, 1 ASC )" >>%LogFile% 2>&1
Logsicherung:
"%PrgPath%\osql" -S %2 -d msdb -E -b -Q "exit(SELECT database_name, CONVERT( SmallDateTime , MAX(backup_finish_date)) as Last_Backup, DATEDIFF(d, MAX(backup_finish_date), Getdate()) as Days_Since_Last FROM backupset WHERE type = 'L' AND database_name NOT IN (SELECT name FROM master.dbo.sysdatabases WHERE (status & 8 = 8)) AND database_name IN (select name from master..sysdatabases) GROUP BY database_name ORDER BY 3 DESC, 1 ASC )" >>%LogFile% 2>&1--- Reports the current compatibility level of 'MyDBName'
EXEC sp_dbcmptlevel 'MyDBName';
GO
--- Changes it to SQL 2000 - 80
EXEC sp_dbcmptlevel 'MyDBName', '80';
GO
--- Changes it to SQL 2005 - 90
EXEC sp_dbcmptlevel 'MyDBName', '90';
GOsp_who2 808
select top 10 * from sysprocesses where SPID = 664
select top 10 * from sysprocesses where SPID = 628
select * from sysprocesses where blocked = 819
Satzsperren?
select * from sysprocesses where blocked <>0
select * from sysprocesses where SPID = 808
Was für ein Befehl wird aktuell ausgeführt
dbcc inputbuffer(808)
select top 10 * from sysprocesses where waittime > 120000
select db_name(36)setspn -L "Domäne/Benutzer"