Tag Archives: SQL Management

串接授權與拒絕

簡單範例:

use tempdb
create table t(c1 int)
create user u without login
create user u2 without login
grant select on t to u with grant option
exec(‘grant select on t to u2 as u’) as user=’u’
exec(‘select * from t’) as user=’u2′
deny select on t to u cascade
/*
Msg 229, Level 14, State 5, Line 18
結構描述 ‘dbo’,資料庫 ‘tempdb’,物件 ‘t’ 沒有 SELECT 權限。
*/
exec(‘select * from t’) as user=’u2′

drop table t
drop user u
drop user u2

透過 SSMS 可以看到授予者

image

讓一般 login 建立 job 時,可以採用 proxy

巧玫提的有趣的問題,答案是需要呼叫 dbo.sp_grant_login_to_proxy,簡單的練習如下:

create login test with password=’abc’, check_expiration=off,check_policy=off

create credential abc with identity=N’sql2014\a’, secret=N’a’

use msdb

create user test for login test

alter role SQLAgentUserRole add member test

GO
EXEC msdb.dbo.sp_add_proxy @proxy_name=N’abc’,@credential_name=N’abc’,
        @enabled=1
GO
EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N’abc’, @subsystem_id=3
GO

/*
訊息 14516,層級 16,狀態 1,程序 sp_verify_proxy_permissions,行 190
Proxy (2) 不允許用於子系統 "CmdExec" 與使用者 "test"。透過呼叫 sp_grant_proxy_to_subsystem 或 sp_grant_login_to_proxy 來授與權限。
*/
execute as user=’test’
    DECLARE @jobId BINARY(16)
    EXEC msdb.dbo.sp_add_job @job_name=N’test’, @job_id = @jobId OUTPUT

    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’test’,
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N’CmdExec’,
            @command=N’"C:\Program Files\Excel Refresher\refreshexcel" "\\sql2014\drop\活頁簿1.xlsx"’,
            @output_file_name=N’C:\logs\refresh.log’,
            @flags=2,
            @proxy_name=N’abc’
revert

exec msdb.dbo.sp_delete_job @job_name=N’test’, @delete_unused_schedule=1

–要賦予 login 可以使用 proxy
EXEC dbo.sp_grant_login_to_proxy
    @login_name = N’test’,
    @proxy_name = N’abc’ ;
GO

execute as user=’test’
    DECLARE @jobId BINARY(16)
    EXEC msdb.dbo.sp_add_job @job_name=N’test’, @job_id = @jobId OUTPUT

    EXEC msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’test’,
            @step_id=1,
            @cmdexec_success_code=0,
            @on_success_action=1,
            @on_success_step_id=0,
            @on_fail_action=2,
            @on_fail_step_id=0,
            @retry_attempts=0,
            @retry_interval=0,
            @os_run_priority=0, @subsystem=N’CmdExec’,
            @command=N’"C:\Program Files\Excel Refresher\refreshexcel" "\\sql2014\drop\活頁簿1.xlsx"’,
            @flags=2,
            @server=N’SQL2014′,
            @proxy_name=N’abc’

    –似乎不是 sysadmin 建立的 job 要多設定,否則會有以下的錯誤
    /*
    訊息 14256,層級 16,狀態 1,程序 sp_start_job,行 123
    無法啟動作業 "test" (識別碼 9BEF0503-F8A6-4F51-9596-0BAD743DFF34),因為未定義任何作業伺服器。請呼叫 sp_add_jobserver 建立作業和作業伺服器的關聯。    */
    EXEC dbo.sp_add_jobserver @job_name = N’test’ ;
revert

exec sp_start_job ‘test’

exec msdb.dbo.sp_delete_job @job_name=N’test’, @delete_unused_schedule=1
drop user test
exec sp_delete_proxy @proxy_name=’abc’
drop credential abc
drop login test

Log Shipping 會將來源資料庫 rebuild index 的結果一併帶到目的資料庫嗎?

朋友的問題,看來是可以,測試如下:

create database dbOrg
use dbOrg
create table t(c1 int identity constraint pk primary key,
c2 time(7) default(sysdatetime()),
c3 varchar(10))
go
insert t(c3) values(‘a’)
go
insert t(c3) select c3 from t
go 20

declare @dbid int= DB_ID(N’dbOrg’);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

 

–restore 的資料庫其索引狀況破碎程度相同

backup database dbOrg to disk=’c:\temp\dbOrg.bak’
restore filelistonly from disk=’c:\temp\dbOrg.bak’
restore database dbOrg2 from disk=’c:\temp\dbOrg.bak’
with move ‘dbOrg’ to ‘c:\temp\dbOrg2.mdf’,move ‘dbOrg_log’ to ‘c:\temp\dbOrg2.ldf’,standby=’C:\dbOrgStandby.bak’,replace
go
declare @dbid int= DB_ID(N’dbOrg2′);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)
go

