Les astuces de Sql Server 2000

Les astuces de Sql Server 2000

Comment connaître le nom de l'utilisateur connecté au serveur?

SQL Serveur fournit quatre fonctions permettants de connaître l'utilisateur connecté au serveur pour la session en cours. Exemple: 

DECLARE @usr char(30)
SET @usr = user
SELECT 'L''utilisateur courant est : ' + @usr
GO
--Ou 
SELECT 'L''utilisateur courant est : ' + SUSER_NAME()
GO
--Ou encore 
SELECT 'L''utilisateur courant est : ' + SESSION_USER
GO
--Nous n'allons pas oublier la fonction Current_user

SELECT 'L''utilisateur courant est : ' + CURRENT_USER

Comment obtenir le nombre d'utilisateurs connectés à une base de données ?

USE MaBase
GO
SELECT COUNT(*)
 FROM master..sysprocesses
WHERE dbid=db_id()

GO

ou 

SELECT COUNT(*)
 FROM master..sysprocesses

WHERE dbid=db_id('MabaseDeDonnée')

Pour la base courante ce sera alors : 

SELECT COUNT(*)
 FROM master..sysprocesses

WHERE dbid=db_id()

Je n'arrive pas à créer un utilisateur, le système me dit : user already exist ?

supprimer l'utilisateur et le recréer: 

exec sp_dropuser 'utilisateur' -- drop le user

exec sp_adduser 'utilateur','login' -- recrée utilisateur et l'associe à login

Quelle requête retourne les processus en train de consommer ?

create proc sp__cpu as 
/* 
* Auteur : Fabien Celaia 
* Date   : 11/01/2002 
* Desc   : Affiche les processus utilisateurs en cours de traitement 
* Parm   : - 
*/ 
SELECT 
     convert(char(4), spid) Spid, 
     convert(char(4), blocked) Blk, 
     convert(char(4), cpu) CPU, 
          left(loginame,15) 'Users', 
     left(hostname, 15) 'Host', 
          left(db_name(dbid),15) DB, 
          convert(char(20), cmd) Command, 
     convert(char(12), program_name) Program , 
     convert(char(10), status) Status 
FROM master..sysprocesses 
WHERE  spid <> @@spid 
AND status not in ( 'BACKGROUND', 'sleeping') 
ORDER BY cpu DESC 
GO 
GRANT execute on sp__cpu to public

GO

Comment extirper un DDL complet pour un utilisateur donné ?

Create  PROC sp_ddluser (@login varchar(30)) 
as 
BEGIN 
/* Auteur : Fabien Celaia 
 * Date   : 6.6.05 
 * Desc   : Extraction du DDL d'un utilisateur spécifique permettant sa recréation multi-serveurs 
 * IParm  : @login (obligatoire) : l'utilisateur à extraire 
 * OParm  : 0 = succès 
 *      -1 = l'utilisateur n'existe pas 
 */ 
 
 
if not exists (select * from sysusers where name = @login) 
   begin 
   PRINT 'L''utilisateur '+@login+'n''existe pas dans la base '+db_name()+' du serveur '+@@servername 
   return -1 
   end 
 
if not exists (select * from master..syslogins where name = @login) 
   begin 
   /* Login inexistant => création */ 
   select 'exec sp_addlogin '+ @login+ ', MotDePasse' 
   end 
 
SELECT 'EXEC SP_DROPUSER '+@login 
SELECT 'EXEC SP_ADDUSER '+@login+', '+ @login 
 
