This page is created from HTTP status code information found at ietf.org and Wikipedia. Click on the category heading or the status code link to read more.
Microsoft provides system stored procedures (dbo.sp_changedbowner) for changing the db owner.
Keep in mind, a user database should have a db owner associated with it; dont mis interpret this witj db_owner role.
I personally prefer setting the db owner to ‘sa’
SELECT NAME,
suser_sname(owner_sid) AS 'owner',
CASE
WHEN suser_sname(owner_sid) = 'sa'
THEN ''
ELSE 'USE [' + NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'';'
END ScriptSql
FROM sys.databases
We can fix owner's jobs with this script:
SELECT
s.name
, l.name
, s.[enabled]
, CASE WHEN l.name <> 'sa' OR l.name IS NULL
THEN 'EXEC msdb.dbo.sp_update_job @job_id=N'''+CAST(job_id AS nvarchar(MAX))+''', @owner_login_name=N''sa'';'
ELSE '' END AS ScriptSql
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;
Script to fix DB/Job owner to 'SA' without question.
PRINT '-- SQL Script to fix DB owner to SA'
SET NOCOUNT ON;
DECLARE @sql NVARCHAR(max);
SET @sql = '';
SELECT @sql = @sql + 'USE [' + sys.databases.NAME + ']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10)
FROM sys.databases
LEFT OUTER JOIN sys.syslogins ON sys.databases.owner_sid = sys.syslogins.sid
WHERE (sys.syslogins.NAME <> N'sa')
OR (sys.syslogins.NAME IS NULL)
PRINT @sql;
EXEC (@sql);
PRINT '-- SQL Script to fix Job owner to SA'
SET @sql = '';
SELECT @sql = @sql + CASE WHEN l.NAME <> 'sa'
OR l.NAME IS NULL THEN 'EXEC msdb.dbo.sp_update_job @job_id=N''' + CAST(job_id AS NVARCHAR(MAX)) + ''', @owner_login_name=N''sa'';' + CHAR(13) + CHAR(10)
ELSE '' END
FROM msdb..sysjobs s
LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid;
PRINT @sql;
EXEC (@sql);
J'ai écrit sur mon profil que tous les services web fermeront un jour... mais j'oublie mes propres mots. La dernière victime est mon propre blogue. J'y explique... J'utilise certains plug-ins officiels et pas officiels, mais je ne pouvais pas imaginer qu'il pourrait aussi disparaitre.
Ahh!!! Oui, la page du site web qui avait le plug-in a été fermée ou effacée. Alors, il n'est plus simplement là.
Alors, cela peut être pratique quand l'on peut avoir un lab. Créer de virtuelles machines sans avoir le souci d'activer ou non les logiciels Microsoft.
print '-- SQL Script to fix owner and Recouvery mode'
print ''
SET NOCOUNT ON;
Declare @sql nvarchar(max);
declare @dbname nvarchar(255);
BEGIN TRY drop table #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db
FROM sys.databases f
WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)
while ((select count(*) from #db)>0)
BEGIN
Set @sql = '';
SELECT top 1
@dbname = name,
@sql =
-- change db owner to SA
+ case when suser_sname(f.owner_sid) = 'sa' THEN '' ELSE 'USE ['+ f.name+']; EXEC dbo.sp_changedbowner @loginame = N''sa'', @map = false;' + CHAR(13) + CHAR(10) END
-- SET RECOVERY SIMPLE
+ CASE when f.recovery_model_desc = 'SIMPLE' THEN '' ELSE +'USE [MASTER]; '+ 'ALTER DATABASE ['+ f.name+'] SET RECOVERY SIMPLE WITH NO_WAIT;'+ CHAR(13) + CHAR(10)
+'USE [MASTER]; '+'DBCC SHRINKDATABASE(N'''+ f.name+''');' + CHAR(13) + CHAR(10) END
FROM #db f
ORDER BY f.name;
if NOT(@sql = '')
SET @sql = 'PRINT ''Database:'+@dbname+''';' + CHAR(13) + CHAR(10)+@sql;
print @sql;
--exec (@sql);
delete from #db where name = @dbname;
END
Create script to change owner
PRINT '---------------------------';
PRINT '-- SQL Script to fix owner to "sa"';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)
DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(suser_sname(owner_sid) = ''sa'') AND [name] = ''?'' ))
BEGIN
PRINT ''---- Fix owner user to "sa"''
USE [?];EXEC dbo.sp_changedbowner @loginame = N''sa'',@map=false;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to fix owner to "sa"'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END
Sql Script to set recovery mode full.
PRINT '---------------------------';
PRINT '-- SQL Script to Set Recovery Mode FULL';
PRINT '-- '+CAST(getdate() as nvarchar(100));
PRINT '---------------------------';
SET NOCOUNT ON;
DECLARE @sql nvarchar(MAX);
DECLARE @dbname nvarchar(255);
BEGIN TRY DROP TABLE #db; END TRY BEGIN CATCH END CATCH;
SELECT * INTO #db FROM sys.databases f WHERE f.state_desc = 'ONLINE'
AND (f.database_id > 4)
DECLARE @sqltemplate nvarchar(MAX);
SET @sqltemplate = '
PRINT ''-- Database "?" '';
IF (EXISTS(SELECT * FROM sys.databases WHERE NOT(recovery_model_desc = ''FULL'') AND [name] = ''?'' ))
BEGIN
PRINT ''---- Fix recovery mode full''
USE Master;ALTER DATABASE [?] SET RECOVERY FULL WITH NO_WAIT;;
END;
'
PRINT 'PRINT ''----------------------'';';
PRINT 'PRINT ''-- Script to Set Recovery Mode FULL'';';
PRINT 'PRINT ''----------------------'';';
WHILE ((SELECT count(*) FROM #db)>0) BEGIN
SET @sql = '';
SELECT TOP 1 @dbname = name , @sql = REPLACE(@sqltemplate, '?', name) FROM #db f ORDER BY f.name;
DELETE FROM #db WHERE name = @dbname;
PRINT @sql;
END
go
-- Last SQL Server instance restart.
SELECT sqlserver_start_time FROM sys.dm_os_sys_info;
go
-- Last SQL Agent Server restart.
SELECT login_time as 'sqlserver_agent_start_time' FROM master.dbo.sysprocesses WHERE program_name = N'SQLAgent - Generic Refresher';
go
-- Last DB windows server restart.
exec xp_cmdshell 'systeminfo | find "Time:"';
go
Le SAP rendre disponible des Web Services par PI (Process Integration) pour réaliser des transactions entre les clients et le SAP.
Les clients peuvent être des applications développées sur plusieurs plateformes, en tous cas, pendant le processus du développement du client, il faut avoir certaines conditions et disponibilités:
We can spawn a Windows command shell and passes in a string for execution. Any output is returned as rows of text. (msdn)
ex:
xp_cmdshell 'dir C:\'
Set configuration
-- --------------------------------
-- Set Configuration
-- --------------------------------
--
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
--