201101211212[SQL] T-SQL 程式小片段 - 二部曲

  1. 變更資料庫 ServerName
  2. Sqlcmd 模式 - 停止/啟動 SQL Service
  3. 資料庫快照 Snapshot
  4. 建立帳號
  5. MS-SQL 資料庫還原到另一台主機無法登入
  6. 以群組方式管理使用者權限

 

@變更資料庫 ServerName@ 

-- 顯示目前伺服器名稱
SELECT @@SERVERNAME
--
刪除原有的伺服器名稱
EXEC sp_dropserver @@SERVERNAME
--
新增新的伺服器名稱
EXEC sp_addserver 'New_Server_Name', local
--
變更完後,記得將SQL service Restart 重跑,才會變更過去
!!net stop mssqlserver /Y
!!net start mssqlserver /Y
--
顯示目前伺服器名稱
SELECT @@SERVERNAME


@Sqlcmd 模式 - 停止/啟動 SQL Service@

-- 在 SQLCMD 模式下執行


--
SQL Server Service

!!net stop mssqlserver /Y
--
執行Dos command 命令
!!copy "C:\DB\Northwind.*"  "D:\temp" 
--
執行 SQL Server Service
!!net start mssqlserver /Y

@資料庫快照 Snapshot @
詳細的介紹請參考「
SQL 2008 Snapshot 資料庫快照集 - 原理篇」與「SQL 2008 Snapshot 資料庫快照集 - 救援篇

建立快照
CREATE DATABASE Northwind_S1
ON ( NAME = N'Northwind', FILENAME = N'C:\myAdmin\DB\Northwind_S1.SS')
AS SNAPSHOT OF Northwind

SSMS 介面會出現在


查詢 Snapshot DB 所佔用之大小
SELECT DB_NAME(DbId) AS '快照資料庫', BytesOnDisk/1024.0 AS '檔案大小(KB)' FROM fn_virtualfilestats(DB_ID(N'Northwind_S1'), NULL);

查詢資料庫與其 Snapshot DB 對應關係


SELECT
name N'資料庫名稱', database_id N'資料庫識別碼' , source_database_id N'Snapshot的來源資料庫識別碼'
FROM sys.databases

刪除快照資料庫
DROP DATABASE northwind_S1


@建立帳號@

-- 在 Northwind 上建立 tolarku 帳號,密碼為 showmemoney
USE [master]
GO
CREATE
LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- 同上,再給予 server role 的 sysadm 最大權限

USE [master]
GO
CREATE LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=
OFF
GO
EXEC
master..sp_addsrvrolemember @loginame = N'tolarku', @rolename = N'sysadmin'

-- 同上,再再給予 tolarku 擁有 Northwind 的 db_owner 權限

*** 請注意:雖然第三個動作是多餘的(因為都已經擁有 sysadm 的最大權限了),但是主要的目的是要觀察,對於某一使用者增加其 Server Role 與 增加 Database Role 的不同~

*** 當增加 Database Role 時,會進入(use)該資料庫,才用 sp_addrolemember 來設定
USE [master]
GO
CREATE LOGIN [tolarku] WITH PASSWORD=N'showmemoney', DEFAULT_DATABASE=[Northwind], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'tolarku', @rolename =
N'sysadmin'
GO

USE [Northwind]
GO
CREATE
USER [tolarku] FOR LOGIN [tolarku]
GO
USE
[Northwind]
GO
EXEC
sp_addrolemember N'db_owner', N'tolarku'

-*-*-*---*-*--*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*

*** 再請觀察另外一件事,就是不管增加 Server Role 或 Database Role 時,就只有指定「誰~帳號」跟「身分~權限」,跟所用的密碼一點都沒關係

*** 你會說,很合理阿~帳密的訊息本來就是在整個 Server 上,不會個別的資料庫去設定密碼......

那麼!想像一下,當資料庫經過「備份」與「還原」到其他 Server 時,他的帳密部分會不會跟著帶過去呢?那在整個 Server 上的資料也會跟著過去嗎?

沒有~記錄在個別資料庫的訊息,,,,,,當然也帶不過去

所以~還原到其他 Server 時,是會發生 SQL 帳號無法正常登入

解決方法....請參考「MS-SQL 資料庫還原到另一台主機無法登入

哇~很晚了明天還要上班,而且也越來越冷了.....先來去睡~明天繼續!!

@以群組方式管理使用者權限@

  1. 建立群組 Database role
  2. 給群組適當的權限範圍
  3. 把帳號指派到該群組內

 

-- 建立群組 DataBase Role ,並把權限加入此群組身份中



USE
[Northwind]
CREATE ROLE [RD_staff]
GRANT SELECT ON [dbo].[customers_BAK] TO [RD_staff]
GRANT UPDATE ON [dbo].[CustomerDemographics] TO [RD_staff]
GRANT SELECT ON [dbo].[CustomerDemographics] TO [RD_staff]
GRANT INSERT ON [dbo].[CustomerCustomerDemo] TO [RD_staff]
GRANT UPDATE ON [dbo].[Customers] TO [RD_staff]
GRANT INSERT ON [dbo].[Customers] TO [RD_staff]
GRANT SELECT ON [dbo].[Customers] TO [RD_staff]

-- 建立一個「hammer」使用者,預設不給任何權限,只建立帳號
USE [master]
CREATE LOGIN [hammer] WITH PASSWORD=N'1111', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

-- 把剛剛建立的 RD_staff 所擁有的權限,指派給新人「hammer」


USE [Northwind]
CREATE USER [hammer] FOR LOGIN [hammer]
USE [Northwind]
EXEC sp_addrolemember N'RD_staff', N'hammer'

-- 測試該帳號是否能登入,並且對「Northwind」的「Customers」有 I/S/U 的權限
嗯嗯~可以登入,但是不能存取 Employees (當然囉!我們又沒給他這個物件的權限)


-- 對有權限的物件,就可以正常的存取,當然 update 也行


-- 以後維護某單位下的使用帳號,就不要再個別的設定,只要使用群組,就可以輕鬆管理
-- 當然~使用 schema 來管理也很好用

-- QQ 那如果又要針對某個 user 給予某個物件特別的權限,可以嗎?
-- AA:當然可以囉,就用一般的權限指派方式就可以了~~是可以並存的。
use [Northwind]
GRANT DELETE ON [dbo].[Employees] TO [hammer]
GRANT UPDATE ON [dbo].[Employees] TO [hammer]
GRANT INSERT ON [dbo].[Employees] TO [hammer]
GRANT SELECT ON [dbo].[Employees] TO [hammer]

-- 上面那樣四行的指令是用「指令碼」產生出來的,自己打 T-SQL 不用那麼麻煩,用逗點隔開就可以了

 

GRANT SELECT,INSERT, UPDATE,DELETE ON [dbo].[customers_BAK] TO [RD_staff]

-- 查詢 資料庫物件 的授權情形


SELECT pm.major_id, pm.type,pc.name 'Principal_Name'
,pm.permission_name, pm.state
FROM sys.database_permissions pm
inner join sys.database_principals pc
on pm.grantee_principal_id = pc.principal_id
WHERE major_id = OBJECT_ID('[Employees]')

-- Application Role 的使用方式跟 Database role 順序差不多,但應用程式角色是使用 sp_setapprole (需要有密碼) 予以啟用,MSDN說明可能會有安全性上的疑慮,那就不要用吧!http://msdn.microsoft.com/zh-tw/library/ms181127.aspx

 

~ End

回應
Google Search
Google
平均分數:0 顆星
投票人數:0
我要評分:
Google