/* membres de groupes */ 
select 'GRANT ROLE '+ g.name +' TO '''+u.name+'''' 
from sysmembers m inner join sysusers u 
on m.memberuid = u.uid 
inner join sysusers g 
on m.groupuid=g.uid 
where u.uid > 2 
and u.name = @login 
 
 
/*Droits*/ 
select 
case p.protecttype 
   when 206 then 'REVOKE' 
   else 'GRANT ' end + 
 
case p.action 
   when 26 then 'REFERENCES' 
   when 178 then 'CREATE FUNCTION' 
   when 193 then 'SELECT' 
   when 195 then 'INSERT' 
   when 196 then 'DELETE' 
   when 197 then 'UPDATE' 
   when 198 then 'CREATE TABLE' 
   when 203 then 'CREATE DATABASE' 
   when 207 then 'CREATE VIEW' 
   when 222 then 'CREATE PROCEDURE' 
   when 224 then 'EXECUTE' 
   when 228 then 'BACKUP DATABASE' 
   when 233 then 'CREATE DEFAULT' 
   when 235 then 'BACKUP LOG' 
   when 236 then 'CREATE RULE' end + 
' ON ' + o.name + 
case when p.action <>then 
   case  when p.protecttype = 206 then ' FROM ' else ' TO ' END +u.name 
else '' end + 
 
case when p.protecttype = 204 then ' WITH GRANT OPTION' else '' end 
 
from sysprotects p 
inner join sysusers u on u.uid=p.uid 
inner join sysobjects o on o.id=p.id 
where    p.columns = 0x01 OR p.columns is null 
and u.name = @login 
order by o.name 
 
end

Comment changer temporairement un mot de passe que l'on ne connaît pas ?

1) Changer de mot de passe après l'avoir sauvé

2) 

select password, name into old_login from master..sysxlogins

exec sp_password NULL, NouveauMotDePasse, MonLogin

2) Revenir à l'ancien 

exec sp_configure updates,1 
reconfigure with override 
 
update master..sysxlogins 
set password=O.password 
from old_login O, sysxlogins L 
where L.name=O.name 
and O.name='MonUtilisateur' 
 
drop table old_login

exec sp_configure updates,0

Comment connaître la dernière date de modification du mot de passe des logins ? 

select name loginname,   updatedate 
from master..syslogins 
where loginname is not null

Comment configurer une base de données en mode utilisateur unique ?

ALTER DATABASE MABASE
SET SINGLE USER WITH ROLLBACK IMMEDIATE

Comment connaître le nom de l'utilisateur connecté au serveur?

SQL Serveur fournit quatre fonctions permettants de connaître l'utilisateur connecté au serveur pour la session en cours. Exemple: 

DECLARE @usr char(30)
SET @usr = user
SELECT 'L''utilisateur courant est : ' + @usr
GO
--Ou 
SELECT 'L''utilisateur courant est : ' + SUSER_NAME()
GO
--Ou encore 
SELECT 'L''utilisateur courant est : ' + SESSION_USER
GO
--Nous n'allons pas oublier la fonction Current_user

SELECT 'L''utilisateur courant est : ' + CURRENT_USER

Comment connaître le type d'authentification installée sur le serveur ?

select CASE
WHEN convert(sysname, serverproperty('Edition')) IS NULL THEN 'ERREUR'
WHEN convert(sysname, serverproperty('Edition'))=0 THEN 'SECURITE INTEGREE'
WHEN convert(sysname, serverproperty('Edition'))=1 THEN 'SECURITE NON INTEGREE'
END AS AUTHENTIFICATION

Quelle procédure stockée permet de limiter le nombre de connexions simultanées ?

 
sp_configure connections, n
RECONFIGURE WITH OVERRIDE

--n est le nombre de connexions souhaitées

Comment fixer la durée d'attente de libération d'un verrou sur un object de la base de données ?

SET LOCK_TIMEOUT permet à une application de définir le délai maximal pendant lequel
une instruction doit attendre en cas de ressource bloquée. Si l'attente d'une instruction
dépasse la valeur du paramètre LOCK_TIMEOUT, l'instruction bloquée est automatiquement
annulée, et un message d'erreur renvoyé à l'application.
Cette valeur est fixée à -1 au début d'une connexion.

--Fixe la valeur du lock_timeout à 1,8 seconde

SET LOCK_TIMEOUT=1800

Pour connaître la valeur courante, on utilise la variable @@LOCK_TIMEOUT 

--consulter la valeur du lock timeout

SELECT @@LOCK_TIMEOUT AS LOCK_TIMEOUTC

Comment sauvegarder une base de données

Via la commande backup

backup database MaBase to DISK=N'c:\temp\MonFichier.bak'

Comment restaurer une base de données depuis un fichier .bak ?

Via la commande restore, en eyant au préalable supprimé toutes les connexions existantes sur la base qui sera écrasée

restore database MaBase from DISK=N'c:\temp\MonFichier.bak'

Comment faire une copie de ma base de données ?

via commandes backup/restore 

backup database MaBase to DISK=N'c:\temp\Mabase.bak'

restore database MaCopie from DISK=N'c:\temp\Mabase.bak'

Comment savoir si je suis en mode recouvrement de type FULL ?

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Recovery')

Comment configurer une base de données en mode FULL RECOVERY ?

ALTER DATABASE MABASE SET RECOVERY FULL

Comment connaîte le jeu de caractère et le tri configuré sur son serveur ? 

 
 
sp_helpsort
Comment faire un export complet d'une base ( structure avec clés, procédures etc...) y.c. des données qui sont dans les tables.
Comment retrouver la date de la dernière restauration d'une base ?

Si vous avez exécuté cette restauration via un job de Microsoft SQL Server, vous pouvez rechercher l'information via la base système msdb 

USE VotreBase
GO
SELECT destination_database_name,
    restore_date,
    b.database_name,
    physical_name,
    backup_start_date
FROM msdb.dbo.RestoreHistory h 
    INNER JOIN msdb.dbo.BackupSet b ON h.backup_set_id = b.backup_set_id
    INNER JOIN msdb.dbo.BackupFile f ON f.backup_set_id = b.backup_set_id
WHERE b.database_name = db_name()

GO

Si par contre vous avez effectué a restauration via la ligne de commande ou l'assistant interactif, ne vous restera plus qu'à aller rechercher dans les journaux 

 
Comment savoir si l'exécution d'un utilitaire (bcp, isql, osql) s'est bien déroulé ?

En interrogeant la variable système %ERRORLEVEL%, directement après l'appel de l'exécutable. 0 = succès, sinon 1 

 
Comment faire pour lire le journal de transaction ?
 
use MaBase
GO
SELECT * FROM ::fn_dblog(null, null)
GO
DBCC LOG('MaBase')
Comment insérer une valeur implicite dans un champs auto-incrémenté ?

SET IDENTITY_INSERT Autorise l'insertion de valeurs explicites dans la colonne d'identité d'une table. Exemple : 

SET IDENTITY_INSERT product ON
GO
INSERT INTO products (id, product)
VALUES(3, 'garden shovel').
GO
SET IDENTITY_INSERT product OFF

GO

Cette façon de faire va à l'encontre du comportement même de l'identity et ne devrait être utilisée qu'exceptionnellement. 

 
Comment connaître la valeur récente inserée dans un champs auto-incrémenté ?

Il suffit de consulter la valeur de la variable de sessions @@IDENTITY juste après l'insertion, faire :

Select @@Identity as Dernière_Valeur_AutoIncrémenté

Utiliser la fonction IDENT_CURRENT pour Renvoie la dernière valeur d'identité générée
pour une table spécifiée dans n'importe quelles sessions et portée.

SELECT IDENT_CURRENT('t_produit')

Comment remettre à zéro la valeur d'un compteur autoincrémenté ?


Vous pouvez supprimer la table puis la recréer.
Mais, je préfère supprimer les données de la table puis redéfinir la valeur de l'auto-incrément par :

DBCC CHECKIDENT ('MaTable', RESEED, 1)

Publié par