SQL
Server Management Studio
- Running SSMS as another Windows
user ( with more Rights ) : create .bat file
C:\WINDOWS\system32\runas.exe /user:DOMAIN\<user>
"C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"
- Change Language for Error
Messages in MSSMStudio
SET LANGUAGE English
- SQL Developper to SQL Server Named
Instance :
<PORT
NUMBER>/;instance=<INSTANCE NAME>
Example:-
1433/;instance=MyInstance
0] Instance Level
0.1
Post-Installation Check-List
EXEC sp_configure 'show advanced options', 1 --
Changing to 1 turns it on
GO
RECONFIGURE WITH OVERRIDE
go
sp_configure
'backup compression default' , 1
go
reconfigure
go
Remark : tune these parameters according to
sp_configure 'max worker threads' -> 0
sp_configure 'max degree of parallelism' -> 0 or 8 when
cpu_count >= 8
Setting the max degree of parallelism
option to 0 allows SQL Server to use all available processors upt to a maximum
of 64 processors in a parallel plan execution.
Memory MB :
sp_configure "max server memory" , 2048
sp_configure "min server memory" , 512
reconfigure
Estimate Max memory as follow :
·
Keep 2 GB for the OS
·
Reserve 386 MB for MemToLive (CLRs, linked servers,
BLOB, …)
·
Find the max worker thread (http://msdn.microsoft.com/fr-fr/library/ms187024.asp)
et allow 2 MB per thread ( 64 bits Architecture ).
-- 1/ Server RAM
[2012]
SELECT Physical_memory_kb/1024 AS Server_Physical_Memory_MB
, cpu_count ,
( select
physical_memory_in_use_kb/1024 as phys_mem_in_use_MB
from sys.dm_os_process_memory ) Mem_in_Use_MB
FROM sys.dm_os_sys_info
[2008]
SELECT Physical_memory_in_bytes/1024/1024 AS Server_Physical_Memory_MB
, cpu_count ,
( select
physical_memory_in_use_kb/1024 as phys_mem_in_use_MB
from sys.dm_os_process_memory ) Mem_in_Use_MB
FROM sys.dm_os_sys_info
-- 32 GB Mem 1273
2/ SQL Instance
[2012]
SELECT ceiling((committed_kb)/1024.0) as
Bpool_Acquired_MB, ceiling((committed_target_kb)/1024.0) as
BPool_Reserved_MB,
ceiling((visible_target_kb)/1024.0) as visible_target_MB
FROM sys.dm_os_sys_info
[2008]
SELECT ceiling((bpool_committed)/1024.0*8)
as Bpool_Acquired_MB, ceiling((bpool_commit_target)/1024.0*8)
as BPool_Reserved_MB,
ceiling((bpool_visible)/1024.0*8) as
visible_target_MB FROM sys.dm_os_sys_info
-- 1024 1024 1024
-- 3/ Modify
sp_configure "max server memory" , 4096
go
sp_configure "min server memory" , 1024
go
reconfigure
go
0.2
Find an object across every databases :
sp_MSforeachdb 'use [?] ; select
db_name() db_name, [name] as ObjectName, Type as ObjectType From ?..sysobjects
where lower(name) like ''%idappel%'''
db_name object_name object_type
DB_ERS IdAppelAllocataire P
DB_ERS IdAppelANI P
1] Table
1.1
Corruption
1.1.1
Detection
use FEPDW
DBCC CHECKTABLE(sysssislog)
Erreur de table : ID d'objet
942626401, ID d'index 1, ID de partition 72057594047299584, ID d'unité
d'allocation 72057594049921024 (type LOB data). Le nœud de données hors ligne
page (1:137889), emplacement 76, ID de texte 0 est référencé par la page
(1:137904), emplacement 17 et par la page (1:233732), emplacement 6.
Il y a 294482 lignes dans 15627 pages
pour l'objet "sysssislog".
CHECKTABLE a trouvé 0 erreurs
d'allocation et 68 erreurs de cohérence dans la table 'sysssislog' (ID d'objet
942626401).
repair_allow_data_loss is the minimum
repair level for the errors found by DBCC CHECKTABLE (FEPDW.dbo.sysssislog).
DBCC execution completed. If DBCC
printed error messages, contact your system administrator.
1.1.2
Tools
DBCC
CHECKTABLE
(
table_name
| view_name
[
, { NOINDEX | index_id }
|,
{ REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD }
]
)
[
WITH
{
ALL_ERRORMSGS ]
[
, EXTENDED_LOGICAL_CHECKS ]
[
, NO_INFOMSGS ]
[
, TABLOCK ]
[
, ESTIMATEONLY ]
[
, { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
REPAIR_ALLOW_DATA_LOSS
| REPAIR_FAST | REPAIR_REBUILD
Specifies that DBCC CHECKTABLE
repair the found errors. To use a repair option, the database must be in
single-user mode.
REPAIR_ALLOW_DATA_LOSS
Tries to repair all reported
errors. These repairs can cause some data loss.
REPAIR_FAST Syntax is
maintained for backward compatibility only. No repair actions are performed.
REPAIR_REBUILD
Performs repairs that have no
possibility of data loss. This can include quick repairs, such as repairing
missing rows in non-clustered indexes, and more time-consuming repairs, such as
rebuilding an index.
REPAIR_REBUILD does not repair
errors involving FILESTREAM data.
DBCC CHECKDB
[
[ ( database_name
| database_id | 0
[
, NOINDEX
|
, { REPAIR_ALLOW_DATA_LOSS | REPAIR_FAST | REPAIR_REBUILD } ]
) ]
[ WITH
{
[
ALL_ERRORMSGS ]
[
, EXTENDED_LOGICAL_CHECKS ]
[
, NO_INFOMSGS ]
[
, TABLOCK ]
[
, ESTIMATEONLY ]
[
, { PHYSICAL_ONLY | DATA_PURITY } ]
}
]
]
1.1.3
Fix
USE master;
GO
ALTER DATABASE FEPDW
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
Les
transactions non qualifiées sont annulées. Fin de l'opération
(estimation) : 100%.
use FEPDW
go
DBCC CHECKTABLE(sysssislog,REPAIR_ALLOW_DATA_LOSS)
Go
Résultats DBCC pour 'sysssislog'.
Réparation : l'index Clustered a été reconstruit pour
l'objet "dbo.sysssislog" dans la base de données "FEPDW".
Réparation : enregistrement supprimé pour l'objet ID
942626401, index ID 1, partition ID 72057594047299584, unité d'allocation ID
72057594049855488 (type In-row data), à la page (1:137904), emplacement 1. Les
index seront reconstruits.
Réparation : colonne de données en dehors des lignes
supprimée ; ID 277547646976, pour l'ID d'objet 942626401, ID d'index 1, ID
de partition 72057594047299584, ID d'unité d'allocation 72057594049855488 (type
In-row data) de la page (1:233732), emplacement 6.
Msg 8945, Niveau 16, État 1, Ligne 1
Erreur de table : ID d'objet 942626401, l'ID d'index 1
sera reconstruit.
L'erreur a été
résolue.
Msg 8929, Niveau 16, État 1, Ligne 1
ID d'objet 942626401, ID d'index 1, ID de partition
72057594047299584, ID d'unité d'allocation 72057594049855488 (type In-row
data) : erreurs détectées dans les données hors ligne avec l'ID
277547515904 possédé par l'enregistrement data identifié par RID = (1:233732:4)
L'erreur a été
résolue.
Msg 8961, Niveau 16, État 1, Ligne 1
Erreur de table : ID d'objet 942626401, ID d'index 1, ID
de partition 72057594047299584, ID d'unité d'allocation 72057594049921024 (type
LOB data). Le nœud de données hors ligne page (1:137889), emplacement 60, ID de
texte 277544566784 ne correspond pas à la référence page (1:137904),
emplacement 1.
Impossible de
résoudre cette erreur.
Il y a 297870 lignes dans 15807 pages pour l'objet
"sysssislog".
CHECKTABLE a trouvé 0 erreurs d'allocation et 68 erreurs de
cohérence dans la table 'sysssislog' (ID d'objet 942626401).
CHECKTABLE a corrigé 0 erreurs d'allocation et 34 erreurs de
cohérence dans la table 'sysssislog' (ID d'objet 942626401).
repair_allow_data_loss est le niveau minimum de réparation
pour les erreurs trouvées par DBCC CHECKTABLE (FEPDW.dbo.sysssislog,
repair_allow_data_loss).
Exécution de DBCC terminée. Si DBCC vous a adressé des
messages d'erreur, contactez l'administrateur système.
-- check
use FEPDW
go
DBCC CHECKTABLE(sysssislog)
go
Résultats DBCC
pour 'sysssislog'.
Il y a 297836 lignes dans 15148 pages
pour l'objet "sysssislog".
Exécution de DBCC terminée. Si DBCC
vous a adressé des messages d'erreur, contactez l'administrateur système.
ALTER DATABASE FEPDW
SET MULTI_USER;
GO
1.2
Create Table
1.2.1
Create & Populate Table with a sequence
create table t_big (c1 int, c2 int, c3 char(1000))
go
declare @i int
select @i =
0
while (@i < 10000)
begin
insert into t_big values (@i, @i
+ 5000, replicate('a',
250))
set @i = @i
+ 1
end
1.2.2
Create Table As - CTAS
-- Copy CT t1_copy AS t1
create table t1 ( id int
, name varchar(16) )
insert into t1
values ( '1' ,
'TEST 1' )
insert into t1
values ( '2' ,
'TEST 2' )
select * from
t1
SELECT * INTO t1_copy
FROM t1 --
WHERE 1=2
select * from
t1_copy
CTAS using a
Linked Server ( equivalent to Oracle database Link )
USE master;
GO
EXEC sp_addlinkedserver
@server=N'S1_SCCM_INS-12', -- link name
@srvproduct=N'',
-- sql server
@provider=N'SQLNCLI',
@datasrc=N'W-INS-12'; --
remote sql instance
GO
EXEC sp_addlinkedsrvlogin 'S1_SCCM_INS-12', 'false'
, 'DOMAIN\localuser' , 'INS_REM_R', 'psw'
sp_addlinkedsrvlogin [ @rmtsrvname = ] 'rmtsrvname'
[ , [ @useself = ] 'useself' ]
[ , [ @locallogin = ] 'locallogin' ]
[ , [ @rmtuser = ] 'rmtuser' ]
[ , [ @rmtpassword = ] 'rmtpassword' ]
select count(1)
from [S1_SCCM_INS-12].CM_001.dbo.v_R_System
SELECT * INTO
v_R_System FROM [S1_SCCM_INS-12].CM_001.dbo.v_R_System
1.3
Rename Table / Column
-- Restore copy of t1 if
failed insert
insert into t1
values ( '3' ,
'TEST 3 KO' )
select * from
t1 -- seen from every session, MSSQL implicit commit
DROP TABLE t1
sp_RENAME 't1_copy'
, 't1'
select *
from t1
sp_RENAME '[OldTableName]' ,
'[NewTableName]'
sp_RENAME 'TableName.[OldColumnName]'
, '[NewColumnName]', 'COLUMN'
1.4
Find table name with given column name
Find which
tables have a given column name
SELECT distinct a.name , b.name FROM
sysobjects a , syscolumns
b WHERE a.id=b.id and
b.name
like '%TIME%' order by 1
use CM_001
SELECT COLUMN_NAME, TABLE_NAME FROM
INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%AgentID%'
1.5
Query Index
-- index individualy
SELECT OBJECT_NAME(ps.[object_id]) AS
[Table] ,
i.name AS [Index],
SUM(ps.row_count) AS
[Rows],
SUM(ps.used_page_count)*8/1024
AS [Data_MB],
SUM(ps.reserved_page_count)*8/1024
AS [Reserved_MB]
FROM sys.dm_db_partition_stats
AS ps INNER
JOIN sys.indexes AS i
ON i.[object_id] = ps.[object_id] AND i.index_id = ps.index_id
WHERE -- i.type_desc IN ( 'CLUSTERED', 'HEAP' )
AND
i.[object_id] > 100 AND OBJECT_SCHEMA_NAME(ps.[object_id]) <>
'sys'
and ps.[object_id] = object_id('dbo.CI_CurrentSettingsComplianceStatusDetail')
GROUP BY
ps.[object_id]
, i.name
1.6
Triggers
1.6.0 List every
triggers in a database ( use )
SELECT sysobjects.crdate,
sysobjects.name AS
trigger_name
,USER_NAME(sysobjects.uid) AS trigger_owner
,s.name
AS table_schema
,OBJECT_NAME(parent_obj) AS
table_name
,OBJECTPROPERTY( id,
'ExecIsUpdateTrigger') AS isupdate
,OBJECTPROPERTY( id,
'ExecIsDeleteTrigger') AS isdelete
,OBJECTPROPERTY( id,
'ExecIsInsertTrigger') AS isinsert
,OBJECTPROPERTY( id,
'ExecIsAfterTrigger') AS isafter
,OBJECTPROPERTY( id,
'ExecIsInsteadOfTrigger') AS isinsteadof
,OBJECTPROPERTY(id,
'ExecIsTriggerDisabled') AS [disabled]
FROM sysobjects
INNER JOIN
sysusers ON sysobjects.uid
= sysusers.uid
INNER JOIN
sys.tables
t ON sysobjects.parent_obj = t.object_id
INNER JOIN
sys.schemas
s ON t.schema_id = s.schema_id
WHERE sysobjects.type =
'TR'
order by
crdate desc
1.6.1
Trigger to Insert in another Table according to the value of an Inserted column
( « insert » pseudo-table )
The source
Oracle equivalent of the
Trigger is :
create or replace
TRIGGER "SCHEMA"."IPL7_BI_ORDER_DETAILS"
BEFORE INSERT ON EP_TAB
FOR EACH ROW
BEGIN
if :new.DELIVERY_LOCATION_ID in
(9106,8781,8802,8846) THEN
Insert into TABLE_SD_DETAIL(CATALOG_ID,
QTY_SUPPLIED,UNIT_NET_PRICE,DESTOCKING_LOCATION_ID,DESTOCKING_DATE )
values (:new.CATALOG_ID,
:new.QTY_DELIVERED,:new.UNIT_NET_PRICE,8669,:new.DELIVERY_DATE);
end if;
END
USE [DB_NAME]
GO
CREATE TRIGGER [SCHEMA].IPL7_BI_ORDER_DETAILS
ON [SCHEMA].EP_TAB
FOR INSERT
AS
DECLARE @nNewValue int
SELECT @nNewValue=a.DELIVERY_LOCATION_ID FROM inserted
a
if @nNewValue in (9106,8781,8802,8846)
Insert into [SCHEMA].TABLE_SD_DETAIL(CATALOG_ID,QTY_SUPPLIED,UNIT_NET_PRICE,DESTOCKING_LOCATION_ID,DESTOCKING_DATE
)
SELECT CATALOG_ID, QTY_DELIVERED,UNIT_NET_PRICE,8669,DELIVERY_DATE FROM inserted ;
go
1.6.2
Trigger to store the Inserted PK Id in another Table field ( use of (
« inserted » pseudo-table )
The source
Oracle equivalent of the
Trigger is :
create or replace
TRIGGER "SCHEMA"."IPL7_TTAB_ASSET"
BEFORE INSERT ON TAB_ASSET
FOR EACH ROW
BEGIN
SELECT WTAB_ASSET.nextval
INTO :new.TAB_ASSET_ID FROM DUAL;
:new.available_field_6
:= to_char(:new.asset_id);
END;
Oracle
Trigger uses a Sequence to get the ID, whereas SQL has already the ID managed
by the Column type :
[TAB_ASSET_ID] [int]
IDENTITY(1,1) NOT NULL
USE [DB_NAME]
GO
SET ANSI_NULLS
ON
GO
SET QUOTED_IDENTIFIER
ON
GO
ALTER TRIGGER [SCHEMA].[IPL7_TTAB_ASSET]
ON [SCHEMA].[TAB_ASSET]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
UPDATE TAB_ASSET SET [available_field_6]
= inserted.[TAB_ASSET_ID]
FROM inserted INNER JOIN TAB_ASSET
On inserted.[TAB_ASSET_ID]
= TAB_ASSET.[TAB_ASSET_ID]
END
Other
equivalent : Oracle
if ( :new.status_id in (5) or
:new.status_id in (25) or :new.status_id in (26) )
then
@@FIRETRIGGER@@
end if;
SQL_Server :
IF EXISTS (SELECT REQUEST_ID FROM INSERTED WHERE STATUS_ID in
(5,25,26))
BEGIN @@FIRETRIGGER@@
END
1.7
Constraints
1.7.1 Find/Drop
FK to target table before Insert
USE [BASE_BASE04]
GO
select distinct name from sys.objects where object_id
in
( select
fk.constraint_object_id from sys.foreign_key_columns as fk
where
fk.referenced_object_id =
(select object_id from sys.tables
where name = 'PROFIL')
)
ALTER TABLE [SCHEMA04].[EMPLOYEE] DROP CONSTRAINT [FK_EMPLOYEE_PROFIL]
GO
after insert,
add the constraint back
ALTER TABLE [SCHEMA04].[EMPLOYEE] WITH NOCHECK ADD CONSTRAINT [FK_EMPLOYEE_PROFIL] FOREIGN
KEY([PROFIL_ID])
REFERENCES [SCHEMA04].[PROFIL] ([PROFIL_ID])
GO
1.7.2 Get
Foreign Key Constraint dependencies
Ø
List every Foreign Keys To a Table
SELECT
K_Table
= FK.TABLE_NAME,
FK_Column
= CU.COLUMN_NAME,
PK_Table
= PK.TABLE_NAME,
PK_Column
= PT.COLUMN_NAME,
Constraint_Name
= C.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
ON
C.CONSTRAINT_NAME
= FK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
ON
C.UNIQUE_CONSTRAINT_NAME
= PK.CONSTRAINT_NAME
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
ON
C.CONSTRAINT_NAME
= CU.CONSTRAINT_NAME
INNER JOIN (
SELECT
i1.TABLE_NAME,
i2.COLUMN_NAME
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
i1
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
WHERE
i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
ON
PT.TABLE_NAME
= PK.TABLE_NAME
WHERE PK.TABLE_NAME = 'W_QUERIES'
-- AND CU.COLUMN_NAME = 'MyColumn'
K_Table ( source ) FK_Column PK_Table ( trg) PK_Column Constraint_Name
W_QUERIES_RESULT QUERY_ID W_QUERIES QUERY_ID FK_W_QUERIES_W_QUERIES_RESULT
Ø
List every Foreign Keys Of a Table
-- use
<DB>
select * from (
SELECT Object_name(f.parent_object_id)
AS
TableName,
Col_name(fc.parent_object_id, fc.parent_column_id)
AS ColumnName,
Object_name (f.referenced_object_id)
AS ReferencedTableName,
Col_name(fc.referenced_object_id, fc.referenced_column_id)
AS
ReferencedColumnName,
f.name
AS ForeignKey,
Quotename(Schema_name(f.schema_id)) + '.'
+ Quotename(
Object_name(f.parent_object_id)) AS
schemaname,
Quotename(Schema_name(o.schema_id)) + '.'
+ Quotename(
Object_name(f.referenced_object_id))
AS ReferencedSchemaName
FROM sys.foreign_keys AS f
INNER JOIN
sys.foreign_key_columns
AS fc
ON f.OBJECT_ID = fc.constraint_object_id
INNER JOIN sys.objects AS o
ON o.OBJECT_ID = fc.referenced_object_id
) A where TableName = 'MYTABLE'
1.7.3 Find/Drop
Unicity Constraint
create table
t1 (
id int ,
name varchar(16)
)
insert into
t1 values ( '1'
, 'TEST 1' )
insert into
t1 values ( '2'
, 'TEST 2' )
alter table
[MASTER].[dbo].[T1]with nocheck
add unique (id)
insert into
t1 values ( '1'
, 'TEST 1' )
Violation de la contrainte UNIQUE KEY 'UQ__t1__3213E83E025D5595'. Impossible d'insérer
une clé en double dans l'objet 'dbo.t1'.
-- find constraint name
select O.name
from sysobjects AS O
left join sysobjects AS T
on O.parent_obj
= T.id
where isnull(objectproperty(O.id,'IsMSShipped'),1) =
0
and T.name
= 'T1'
alter table [MASTER].[dbo].[T1] drop
constraint UQ__t1__3213E83E025D5595 ;
insert into
t1 values ( '1'
, 'TEST 1' )
OK
1.8
Trigger based Audit DML on Table
Get
Operation type and log an audit row only if a criteria on teh Inserted or
deleted record is met.
create TABLE
AUDIT (
audittime datetime default(getdate())
, targettable
sysname , Event_A varchar(50)
, host varchar(50) ,
appli varchar(50)
, loginname
sysname
, spid int
, sqltext nvarchar(max))
go
create TRIGGER
TR_CollectionMembers ON CollectionMembers FOR INSERT, UPDATE, DELETE
AS
declare @operation
varchar(16)
declare @NewSI
varchar(16)
BEGIN
CREATE
TABLE #DBCC
(EventType varchar(50),
Parameters varchar(50),
EventInfo nvarchar(max))
INSERT
INTO #DBCC EXEC ('DBCC INPUTBUFFER(@@SPID)')
set @operation
= 'DUMMY'
IF EXISTS(SELECT 1 FROM inserted) BEGIN
SELECT
@NewSI=SiteID
FROM inserted
IF
@NewSI = '001000FB' set @operation
= 'INSERT'
END
IF EXISTS(SELECT 1 FROM deleted) BEGIN
SELECT
@NewSI=SiteID
FROM deleted
IF
@NewSI = '001000FB' set @operation
= 'DELETE'
END
IF ( @operation != 'DUMMY'
)
INSERT
INTO AUDIT
(targettable, Event_A, host ,
appli , loginname, spid,
sqltext)
SELECT targettable = 'CollectionMembers' , Event_A
= @operation
, host = HOST_NAME() , app =
APP_NAME() , suser = suser_name()
, spid = @@SPID , sqltext = EventInfo
FROM #DBCC
END
GO
|
audittime
|
targettable
|
Event_A
|
host
|
appli
|
loginname
|
spid
|
sqltext
|
|
2014-12-04 14:24:32.930
|
CollectionMembers
|
DELETE
|
W-APP-12
|
SMS_COLLECTION_EVALUATOR_PRIMARY_EVALUATOR
|
AUTORITE NT\Système
|
847
|
exec dbo.sp_TransferMembership '001000FB', 0
|
1.9
T-SQL
Sum-UP from
2 tables
select sum(result) from
(
SELECT count(*) AS result from [DB].[SCH].[T1]
UNION ALL
SELECT count(*) AS result from [DB].[SCH].[T2]
) AS Q1
1.10
Copy table with xml column OPENQUERY
SELECT a.*
FROM OPENROWSET('SQLNCLI',
'Server=Seattle1;Trusted_Connection=yes;',
'SELECT GroupName, Name, DepartmentID,
CAST(MyXML as VARCHAR(MAX))
FROM AdventureWorks.HumanResources.Department
ORDER BY GroupName, Name') AS a;
set language
english
SELECT *
INTO vSites FROM [S1_SCCM_APP-12].CM_001.dbo.vSites where 1=2 ;
-- Msg 9514, Niveau 16, État 1,
Ligne 1
-- Xml data type is not supported in
distributed queries. Remote object 'S1_SCCM_APP-12.CM_001.dbo.vSites' has xml
column(s).
SELECT *
FROM OPENQUERY([S1_SCCM_APP-12], 'SELECT Cast(XMLcol4 as Varchar(max)) FROM
CM_001.dbo.vSites');
-- Msg 9514, Niveau 16, État 1,
Ligne 1
-- Xml data type is not supported in
distributed queries. Remote object 'OPENQUERY' has xml column(s).
DECLARE
@TSQL varchar(8000)
SELECT @TSQL = 'insert into Database.dbo.Table_Local
(col1, col2, col3,
XMLcol4)
select col1,
col2, col3, Cast(t2.XMLcol4 as XML) as XMLcol4
from
OPENQUERY([LINKED_SERVER_NAME],''SELECT col1, col2, col3,
Cast(XMLcol4
as Varchar(max)) as XMLcol4
FROM
DATABASE.dbo.Table_Remote
where col1 > ''''' + @maxid +
''''''') t2'
EXEC (@TSQL)
-- Go through a View
SELECT
* INTO vSites FROM [S1_SCCM_APP-12].CM_001.dbo.vSites_auc
-- (1 ligne(s) affectée(s))
use CM_001
create view
dbo.vSites_auc as SELECT [SiteKey]
,[SiteCode]
,[SiteName]
,[ReportToSite]
,[Status]
,[DetailedStatus]
,[TimeStamp]
,[SomeId]
,[SiteType]
,[BuildNumber]
,[Version]
,[SiteServer]
,[DefaultMP]
,[DefaultMPType]
,[DefaultMPSslState]
,[LocaleID]
,[ReplicatesReservedRanges]
,[Mode]
FROM CM_001.dbo.vSites
drop view dbo.vSites_auc
1.10
Find the tables containing column with a given string, print Table , Column
USE SP_Content_Billing
DECLARE
@search_string VARCHAR(100),
@table_name SYSNAME,
@table_schema SYSNAME,
@column_name SYSNAME,
@sql_string VARCHAR(2000)
SET @search_string = 'CD13_F'
DECLARE tables_cur CURSOR
FOR SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables_cur
FETCH NEXT
FROM tables_cur INTO @table_schema, @table_name
WHILE (@@FETCH_STATUS = 0)
BEGIN
DECLARE
columns_cur CURSOR FOR SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = @table_schema AND TABLE_NAME = @table_name AND COLLATION_NAME IS NOT NULL --
Only strings have this and they always have it
OPEN
columns_cur
FETCH
NEXT FROM columns_cur INTO @column_name
WHILE
(@@FETCH_STATUS = 0)
BEGIN
SET @sql_string = 'IF
EXISTS (SELECT * FROM ' +
QUOTENAME(@table_schema) + '.'
+ QUOTENAME(@table_name) +
' WHERE ' + QUOTENAME(@column_name) +
' LIKE ''%' + @search_string + '%'') PRINT ''' + QUOTENAME(@table_schema) + '.'
+ QUOTENAME(@table_name) +
', ' + QUOTENAME(@column_name) + ''''
EXECUTE(@sql_string)
FETCH NEXT FROM
columns_cur INTO
@column_name
END
CLOSE
columns_cur
DEALLOCATE
columns_cur
FETCH
NEXT FROM tables_cur INTO @table_schema, @table_name
END
CLOSE tables_cur
DEALLOCATE tables_cur
2] Schema
SQL Server equivalent to Oracle « alter session set
current_schema= SCH ; »
use DB1
select count(1) from [SCH1].T1
EXECUTE AS USER = 'SCH1'
select count(1) from T1
2.0
Schemas / DB Decsription
-- list number of objects per schema name after use
<db>
select b.name ,
count(*) NB_OBJ from sys.objects a ,
sys.schemas
b where a.schema_id=b.schema_id group by b.name ;
-- name NB_OBJ
-- dbo 14
-- sys 50
-- RAED_USER 5
-- schémas
CREATE TABLE #ora_schemas (DBName VARCHAR(50)
NOT NULL, nb_objects int
NOT NULL)
-- 2 -
Populate temp tables
DECLARE @DBName VARCHAR(50)
declare @SQL varchar(max);
declare @SQLWithDB varchar(max);
DECLARE db_cursor CURSOR FOR
SELECT name FROM
master.dbo.sysdatabases WHERE name NOT
IN ('master','msdb','tempdb','model')
OPEN db_cursor
FETCH NEXT FROM
db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL ='select
DB_NAME(),count(1) from sys.objects ;'
SET @SQLWithDB = 'USE
[' + @DBName + '];'
+ CHAR(10) + CHAR(13) +
@SQL
-- print @query
INSERT INTO #ora_schemas(DBName, nb_objects)
exec(@SQLWithDB)
FETCH NEXT FROM db_cursor
INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor
select a.name , SUSER_SNAME(owner_sid) as
Owner, convert(varchar(10),create_date,101)
Create_Date , state_desc State,
recovery_model_desc Recovery , snapshot_isolation_state_desc
, compatibility_level ,
( SELECT ceiling(SUM(size*8)/1024.) Total_mdf_ldf_MB_sz
FROM sys.master_files c where a.database_id = c.database_id ) size_mb
,
( SELECT b.nb_objects FROM #ora_schemas
b where a.name = b.DBName ) nb_objects
from sys.databases a where
a.database_id
> 4
order by 2
drop table #ora_schemas
go
|
DB
|
owner
|
create_date
|
state
|
recovery
|
snap_iso
|
compat
|
size_mb
|
nb_objects
|
|
DB_01
|
sa
|
02/10/2014
|
ONLINE
|
SIMPLE
|
ON
|
100
|
61
|
133
|
|
DB_02
|
sa
|
02/10/2014
|
ONLINE
|
SIMPLE
|
ON
|
100
|
471
|
163
|
2.1
Creation
IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name =
'exe'))
BEGIN
EXEC
('CREATE SCHEMA [exe] AUTHORIZATION [dbo]')
END
2.2
How to change schema name
ALTER SCHEMA exe TRANSFER dbo.Emp_Tab
SELECT 'ALTER SCHEMA NewSchema TRANSFER ' +
TABLE_SCHEMA + '.' + TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE
TABLE_SCHEMA = 'OldSchema'
3] Database
3.0
Database Access Mode
How to see
Database mode
SELECT user_access_desc
FROM sys.databases WHERE name =
'ETL'
How to set
DB mode
ALTER DATABASE [Works] SET MULTI_USER WITH NO_WAIT
ALTER DATABASE [Works] SET SINGLE_USER WITH NO_WAIT
3.1
Creation
-- =============================================
-- Create
database template
--
=============================================
USE master
GO
CREATE DATABASE O13
GO
use O13
GO
sp_changedbowner 'sa'
ALTER DATABASE O13
SET RECOVERY SIMPLE;
GO
3.2
Drop
USE master
GO
-- Drop the
database if it already exists
IF
EXISTS (
SELECT name
FROM sys.databases
WHERE name =
N'O13'
)
DROP DATABASE O13
GO
3.3
How to drop a database that does not exist
R2H DB was Compatible
6.5 in a SQL Server 9.0 – it was impossible to drop :
USE R2H
GO
Could not locate entry in sysdatabases for database 'R2H'. No
entry found with that name. Make sure that the name is entered correctly.
USE MASTER
DROP DATABASE R2H
GO
Cannot drop the database 'R2H', because it does not exist or
you do not have permission.
From
Internet : Solved it! SQL2000 allowed me to create another new database
with the same name as my troubled DB, then I deleted it.
USE MASTER
GO
DROP DATABASE R2H
GO
=> OK
3.4
Change Size and Filegrowth from % to MB on every DBs
-- increase every log size to 64 MB
DECLARE @name VARCHAR(64) -- database name
declare @lnam varchar(512)
declare @query varchar(max)
DECLARE db_cursor CURSOR
FOR
SELECT a.name
, b.name FROM master.dbo.sysdatabases
a, sys.master_files b
WHERE a.name
NOT IN ('master','msdb','tempdb','model') and a.name
= DB_NAME(b.database_id) ;
OPEN db_cursor
FETCH NEXT
FROM db_cursor INTO @name , @lnam
WHILE @@FETCH_STATUS
= 0
BEGIN
SELECT @query ='ALTER DATABASE '+@name+' MODIFY FILE
(NAME = '+@lnam+', SIZE = 64 MB) ;'
print @query
exec(@query)
SELECT @query ='ALTER DATABASE '+@name+' MODIFY FILE
(NAME = '+@lnam+', FILEGROWTH = 64 MB) ;'
print @query
exec(@query)
FETCH NEXT FROM
db_cursor INTO @name , @lnam
END
CLOSE db_cursor
DEALLOCATE db_cursor
ALTER DATABASE SHPPP_ConfigDB MODIFY
FILE (NAME = SHPPP_ConfigDB, SIZE = 64 MB) ;
ALTER DATABASE SHPPP_ConfigDB MODIFY
FILE (NAME = SHPPP_ConfigDB, FILEGROWTH = 64 MB) ;
ALTER DATABASE SHPPP_ConfigDB MODIFY
FILE (NAME = SHPPP_ConfigDB_log, SIZE = 64 MB) ;
ALTER DATABASE SHPPP_ConfigDB MODIFY
FILE (NAME = SHPPP_ConfigDB_log, FILEGROWTH = 64 MB) ;
3.4b
SQL Server Move DB Files
ALTER DATABASE StateService SET offline
ALTER DATABASE StateService MODIFY FILE ( NAME =
StateService, FILENAME = "G:\ISS_Logs\StateService.mdf")
ALTER DATABASE StateService MODIFY FILE ( NAME =
StateService_log, FILENAME =
"F:\ISS_Data\MSSQL12.IOSPP\MSSQL\DATA\StateService_log.ldf")
ALTER DATABASE StateService SET online
3.5
Change DBOWNER
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);
declare DB cursor
fast_forward for
SELECT name FROM
sys.databases
where name not
in ('model','tempdb','master') and ( SUSER_SNAME(owner_sid) is
null or SUSER_SNAME(owner_sid) !='sa' )
open DB
fetch next from
DB into @dbName
while @@FETCH_STATUS = 0
begin
SET @sql =
N'exec sp_changedbowner ''''sa''''';
SET @BigSQL = N'USE
[' + @dbName + '];
EXEC sp_executesql N''' +
@sql + '''';
print @BigSQL
EXEC (@BigSQL)
fetch next from
DB into @dbName
end
close DB
deallocate DB
USE [BASE_ADMIN]; EXEC sp_executesql
N'exec sp_changedbowner ''sa'''
USE [BASE_BACKOFFICE]; EXEC
sp_executesql N'exec sp_changedbowner ''sa'''
USE [DB01]; EXEC sp_executesql N'exec
sp_changedbowner ''sa'''
USE [BASE_CONF04]; EXEC sp_executesql
N'exec sp_changedbowner ''sa'''
USE [DBSCHEMA05]; EXEC sp_executesql
N'exec sp_changedbowner ''sa'''
USE [<DB_1>]; EXEC
sp_executesql N'exec sp_changedbowner ''sa'''
USE [BASE_BASE04]; EXEC sp_executesql
N'exec sp_changedbowner ''sa'''
USE [BASE_DATASCHEMA05]; EXEC
sp_executesql N'exec sp_changedbowner ''sa'''
USE [BASE_DATA40000]; EXEC
sp_executesql N'exec sp_changedbowner ''sa'''
3.6
Change Compatibility Level
After a
Restore from previous version.
sp_dbcmptlevel 'BASE_BACKOFFICE', '110'
-- changing from 2008R2 ( 100 ) to
2012 ( 110 )
3.7
Change DB Name
ALTER DATABASE
BASE_CONF04 MODIFY NAME =
BASE_CONF04
3.8
Change DB Configuration
--BASE_BASE04
set @DATABASE='BASE_BASE04'
set @COMMAND='use master
if exists (select * from
master.dbo.sysdatabases where name='''+@DATABASE+''')
begin
print ''Configuration de la base '+@DATABASE+'
''
alter database '+@DATABASE+' set
AUTO_CLOSE off,AUTO_CREATE_STATISTICS on,
AUTO_UPDATE_STATISTICS on,AUTO_SHRINK
off,RECOVERY SIMPLE,AUTO_CLOSE off,ALLOW_SNAPSHOT_ISOLATION ON'+char(13)+
'alter database '+@DATABASE+' set
READ_COMMITTED_SNAPSHOT ON'+char(13)+'
End
else print ''La base '+@DATABASE+' n''''existe
pas, impossible de la configurer.
'''
print' param. base BASE_DATASCHEMA05
AUTO_CLOSE off
AUTO_CREATE_STATISTICS on
AUTO_UPDATE_STATISTICS on
AUTO_SHRINK off
RECOVERY SIMPLE
AUTO_CLOSE off
ALLOW_SNAPSHOT_ISOLATION ON
READ_COMMITTED_SNAPSHOT ON
'
EXEC sp_executesql @COMMAND
3.9
Identify/Change DB Collation
select SERVERPROPERTY ( 'collation' )
USE Master
GO
SELECT NAME,
COLLATION_NAME FROM sys.Databases ORDER
BY DATABASE_ID ASC
GO
Change
USE master;
GO
ALTER DATABASE MyOptionsTest
COLLATE French_CI_AS ;
GO
3.8
Database Integrity Corruption
Sharepoint
2013 CONFIG DB is corrupted.
I got those
message when trying to join a new App server to the Farm :
Error 7886, Severity 20, State 2
A read operation on a large object failed while sending data to the
client. A common cause for this is if the application is running in READ
UNCOMMITTED isolation level. This connection will be terminated.
dbcc checkdb ('CONFIG')
CHECKDB
found 0 allocation errors and 4 consistency errors in database 'Config'.
repair_allow_data_loss
is the minimum repair level for the errors found by DBCC CHECKDB (Config).
Msg 8914, Level 16, State 1, Line 37
Incorrect PFS free space information for page
(1:34859) in object ID 389576426, index ID 1, partition ID 72057594044481536,
alloc unit ID 72057594046447616 (type LOB data). Expected value 100_PCT_FULL,
actual value 50_PCT_FULL.
Msg 8964, Level 16, State 1, Line 37
Table error: Object ID 389576426, index ID 1,
partition ID 72057594044481536, alloc unit ID 72057594046447616 (type LOB
data). The off-row data node at page (1:34859), slot 0, text ID 32146653184 is
not referenced.
Msg 8965, Level 16, State 1, Line 37
Table error: Object ID 389576426, index ID 1,
partition ID 72057594044481536, alloc unit ID 72057594046447616 (type LOB
data). The off-row data node at page (1:34859), slot 1, text ID 32146653184 is
referenced by page (1:1634), slot 0, but was not seen in the scan.
Msg 8929, Level 16, State 1, Line 37
Object ID 389576426, index ID 1, partition ID
72057594044481536, alloc unit ID 72057594050576384 (type In-row data): Errors
found in off-row data with ID 32146653184 owned by data record identified by
RID = (1:5549:14)
ALTER DATABASE CONFIG SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;
dbcc checkdb ('CONFIG',REPAIR_REBUILD)
ALTER DATABASE CONFIG SET MULTI_USER;
DBCC
results for 'Objects'.
Msg 8914,
Level 16, State 1, Line 40
Incorrect
PFS free space information for page (1:34859) in object ID 389576426, index ID
1, partition ID 72057594044481536, alloc unit ID 72057594046447616 (type LOB
data). Expected value 100_PCT_FULL, actual value 50_PCT_FULL.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8964,
Level 16, State 1, Line 40
Table
error: Object ID 389576426, index ID 1, partition ID 72057594044481536, alloc
unit ID 72057594046447616 (type LOB data). The off-row data node at page
(1:34859), slot 0, text ID 32146653184 is not referenced.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8965,
Level 16, State 1, Line 40
Table
error: Object ID 389576426, index ID 1, partition ID 72057594044481536, alloc
unit ID 72057594046447616 (type LOB data). The off-row data node at page
(1:34859), slot 1, text ID 32146653184 is referenced by page (1:1634), slot 0,
but was not seen in the scan.
The repair level on the DBCC statement caused this repair to be bypassed.
Msg 8929,
Level 16, State 1, Line 40
Object ID
389576426, index ID 1, partition ID 72057594044481536, alloc unit ID
72057594050576384 (type In-row data): Errors found in off-row data with ID
32146653184 owned by data record identified by RID = (1:5549:14)
The repair level on the DBCC statement caused this repair to be bypassed.
REPAIR
ALTER DATABASE CONFIG SET SINGLE_USER WITH ROLLBACK
IMMEDIATE;
dbcc checkdb ('CONFIG',REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE CONFIG SET MULTI_USER;
Msg 8945, Level 16, State 1, Line
42
Table error: Object ID 389576426,
index ID 1 will be rebuilt.
The error has been repaired.
Msg 8914, Level 16, State 1, Line 42
Incorrect PFS free space information for page
(1:34859) in object ID 389576426, index ID 1, partition ID 72057594044481536,
alloc unit ID 72057594046447616 (type LOB data). Expected value 100_PCT_FULL,
actual value 50_PCT_FULL.
The error has been repaired.
Msg 8964, Level 16, State 1, Line
42
Table error: Object ID 389576426,
index ID 1, partition ID 72057594044481536, alloc unit ID 72057594046447616
(type LOB data). The off-row data node at page (1:34859), slot 0, text ID
32146653184 is not referenced.
The error has been repaired.
Msg 8965, Level 16, State 1, Line
42
Table error: Object ID 389576426,
index ID 1, partition ID 72057594044481536, alloc unit ID 72057594046447616
(type LOB data). The off-row data node at page (1:34859), slot 1, text ID
32146653184 is referenced by page (1:1634), slot 0, but was not seen in the
scan.
The error has been repaired.
Msg 8929, Level 16, State 1, Line
42
Object ID 389576426, index ID 1,
partition ID 72057594044481536, alloc unit ID 72057594050576384 (type In-row
data): Errors found in off-row data with ID 32146653184 owned by data record
identified by RID = (1:5549:14)
The error has been repaired.
Msg 8945, Level 16, State 1, Line
42
Table error: Object ID 389576426,
index ID 2 will be rebuilt.
The error has been repaired.
Msg 8945, Level 16, State 1, Line
42
Table error: Object ID 389576426,
index ID 3 will be rebuilt.
The error has been repaired.
CHECKDB found 0 allocation errors and 4
consistency errors in database 'Config'.
CHECKDB fixed 0 allocation errors and 4
consistency errors in database 'Config'.
DBCC execution completed. If DBCC printed
error messages, contact your system administrator.
dbcc checkdb ('CONFIG')
CHECKDB found 0 allocation errors and 0 consistency errors in database 'Config'.
3.9
DB Snapshot
Find Snap
SELECT DB_NAME(sd.source_database_id) AS
[SourceDatabase],
sd.name AS [Snapshot],
mf.name AS [Filename],
size_on_disk_bytes/1024 AS [size_on_disk (KB)],
mf2.size/128
AS [MaximumSize (MB)]
FROM sys.master_files mf
JOIN sys.databases sd
ON
mf.database_id = sd.database_id
JOIN sys.master_files mf2
ON
sd.source_database_id = mf2.database_id
AND
mf.file_id = mf2.file_id
CROSS APPLY
sys.dm_io_virtual_file_stats(sd.database_id, mf.file_id)
WHERE mf.is_sparse
= 1
AND mf2.is_sparse
= 0
ORDER BY
1;
-- sql
server find the used size of a db snapshot file
select Cast(Cast(BytesOnDisk as decimal(19,3))/1024/1024 as decimal(19,3))
as BytesOnDiskMB, D.[name] as DBName, S.FileID, S.NumberReads, S.BytesRead,
S.IoStallWriteMS, S.BytesOnDisk, GetDate() AS CurrentTime, 'Before'
as Stage
from fn_virtualfilestats(db_id('SP_Content_Billing_20170322_190045_7346384'),null) S
inner join
dbo.sysdatabases D on D.dbid = S.dbid
4] Accounts
-- list of
created users
SELECT COUNT(1)
FROM sys.server_principals where type_desc in ('SQL_LOGIN','WINDOWS_LOGIN','WINDOWS_GROUP')
4.1
Instance Wise
DECLARE @SQLCMDDBUser
NVARCHAR(MAX)
IF
object_id('tempdb..#DBUserTable','U') IS not
NULL DROP TABLE
#DBUserTable
CREATE
TABLE #DBUserTable
([ServerName] [sysname] NOT NULL,
[DBName]
[sysname] NOT NULL,
[UserName]
[sysname] NOT NULL,
[MappedLogin]
[nvarchar](128) NULL,
-- dp_sid
varbinary(85),
[UserType]
[nvarchar](60) NOT
NULL,
[CreateDate]
[datetime] NULL,
[ModifyDate]
[datetime] NULL,
[IsOrphan]
[int] NOT NULL)
SELECT
@SQLCMDDBUser=coalesce(@SQLCMDDBUser,'')
+ CHAR(13) + CHAR(10) +
' use ' + QUOTENAME([name]) +
';
INSERT INTO
#DBUserTable
-- no use db ,
db.sys.database_principals instead
-- use SHPPP_ProjectServer_PWA_Archive
SELECT @@ServerName As
ServerName,DB_NAME() As DBName,DP.name As UserName,SP.name As MappedLogin,
DP.type_desc AS
UserType,DP.create_date As CreateDate,DP.modify_date As ModifyDate,
Case WHEN SP.sid Is
Null THEN 1 ELSE 0 END As IsOrphan -- for AD account known from AD Groups
FROM
sys.database_principals DP LEFT JOIN master.sys.server_principals SP ON DP.sid
= SP.sid
WHERE DP.type NOT IN
(''R'')
AND DP.is_fixed_role
<> 1 AND DP.sid NOT IN (0x01,0x00) AND DP.sid IS NOT NULL AND DP.name NOT
LIKE ''##%'' AND DP.name != ''dbo''
union
select
ServerName,DBName,UserName,SUSER_SNAME(DB.owner_sid) As
MappedLogin,UserType,CreateDate,ModifyDate,IsOrphan from
(
SELECT
@@ServerName As ServerName,DB_NAME()
As DBName,DP.name As UserName,
-- SUSER_SNAME(DP.sid)
As MappedLogin, -- KO : sys.database_principals SID is different than
sys.databases
-- DP.sid as dp_sid, --
sys.database_principals SID is different than sys.databases
--SP.name As
MappedLogin,
DP.type_desc AS
UserType,DP.create_date As CreateDate,DP.modify_date As ModifyDate,
Case WHEN SP.sid Is
Null THEN 1 ELSE 0 END As IsOrphan -- for AD account known from AD Groups
FROM
sys.database_principals DP LEFT JOIN master.sys.server_principals SP ON DP.sid
= SP.sid
WHERE DP.type NOT IN
(''R'') and DP.name = ''dbo''
AND DP.is_fixed_role
<> 1 AND DP.sid NOT IN (0x01,0x00) AND DP.sid IS NOT NULL AND DP.name NOT
LIKE ''##%''
) SEL1 LEFT JOIN
sys.databases DB ON SEL1.DBName = DB.name
ORDER BY
DBName,UserName
' FROM master.sys.databases WHERE name NOT
IN ('tempdb','model') AND state_desc
= 'ONLINE' ORDER BY name
EXECUTE(@SQLCMDDBUser)
SELECT
* FROM #DBUserTable
DROP TABLE
#DBUserTable
|
ServerName
|
DBName
|
UserName
|
MappedLogin
|
UserType
|
CreateDate
|
ModifyDate
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_PMO
|
BUILTIN\Administrateurs
|
BUILTIN\Administrateurs
|
WINDOWS_GROUP
|
2013-01-22 16:56:22.930
|
2013-01-22 16:56:22.930
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_PMO
|
dbo
|
DOMN\USER_1
|
WINDOWS_USER
|
2003-04-08 09:10:42.287
|
2012-07-13 10:09:33.223
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_PMO
|
DOMN\SHPProjectSvc
|
DOMN\SHPProjectSvc
|
WINDOWS_USER
|
2012-08-01 15:35:17.810
|
2012-08-01 15:35:17.810
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_PMO
|
DOMN\SHPServicesAppTech
|
DOMN\SHPServicesAppTech
|
WINDOWS_USER
|
2012-08-01 15:35:16.500
|
2012-08-01 15:35:16.500
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_PMO
|
DOMN\SHPWebAppTech
|
DOMN\SHPWebAppTech
|
WINDOWS_USER
|
2012-07-13 10:09:56.537
|
2012-07-13 10:09:56.537
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_TestDPM
|
dbo
|
DOMN\SHPAdminTech
|
WINDOWS_USER
|
2003-04-08 09:10:42.287
|
2013-01-07 15:21:34.223
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_Content_TestDPM
|
DOMN\SHPWebAppTech
|
DOMN\SHPWebAppTech
|
WINDOWS_USER
|
2013-01-07 15:22:05.927
|
2013-01-07 15:22:05.927
|
|
W-ISSSHPPP-1\SHPPP
|
SHPPP_GCG
|
dbo
|
DOMN\SHPAdminTech
|
WINDOWS_USER
|
2003-04-08 09:10:42.287
|
2013-11-05 11:08:52.873
|
SELECT *
FROM #DBUserTable
where MappedLogin is NULL
When
MappedLogin is NULL , it is an Orphaned User needing :
(
note : it’s better to use the EXEC sp_change_users_login
'Report'; in a Database to list the Orphaned Users ).
USE [DB_NAME]
GO
EXEC sp_change_users_login 'Update_One', 'MYLOGIN', 'MYLOGIN'
GO
USE [R2H]
GO
EXEC
sp_change_users_login 'Report';
GO
-> fix :
Mapping a database
user to a new SQL Server login
--Create the new
login.
CREATE LOGIN MaryB
WITH PASSWORD = '982734snfdHHkjj3';
GO
--Map database
user MB-Sales to login MaryB.
USE
AdventureWorks2012;
GO
EXEC
sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';
GO
Automatically
mapping a user to a login, creating a new login if it is required
USE
AdventureWorks2012;
GO
EXEC
sp_change_users_login 'Auto_Fix', 'Mary', NULL, 'B3r12-3x$098f6';
GO
List every
Mapped Database for a given SQL Login :
ð
Just modify the previous
Select :
SELECT *
FROM #DBUserTable where Username =
'MYLOGIN'
4.2
DB Wise
use SHPPP_ProjectServer_PWA_Archive
SELECT @@ServerName
As ServerName,DB_NAME() As DBName,DP.name As UserName,SP.name As MappedLogin,
DP.type_desc AS UserType,DP.create_date As CreateDate,DP.modify_date As ModifyDate,
Case
WHEN SP.sid Is Null
THEN 1 ELSE 0 END As
IsOrphan -- for AD account known from AD Groups
FROM
sys.database_principals
DP LEFT JOIN
master.sys.server_principals SP ON
DP.sid
= SP.sid
WHERE
DB_Name() NOT IN ('model','tempdb') AND
DP.type
NOT IN ('R')
AND
DP.is_fixed_role
<> 1 AND DP.sid NOT IN (0x01,0x00) AND DP.sid IS
NOT NULL AND
DP.name
NOT LIKE '##%'
AND DP.name != 'dbo'
union
select
ServerName,DBName,UserName,SUSER_SNAME(DB.owner_sid) As
MappedLogin,UserType,CreateDate,ModifyDate,IsOrphan
from
(
SELECT
@@ServerName
As ServerName,DB_NAME() As DBName,DP.name As UserName,
--
SUSER_SNAME(DP.sid) As MappedLogin, -- KO : sys.database_principals SID is
different than sys.databases
--
DP.sid as dp_sid, --
sys.database_principals SID is different than sys.databases
--SP.name
As MappedLogin,
DP.type_desc AS UserType,DP.create_date As CreateDate,DP.modify_date As ModifyDate,
Case
WHEN SP.sid Is Null
THEN 1 ELSE 0 END As
IsOrphan -- for AD account known from AD Groups
FROM
sys.database_principals
DP LEFT JOIN
master.sys.server_principals SP ON
DP.sid
= SP.sid
WHERE
DB_Name() NOT IN ('model','tempdb') AND
DP.type
NOT IN ('R')
and DP.name = 'dbo'
AND
DP.is_fixed_role
<> 1 AND DP.sid NOT IN (0x01,0x00) AND DP.sid IS
NOT NULL AND
DP.name
NOT LIKE '##%'
)
SEL1 LEFT JOIN
sys.databases
DB ON SEL1.DBName = DB.name
ORDER
BY DBName,UserName
4.3
Change DBOwner
Modify DBO
when it is linked to an account of a physical user ( the DB creator ) that may
disapear ( removed when from external IT company … )
Change dbo
to sa for every DBs
select a.name , SUSER_SNAME(owner_sid) as Owner from sys.databases a where a.name not in ('model','tempdb')
and ( SUSER_SNAME(owner_sid) is null or SUSER_SNAME(owner_sid) like '%DOMN%EXT%' )
order by 1,2
use SHPQR_ASPSessionServices
go
EXEC sp_changedbowner 'sa'
go
DECLARE @sql nvarchar(4000);
DECLARE @BigSQL nvarchar(4000);
DECLARE @dbName varchar(100);
declare DB cursor fast_forward for
SELECT name FROM sys.databases
where name not in ('model','tempdb') and ( SUSER_SNAME(owner_sid) is null or SUSER_SNAME(owner_sid) like '%DOMN%EXT%' )
open DB
fetch next from DB into @dbName
while @@FETCH_STATUS = 0
begin
SET @sql = N'exec
sp_changedbowner ''''sa''''';
SET @BigSQL = N'USE [' + @dbName + ']; EXEC sp_executesql N''' + @sql + '''';
print @BigSQL
-- EXEC (@BigSQL)
fetch next from DB into @dbName
end
close DB
deallocate DB
USE [SHPPP_ConfigDB];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_CentralAdminContent];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_StateService];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_UsageandHealthDataCollection];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_SearchServiceApplication];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_SearchServiceApplication_PropertyStore];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_SearchServiceApplication_CrawlStore];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_ManagedMetadataService];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_Content_PMO];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE
[SessionStateService_f267dbf4ffaf435da64c6566523bf68e]; EXEC sp_executesql
N'exec sp_changedbowner ''sa'''
USE [SHPPP_ProjectServer_PWA_Archive];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_ProjectServer_PWA_Draft];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_ProjectServer_PWA_Published];
EXEC sp_executesql N'exec sp_changedbowner ''sa'''
USE [SHPPP_ProjectServer_PWA_Reporting]; EXEC
sp_executesql N'exec sp_changedbowner ''sa'''
4.4
Example : create user with READ rights
4.4.1
create a user for Read-only purpose
use master
CREATE LOGIN
APP1_APP2_R WITH PASSWORD = 'psw'
, CHECK_POLICY = OFF
;
GO
USE DB1
GO
CREATE USER
[APP1_APP2_R] FOR LOGIN [APP1_APP2_R] WITH
DEFAULT_SCHEMA=[dbo]
GO
ALTER USER
[APP1_APP2_R] WITH LOGIN = [APP1_APP2_R]
GO
EXEC sp_addrolemember 'db_datareader','APP1_APP2_R'
GO
4.4.2
create a user for Read-only purpose and owning a View
We create a
DB DB_READ_USER to host a user owning a view to access other DB/Objects.
After DB
Creation :
-
As an Admin user : create login
and user
> Login
use master
CREATE LOGIN READ_USER WITH PASSWORD = 'RU_PSW' ,
CHECK_POLICY = OFF ;
map user to OTHER_DB
for READ privilege under Studio GUI ( 'db_datareader' )
or use
T-SQL :
USE OTHER_DB
GO
CREATE USER
[READ_USER] FOR LOGIN [READ_USER] WITH
DEFAULT_SCHEMA=[dbo]
GO
EXEC sp_addrolemember 'db_datareader','READ_USER'
GO
> Then
create User
use DB_READ_USER
CREATE USER
[READ_USER] FOR LOGIN [READ_USER];
grant create
view to [READ_USER]
;
GRANT CREATE
schema TO [READ_USER] WITH GRANT
OPTION;
Add a
Function execution right in the target DB :
GRANT EXECUTE ON [OTHER_DB].[SCHEMA04].GET_PATH_LOC TO
[READ_USER]
-
Connected As the created user
-- as READ_USER :
use DB_READ_USER
CREATE SCHEMA
[READ_USER]
--ALTER
USER [READ_USER] WITH DEFAULT_SCHEMA =[READ_USER]
create view [READ_USER].COMPUTER_DETAILS
as
select sy.Name0 as Nom, sy.Operating_System_Name_and0
as OS, mem.Capacity0 as Mémoire, pro.Name0 as Processeur
from [CM_001].[dbo].v_R_system sy
…
select * from [READ_USER].ENTITY_COMPUTER_DETAILS
4.5
Reverse Engineering to get a SQL script of SQL Server Logins
Thanks to
http://www.databasejournal.com/features/mssql/article.php/3922256/Re-generating-SQL-Server-Logins.htm
Pre-requisite
---------------------------------------
-- Stored Procedure sp_hexadecimal
---------------------------------------
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT
NULL
DROP
PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE
sp_hexadecimal
@binvalue
varbinary(256),
@hexvalue
varchar (514) OUTPUT
AS
DECLARE
@charvalue varchar (514)
DECLARE
@i int
DECLARE
@length int
DECLARE
@hexstring char(16)
SELECT
@charvalue = '0x'
SELECT
@i = 1
SELECT
@length = DATALENGTH (@binvalue)
SELECT
@hexstring = '0123456789ABCDEF'
WHILE
(@i <= @length)
BEGIN
DECLARE @tempint int
DECLARE @firstint int
DECLARE @secondint int
SELECT @tempint = CONVERT(int,
SUBSTRING(@binvalue,@i,1))
SELECT @firstint = FLOOR(@tempint/16)
SELECT @secondint = @tempint
- (@firstint*16)
SELECT @charvalue = @charvalue
+
SUBSTRING(@hexstring, @firstint+1,
1) +
SUBSTRING(@hexstring, @secondint+1,
1)
SELECT @i =
@i + 1
END
SELECT @hexvalue
= @charvalue
GO
Reverse on a login = MYLOGIN
----------------------------------------------
--Login
Pre-requisites
----------------------------------------------
USE master
go
SET NOCOUNT ON
DECLARE @login_name varchar(100)
SET @login_name = 'MYLOGIN'
IF NOT EXISTS (SELECT
* FROM sys.server_principals WHERE name =
@login_name AND type IN ('G','U','S'))
BEGIN
PRINT 'Please
input valid login name'
RETURN
END
DECLARE @login_sid varbinary(85)
SELECT @login_sid = sid
FROM sys.server_principals WHERE name =
@login_name
DECLARE @maxid int
IF OBJECT_ID('tempdb..#db_users') is not
null
DROP TABLE #db_users
-- 2005 -
2008
-- SELECT id
= identity(int,1,1), sql_cmd = 'SELECT '''+name+''', * FROM
['+name+'].sys.database_principals' INTO #db_users FROM sys.sysdatabases
-- 2012
SELECT id =
identity(int,1,1), sql_cmd
= 'SELECT '''+name+''', '+
'name,
principal_id,type,type_desc,default_schema_name,create_date,modify_date,owning_principal_id,sid,is_fixed_role'+ ' FROM ['+name+'].sys.database_principals' INTO #db_users
FROM sys.sysdatabases
SELECT @maxid = @@ROWCOUNT
---------------------------------------------
--Retrieve
hashed password and hashed sid
---------------------------------------------
IF EXISTS (SELECT * FROM
sys.server_principals
WHERE type =
'S' and name
= @login_name )
BEGIN
DECLARE @PWD_varbinary
varbinary (256)
SET @PWD_varbinary
= CAST( LOGINPROPERTY( @login_name, 'PasswordHash'
) AS varbinary
(256) )
DECLARE @SID_string varchar (514)
DECLARE @PWD_string
varchar (514)
EXEC sp_hexadecimal @PWD_varbinary, @PWD_string
OUT
EXEC sp_hexadecimal @login_sid, @SID_string OUT
END
--select
@SID_string
--select
@PWD_string
----------------------------------------------
--Login
Properties
----------------------------------------------
PRINT '----------------------------------------------'
PRINT '--SET Login Properties'
PRINT '----------------------------------------------'
DECLARE @login_sqlcmd varchar(1000)
SET @login_sqlcmd = ''
SELECT @login_sqlcmd = '--
LOGIN ['+@login_name+'] IS
'+case is_disabled
WHEN
1 THEN 'DISABLED' ELSE 'ENABLED'
END FROM sys.server_principals
WHERE name =
@login_name
IF EXISTS (SELECT * FROM
sys.sql_logins
WHERE name =
@login_name)
BEGIN
SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE
LOGIN '+ QUOTENAME(@login_name)+'
WITH
PASSWORD = ' + @PWD_string + '
HASHED, SID = ' +
@SID_string + ',
DEFAULT_DATABASE
= ['+default_database_name+']' FROM sys.server_principals WHERE name =
@login_name
SELECT @login_sqlcmd = @login_sqlcmd
+ ', CHECK_POLICY' + CASE
is_policy_checked
WHEN 0 THEN '=OFF'
ELSE '=ON' END
FROM sys.sql_logins WHERE name =
@login_name
SELECT @login_sqlcmd = @login_sqlcmd
+ ', CHECK_EXPIRATION' + CASE
is_expiration_checked
WHEN 0 THEN '=OFF'
ELSE '=ON' END
FROM sys.sql_logins WHERE name =
@login_name
SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'ALTER LOGIN ['+@login_name+']
WITH
DEFAULT_DATABASE = ['+default_database_name+']' FROM sys.server_principals WHERE name =
@login_name
END
ELSE
BEGIN
SELECT @login_sqlcmd = @login_sqlcmd+ char(10)+'CREATE LOGIN ' + QUOTENAME( @login_name ) +
' FROM WINDOWS WITH DEFAULT_DATABASE = [' + default_database_name + ']'
FROM sys.server_principals WHERE name =
@login_name
END
PRINT @login_sqlcmd
----------------------------------------------
--Grant
Server Role to login
----------------------------------------------
PRINT ''
PRINT '----------------------------------------------'
PRINT '--Grant Server Role to login '
PRINT '----------------------------------------------'
IF OBJECT_ID('tempdb..#srvrole') IS NOT
NULL
DROP TABLE #srvrole
CREATE TABLE #srvrole(ServerRole sysname, MemberName
sysname, MemberSID varbinary(85))
INSERT INTO [#srvrole]
EXEC sp_helpsrvrolemember
DECLARE @login_srvrole varchar(1000)
SET @login_srvrole = ''
IF EXISTS (SELECT 1 FROM #srvrole
WHERE[MemberName] = @login_name)
BEGIN
SELECT @login_srvrole = @login_srvrole
+ 'EXEC sp_addsrvrolemember '''+MemberName+''',
'''+ServerRole+''''+CHAR(10) FROM
#srvrole
WHERE [MemberName] =
@login_name
PRINT @login_srvrole
END
ELSE
BEGIN
PRINT 'Login
['+@login_name+'] is
not a member of any server level role'
END
---------------------------------------------------
--Find out
list of db that the login has access to
---------------------------------------------------
IF OBJECT_ID('tempdb..#alldb_users') is not
null
DROP TABLE #alldb_users
CREATE TABLE #alldb_users(
[dbname] [sysname] NOT NULL,
[name] [sysname] NOT NULL,
[principal_id] [int] NOT NULL,
[type] [char](1)
NOT NULL,
[type_desc] [nvarchar](60)
NULL,
[default_schema_name] [sysname] NULL,
[create_date] [datetime] NOT NULL,
[modify_date] [datetime] NOT NULL,
[owning_principal_id] [int] NULL,
[sid] [varbinary](85) NULL,
[is_fixed_role] [bit] NOT NULL
)
DECLARE @id int, @sqlcmd varchar(500)
SET @id =
1
WHILE @id <=@maxid
BEGIN
SELECT @sqlcmd = sql_cmd
FROM #db_users WHERE id =
@id
INSERT INTO #alldb_users
EXEC (@sqlcmd)
SET @id
= @id +
1
END
DELETE FROM #alldb_users
WHERE sid is
null
DELETE FROM #alldb_users
WHERE sid <>
@login_sid
--SELECT *
FROM #alldb_users
----------------------------------------------
--granting
database role to login
----------------------------------------------
PRINT ''
PRINT '----------------------------------------------'
PRINT '--Grant database role to login '
PRINT '----------------------------------------------'
IF OBJECT_ID('tempdb..#dbrole') is not
null
DROP TABLE #dbrole
create table #dbrole (dbname varchar(100),
dbrole varchar (100),
dbrole_member varchar(100),
sid
varbinary(85), default_schema_name
varchar(100), login_name
varchar(100), db_principal_id
int)
DECLARE @dbrole_sqlcmd varchar(max)
SET @dbrole_sqlcmd = ''
SELECT @dbrole_sqlcmd = @dbrole_sqlcmd
+ 'SELECT '''+dbname+''', c.name, b.name, b.sid,
b.default_schema_name, d.name,
b.principal_id as login_name
from ['+dbname+'].sys.database_role_members
a
inner join ['+dbname+'].sys.database_principals
b on a.member_principal_id = b.principal_id
inner join ['+dbname+'].sys.database_principals
c on a.role_principal_id = c.principal_id
left join
sys.server_principals d on b.sid = d.sid
'
from #alldb_users
--SELECT
@dbrole_sqlcmd
--PRINT
@dbrole_sqlcmd
INSERT INTO #dbrole
exec(@dbrole_sqlcmd)
--SELECT *
FROM #dbrole
DELETE FROM #dbrole
WHERE sid <>
@login_sid
ALTER TABLE #dbrole ADD
ID INT identity(1,1)
DECLARE @counter int,
@maxid2 int, @login_dbrole
varchar(max)
SELECT @maxid2 = MAX(ID)
FROM #dbrole
SET @counter = 1
--SELECT *
FROM #dbrole
IF NOT EXISTS (SELECT
* FROM #dbrole
)
BEGIN
PRINT '--Login ['+@login_name+'] is
not a member of any database level role'
return
END
WHILE @counter <= @maxid2
BEGIN
SELECT @login_dbrole
= 'USE ['+dbname+']
IF NOT EXISTS
(SELECT * FROM sys.database_principals WHERE name = '''+dbrole_member+''')
BEGIN
CREATE USER ['+dbrole_member+'] FOR LOGIN
['+login_name+']'+isnull
(' WITH
DEFAULT_SCHEMA=['+default_schema_name+']','')+'
END
ALTER USER ['+dbrole_member+'] WITH LOGIN
= ['+login_name+']
EXEC
sp_addrolemember '''+dbrole+''','''+dbrole_member+'''
' FROM #dbrole
WHERE ID =
@counter
SELECT @counter = @counter
+ 1
PRINT @login_dbrole
END
----------------------------------------------
--SET Login
Properties
----------------------------------------------
-- LOGIN [MYLOGIN]
IS ENABLED
CREATE LOGIN [MYLOGIN]
WITH PASSWORD =
0x0200F428276775AAE569D2D598536DA7B4447133203FDFC34EA05664B834C56D4E7961075CC685DAD7924F894D6F83B99EB31ED7D2F6F94814F060C4FAD380B077670FA40789
HASHED, SID = 0xBF5CED4797F64B4A8BCFE2211FF2ECD5,
DEFAULT_DATABASE
= [BASE_DATA50004], CHECK_POLICY=ON, CHECK_EXPIRATION=OFF
ALTER LOGIN [MYLOGIN]
WITH
DEFAULT_DATABASE = [MY_DB]
----------------------------------------------
--Grant Server
Role to login
----------------------------------------------
Login [MYLOGIN]
is not a member of any server level role
----------------------------------------------
--Grant
database role to login
----------------------------------------------
USE [MY_DB]
IF NOT EXISTS
(SELECT * FROM sys.database_principals WHERE name = 'MYLOGIN')
BEGIN
CREATE USER [MYLOGIN] FOR LOGIN [MYLOGIN]
WITH DEFAULT_SCHEMA=[dbo]
END
ALTER USER [MYLOGIN]
WITH LOGIN = [MYLOGIN]
EXEC
sp_addrolemember 'db_datareader','MYLOGIN'
USE [MY_DB_2]
IF NOT EXISTS
(SELECT * FROM sys.database_principals WHERE name = 'MYLOGIN')
BEGIN
CREATE USER [MYLOGIN] FOR LOGIN [MYLOGIN]
WITH DEFAULT_SCHEMA=[dbo]
END
ALTER USER [MYLOGIN]
WITH LOGIN = [MYLOGIN]
EXEC
sp_addrolemember 'db_datareader','MYLOGIN'
5] Miscellanous
5.1
T-SQL vs PL/SQL
ORACLE PL :
LIKE(CONCAT('%',(select
EMPLOYEE.E_MAIL FROM EMPLOYEE WHERE
EMPLOYEE.EMPLOYEE_ID =@EMPLOYEE_ID@))||'%')
=>
SQL T-SQL :
LIKE '%' + ISNULL( ( select EMPLOYEE.E_MAIL FROM EMPLOYEE WHERE EMPLOYEE.EMPLOYEE_ID
=@EMPLOYEE_ID@) , '') + '%'
5.2
Dynamic SQL with T-SQL
SELECT distinct 'select top 10 * from FEPDB.dbo.'+ a.name + '
order by ' +
b.name
+ ' desc' FROM sysobjects a ,
syscolumns b WHERE a.id=b.id and
b.name
like '%TIME%' order by 1
