Après restaure la base de données MS SQL, il est possible que certains utilisateurs de la base de données soient orphelins. Alors, voici les SQL scripts pour les réparer.
Répare tous les utilisateurs orphelins de toutes bases de données:
DECLARE @sql as nvarchar(max);
SET @sql = '
USE[?];PRINT''Database: ''+db_name();DECLARE @username VARCHAR(25);DECLARE
GetOrphanUsers CURSOR FOR SELECT UserName=NAME FROM sysusers WHERE issqluser=1AND(sid IS
NOT NULL AND sid<>0x0)AND SUSER_SNAME(sid)IS NULL ORDER BY NAME;OPEN GetOrphanUsers;FETCH
NEXT FROM GetOrphanUsers INTO @username;WHILE @@FETCH_STATUS=0BEGIN IF @username=''dbo''
EXEC sp_changedbowner''sa'';ELSE BEGIN BEGIN TRY EXEC sp_change_users_login''update_one''
,@username,@username;END TRY BEGIN CATCH PRINT''Fail to fix database user: ''+@username;END CATCH
;END FETCH NEXT FROM GetOrphanUsers INTO @username;END;CLOSE GetOrphanUsers;DEALLOCATE
GetOrphanUsers;';
EXEC sp_MSforeachdb @sql
Lire la suite de Récuperer les utilisateurs orphelins
Sometimes I want to have control over a SQL Server database, but always some connection blocks.
We can use this
USE MASTER;
ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
BUT, sometime, we cannot do it! Well, we have this solution...
SET NOCOUNT ON;
DECLARE @ToKill AS NVARCHAR(max);
SET @ToKill = '';
--SELECT @toKill = @ToKill + 'kill ' + cast(spid AS NVARCHAR(10)) + ';'
SELECT @toKill = @ToKill + 'begin try kill ' + cast(spid AS NVARCHAR(10)) + '; print ''Ok to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end try begin catch print ''Fail to kill : ' + cast(spid AS NVARCHAR(10)) + '''; end catch;'
FROM master..sysprocesses WHERE
spid <> @@spid -- Not my self
AND dbid = DB_ID(db_name()) -- only my database
--print @tokill;
EXEC (@tokill);
Solution without "DECLARE", it's useful when many times you need to kill connections.
exec('PRINT ''Kill connection of database: '' + db_name();DECLARE @kill AS NVARCHAR (MAX);SET @kill = '''';SELECT @kill = @kill + ''begin try kill '' + CAST (spid AS NVARCHAR (10)) + ''; print ''''Kill spid: '' + CAST (spid AS NVARCHAR (10)) + '' ('' + RTRIM(ISNULL(hostname, ''*NULL*'')) + ''|'' + RTRIM(ISNULL(nt_username, ''*NULL*'')) + '')'''' ; end try begin catch print ''''Fail to kill : '' + CAST (spid AS NVARCHAR (10)) + ''''''; end catch;'' FROM master..sysprocesses WHERE dbid = DB_ID(db_name()) AND spid <> @@spid AND hostprocess <> '''';PRINT (@kill);');
How can we send email by sql script ?
To enable Database Mail XP
USE msdb;
GO
EXEC sp_configure 'Database Mail XPs'
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'Database Mail XPs'
GO
To configure database mail
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddress VARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'smtp.server.com';
SET @EmailAddress = 'myemail@email.com';
SET @DisplayUser = 'Myname Noname';
EXECUTE msdb.dbo.sysmail_add_account_sp @account_name = @AccountName, @email_address = @EmailAddress, @display_name = @DisplayUser, @mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = @ProfileName
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = @ProfileName, @account_name = @AccountName, @sequence_number = 1;
--Now that everything is setup, I will send a test e-mail.
EXEC msdb.dbo.sp_send_dbmail @recipients = N'myemail@email.com', @body = 'Test Email Body', @subject = 'Test Email Subject from SQL', @profile_name = 'DBMailProfile'
On a besoin *parfois* de connaitre ou avoir la liste de toutes les tables et les colonnes
Ce script crée une table avec toutes définitions.
ex:
select * from _text_table_dictionary
where Datatype = 'datetime'
Lire la suite de Table dictionary