image

 

–重整索引,以清除破碎

alter index pk on t rebuild
go
declare @dbid int= DB_ID(N’dbOrg’);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

–檢視 Log 檔可以看到大幅增加 size 外,tx log 的內容我看不懂,但似乎是移動 data page 的相關資料

SELECT * FROM sys.fn_dblog(NULL,NULL)

–備份並還原交易紀錄,看起來目的端 index 也經由交易紀錄重建索引了

backup log dbOrg to disk=’c:\temp\dbOrgLog.bak’
restore log dbOrg2 from disk=’c:\temp\dbOrgLog.bak’ with recovery

go

declare @dbid int= DB_ID(N’dbOrg2′);
declare @objectid int = OBJECT_ID(N’t’);
select * from sys.dm_db_index_physical_stats (@dbid, @objectid, NULL , NULL, N’LIMITED’)

image

單純臆測,就檔案大小來看,rebuild index 的內容應該可以透過 log backup/restore 讓目的端一併達到 rebuild 的結果

image

SQL Server 2016 Audit 搭配 Temporal 資料,傳回使用者查詢時所看到的資料

Terry 提了一個有趣的需求,回傳使用者查詢時所看到的資料

USE [master]
GO
CREATE SERVER AUDIT [Audit-20150917-085416]
TO FILE
(    FILEPATH = N’C:\temp’
    ,MAXSIZE = 0 MB
    ,MAX_ROLLOVER_FILES = 2147483647
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(    QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
GO
alter server audit [Audit-20150917-085416] with(state=on)
go

create database dbTemporal
–非 CDC 從 ldf 透過 log reader 讀交易紀錄放到 system table
alter database dbTemporal set recovery simple
go
use dbTemporal
go

–自行指定記錄時間歷程的對應資料表
create table tbOrg
(
    PK int IDENTITY NOT NULL PRIMARY KEY,
    c1 nvarchar(500),
    StartTime datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL, –Hidden 關鍵字會隱藏這個欄位
    DueTime datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL, 
    PERIOD FOR SYSTEM_TIME (StartTime,DueTime) 
)
go

CREATE TABLE tbOrgHistory(
    PK int NOT NULL,
    c1 nvarchar(500) NULL,
    StartTime datetime2(7) NOT NULL,
    DueTime datetime2(7) NOT NULL
)
go

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = ON
(HISTORY_TABLE =  dbo.tbOrgHistory)
)
go

CREATE DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
FOR SERVER AUDIT [Audit-20150917-085416]
ADD(SELECT,INSERT,UPDATE,DELETE,EXECUTE ON database::dbTemporal BY public)
WITH (STATE = ON)
GO

–某個時間點查詢語法看到的資料
create proc spGetSnapshot @sql nvarchar(max),@time datetime2(7)
as
    set @sql += ‘ for system_time as of “‘ + convert(varchar(100),dateadd(hour,-8,@time)) + “" –轉成 UTC 時間
    exec(@sql)
go

———————————————————————————–
–Audit 搭配 Temporal 檢視回傳的資料
insert tbOrg(c1) values(‘a’)
waitfor delay ’00:00:01′
declare @t datetime2(7)=sysdatetime()
select * from tbOrg –模擬使用者查詢
waitfor delay ’00:00:01′
update tbOrg set c1=c1 + ‘1’
insert tbOrg(c1) values(‘d2’)
declare @sql nvarchar(max)

–Audit 也是用 UTC 時間
SELECT top(1) @sql=statement
FROM sys.fn_get_audit_file (‘C:\temp\Audit*.sqlaudit’,default,default)
where event_time=dateadd(hour,-8,@t) and object_name=’tbOrg’;
select @sql
exec spGetSnapshot @sql,@t

ALTER TABLE tbOrg SET (SYSTEM_VERSIONING = OFF)
drop table tbOrg
drop table tbOrgHistory

ALTER DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
WITH (STATE = OFF)
GO
DROP DATABASE AUDIT SPECIFICATION [DatabaseAuditSpecification-20150917-085751]
GO

測試本 Instance 的 Linked Server 是否可以正常連結

create proc #sp @cur sysname
as
    declare @sql nvarchar(1000)=N’select count(*) from [@cur].master.sys.objects’
    set @Sql=REPLACE(@sql,’@cur’,@cur)   
    exec(@Sql)
go

declare cur  cursor fast_forward read_only for
select name from master.sys.servers where product=’sql server’
declare @cur sysname
create table #t(ServerName sysname,ConnectStatus varchar(50),ErrMsg nvarchar(max),ErrNo int)        
open cur
fetch next from cur into @cur
while @@FETCH_STATUS=0
begin
    begin try
        exec #sp @cur
        insert #t(ServerName,ConnectStatus) values(@cur,’Succeed’)
    end try
    begin catch
        insert #t(ServerName,ConnectStatus,ErrMsg,ErrNo) values(@cur,’Fail’,ERROR_MESSAGE(),ERROR_NUMBER())
    end catch
    fetch next from cur into @cur
end
close cur
deallocate cur
select * from #t
drop table #t
drop proc #sp

降低 cxpacket 的設定

執行個體的設定

單一 NUMA 節點的伺服器,小於 8 的邏輯處理器:保留 MAXDOP 或其下的邏輯處理器 #

單一 NUMA 節點的伺服器,大於 8 的邏輯處理器:將 MAXDOP 保持在 8

具有多個 NUMA 節點伺服器,每個 NUMA 節點小於 8 的邏輯處理器:保留 MAXDOP 或其下的每個 NUMA 節點的邏輯處理器 #

具有多個 NUMA 節點伺服器,每個 NUMA 節點大於 8 的邏輯處理器:將 MAXDOP 保持在 8

參考:https://support.microsoft.com/en-us/kb/2806535

語法的設定

  • OPTION(MAXDOP )

當 SQL Server 發生 Deadlock 時,自動發送 Email

參照:https://technet.microsoft.com/zh-tw/library/ms186385(v=sql.110).aspx

關鍵技巧是 WMI Event 所觸發的 Alert

image

但事實上,該範例會發生 Error

image

雖然英文版有些討論,但都不是正解

https://technet.microsoft.com/en-us/library/ms186385(v=sql.110).aspx

需要如 Bob 所說 http://www.sqlskills.com/blogs/bobb/using-wmi-and-sql-agent-alerts-part-1/

開啟以下的設定,Agent Job 才會將 Token 值取出

image

看資料表在 data file 上的分佈

感謝 James Fu 的問題與解答,簡單測試的 script

CREATE DATABASE [test]
ON  PRIMARY
( NAME = N’test’, FILENAME = N’C:\temp\test.mdf’  ),
( NAME = N’f1′, FILENAME = N’C:\temp\f1.ndf’  ),
( NAME = N’f2′, FILENAME = N’C:\temp\f2.ndf’ )
LOG ON
( NAME = N’test_log’, FILENAME = N’C:\temp\test_log.ldf’)
GO

use test
go
create table t(c1 int)
insert t values(1)
go
insert t select * from t
go 13

dbcc ind(‘test’,’t’,0)  — heap 時第三個參數是 0 或 1 似乎沒差,但若是 clustered index 需要 1 才會 show 出所有的 leaf 和 non-leaf(index level>0) 的 page

image

select *
from sys.dm_db_database_page_allocations( DB_ID(‘test’), OBJECT_ID(‘t’),1, NULL,NULL) a
join sys.master_files m ON a.database_id = m.database_id AND a.allocated_page_file_id = m.file_id
order by allocated_page_file_id

image

sys.dm_db_database_page_allocations 可以呈現未使用的空間

參考資料

http://blogs.msdn.com/b/sqlserverstorageengine/archive/2006/12/13/more-undocumented-fun_3a00_-dbcc-ind_2c00_-dbcc-page_2c00_-and-off_2d00_row-columns.aspx

http://www.dotblogs.com.tw/jamesfu/archive/2014/09/17/dbccind.aspx

看 SQL Server 資料庫內物件的擁有者需要靠追蹤 DDL 語法

似乎大部分 sys.objects 內的 principal_id 都是 NULL,要 alter authorization 後才會填入 principal_id

http://stackoverflow.com/questions/1255947/determine-what-user-created-objects-in-sql-server

可用性群組相關資訊未移除乾淨

例如直接移除 SQL Server,但未先移除可用性群組。則再建立同名的可用性群組會得到如下錯誤:

訊息 41042,層級 16,狀態 1,行 131
可用性群組 ‘AG’ 已經存在。這個錯誤可能是之前失敗的 CREATE AVAILABILITY GROUP 或 DROP AVAILABILITY GROUP 作業所造成。如果您指定的可用性群組名稱正確無誤,請嘗試卸除可用性群組,然後重試一次 CREATE AVAILABILITY GROUP 作業。
訊息 41152,層級 16,狀態 2,行 131
無法建立可用性群組 ‘AG’。作業發生 SQL Server 錯誤 41042 且已回復。如需詳細資料,請檢查 SQL Server 錯誤記錄檔。當您解決錯誤的原因之後,請重試 CREATE AVAILABILITY GROUP 命令。

可以直接刪掉相關的註冊機碼

image

參考:http://dba.stackexchange.com/questions/48428/sql-server-2012-availability-group-issue