note

1-1創造DB與資料表使用者.sql

--建立使用者-登入DB主機
Create login [uer_1105] with password='uer_1105'

--建立使用者-使用某個資料庫
USE [a1105]
GO
CREATE USER [uer_1105] FOR LOGIN [uer_1105]

--新增SP
Create Procedure sp_1105_101
AS
select * from a1105.dbo.tb_1105 Where a = '101'

Create Procedure sp_1105_102
AS
select * from a1105.dbo.tb_1105 Where a = '102'

Create Procedure sp_1105_103
AS
select * from a1105.dbo.tb_1105 Where a = '103'

Create Procedure sp_1105_104
AS
select * from a1105.dbo.tb_1105 Where a = '104'

--刪除使用者-使用某個資料庫
--DROP USER [使用者帳號]

--刪除使用者-登入DB主機
--DROP LOGIN [使用者帳號]

1-2賦予SP權限.sql

USE [a1105]
--declare field
declare @spname varchar(100)
declare @cmd  varchar(100)


--declare cursor
DECLARE ju_cursor CURSOR FOR  
  --取出要去迴圈的大量資料
  SELECT specific_name FROM [a1105].information_schema.routines WHERE routine_type = 'Procedure' 
OPEN ju_cursor ;

--相當於FOR迴圈的 i = i + 1 ，逐步去抓資料表每一個特定欄位，放到@spname
FETCH NEXT FROM ju_cursor into @spname
  
--開始跑回圈  
WHILE @@FETCH_STATUS = 0
BEGIN    
 --下指令(給使用者 uer_1105)
 set @cmd='GRANT VIEW DEFINITION ON dbo.' + @spname + ' TO uer_1105'
 --執行指令
 exec (@cmd)
 --取得下一筆資料
 FETCH NEXT FROM ju_cursor into  @spname
END

--close cursor
CLOSE ju_cursor
DEALLOCATE ju_cursor


--單筆去賦予權限
--GRANT VIEW DEFINITION ON dbo.tb_1105 TO uer_1105

1-3賦予Table權限.sql

USE [a1105]
--declare field
declare @tbname varchar(100)
declare @cmd varchar(100)


--declare cursor
DECLARE ju_cursor CURSOR FOR  
  --取出要去迴圈的大量資料
  SELECT TABLE_NAME FROM [a1105].INFORMATION_SCHEMA.TABLES
OPEN ju_cursor ;

--相當於FOR迴圈的 i = i + 1 ，逐步去抓資料表每一個特定欄位，放到@spname
FETCH NEXT FROM ju_cursor into @tbname
  
--開始跑回圈  
WHILE @@FETCH_STATUS = 0
BEGIN    
 --下指令(給使用者 uer_1105)
 set @cmd='GRANT SELECT ON dbo.' + @tbname + ' TO uer_1105'
 --執行指令
 exec (@cmd)
 --取得下一筆資料
 FETCH NEXT FROM ju_cursor into  @tbname
END

--close cursor
CLOSE ju_cursor
DEALLOCATE ju_cursor


--單筆去賦予權限
--GRANT SELECT ON dbo.tb_1105 TO uer_1105;

1-4檢查使用者權限.sql

USE [a1105]
Select UserName = USERS.name, ObjectName = o.name, PermissionName = prmssn.permission_name
From sys.database_permissions prmssn
         INNER JOIN sys.database_principals AS grantee
         ON grantee.principal_id = prmssn.grantee_principal_id
         JOIN sys.sysusers USERS ON USERS.sid=grantee.sid
         JOIN sys.objects o on major_id=o.object_id
Order By  prmssn.permission_name

 

#查詢 sp_1031_104 權限

SELECT * FROM fn_my_permissions('sp_1031_104', 'object');  

 

#新增

給使用者 u1101_1719 這個 SP (dbo.sp_1031_101) 的權限

GRANT EXECUTE ON dbo.sp_1031_101 TO u1101_1719

GRANT CONTROL,VIEW DEFINITION ON dbo.sp_1031_101 TO u1101_1719

GRANT CONTROL,VIEW DEFINITION ON dbo.sp_1031_101 TO u1101_1719,u1101_1743

#讀取該資料庫所有的SP

select * from a1031.information_schema.routines where routine_type = 'Procedure'

//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

SP 權限 

#執行 execute 
#改變 alert 
ALTER: Confers permission to change the properties of the securable object, except to change ownership. This permission implies other permissions to alter, create, and drop objects within the same scope. For example, an ALTER permission on a database includes permission to alter tables and schemas.

#取得擁有權 control 
CONTROL: Confers essentially all possible permissions on the securable object, making the principal a virtual owner of the securable. This includes the ability to grant permissions on the securable to other principals.

#控制 take ownership 
TAKE OWNERSHIP: Confers on a principal permission to take ownership of an object. Granting this permission does not immediately transfer ownership. Instead, it allows the principal to take ownership at some future time.

#檢視定義 view definition 
VIEW DEFINITION: Confers on a principal permission to view the definition of a securable object. This is an important permission because structural information is useful in attacking a database. Without this permission, an attacker’s abilities to discover juicy targets in a database or server instance are severely limited.