create database booking (可以用 Workbench, root 或 sa 登入)

cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"

mysqldump -u root -p booking < d:\MySQLBackup\yours.bak

mysql -u sa -p booking < d:\MySQLBackup\yours.bak

建立使用者 (可以用 Workbench, root 或 sa 登入)

grant all privileges on booking.* to 'booking'@'%'; (可以用 Workbench, root 或 sa 登入)

如下, 要記得修改 database_name, userOLD, userNEW 三個參數

CREATE TABLE #Command

    (

    Id int NOT NULL IDENTITY (1, 1),

    command nvarchar(MAX) NOT NULL

    )  ON [PRIMARY]

     TEXTIMAGE_ON [PRIMARY]

GO



USE database_name  -- Use the database from which you want to extract the permissions

GO



SET NOCOUNT ON



DECLARE @OldUser sysname, @NewUser sysname



SET @OldUser = 'userOLD' --The user or role from which to copy the permissions from

SET @NewUser = 'userNEW' --The user or role to which to copy the permissions to



insert into #Command(command)

Select convert(nvarchar(max), '--Database Context')



insert into #Command(command)

SELECT 'USE' + SPACE(1) + QUOTENAME(DB_NAME())



insert into #Command(command)

SELECT '--Cloning permissions from' + SPACE(1) + QUOTENAME(@OldUser) + SPACE(1) + 'to' + SPACE(1) + QUOTENAME(@NewUser)



insert into #Command(command)

SELECT 'EXEC sp_addrolemember @rolename ='

    + SPACE(1) + QUOTENAME(USER_NAME(rm.role_principal_id), '''') + ', @membername =' + SPACE(1) + QUOTENAME(@NewUser, '''') AS '--Role Memberships'

FROM    sys.database_role_members AS rm

WHERE USER_NAME(rm.member_principal_id) = @OldUser

ORDER BY rm.role_principal_id ASC



insert into #Command(command)

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

    + SPACE(1) + perm.permission_name + SPACE(1) + 'ON ' + QUOTENAME(USER_NAME(obj.schema_id)) + '.' + QUOTENAME(obj.name)

    + CASE WHEN cl.column_id IS NULL THEN SPACE(0) ELSE '(' + QUOTENAME(cl.name) + ')' END

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Object Level Permissions'

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.objects AS obj

    ON perm.major_id = obj.[object_id]

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

    LEFT JOIN

    sys.columns AS cl

    ON cl.column_id = perm.minor_id AND cl.[object_id] = perm.major_id

WHERE usr.name = @OldUser

ORDER BY perm.permission_name ASC, perm.state_desc ASC



insert into #Command(command)

SELECT CASE WHEN perm.state <> 'W' THEN perm.state_desc ELSE 'GRANT' END

    + SPACE(1) + perm.permission_name + SPACE(1)

    + SPACE(1) + 'TO' + SPACE(1) + QUOTENAME(@NewUser) COLLATE database_default

    + CASE WHEN perm.state <> 'W' THEN SPACE(0) ELSE SPACE(1) + 'WITH GRANT OPTION' END AS '--Database Level Permissions'

FROM    sys.database_permissions AS perm

    INNER JOIN

    sys.database_principals AS usr

    ON perm.grantee_principal_id = usr.principal_id

WHERE usr.name = @OldUser

AND perm.major_id = 0

ORDER BY perm.permission_name ASC, perm.state_desc ASC



Select command from #Command order by Id

drop table #Command

使用 SQL Server 管理介面設定欄位權限時要很小心陷井. 如下圖,
1. Col2 和 Id 欄位沒有被授予也沒有被拒絕.
2. 若是設定 Col2 欄位為授予, Id 欄位沒有做任何的修改.
3. 存檔後, Id 欄位會被拒絶存取.

 
以上的情況是用以下 SQL 生成:

GRANT SELECT ON OBJECT::S3..test TO webuser;
Deny SELECT ON OBJECT::S3..test(Col1) TO webuser;
 

想要讓特定 user 可以備份某個 DB ，但又不想把該 user 加入 sysadmin 時，可以用的。

use DBName
GRANT BACKUP DATABASE TO uasername

接下來該使用者的帳戶就可以執行
​
BACKUP DATABASE XXX TO DISK = 'D:\SQLBackup\XXXXX.bak'