การสร้าง usesr proxyCMAH ด้วย Management Studio
สร้าง role ใน Master database
| |
Role
|
Permission
|
CMAH_SYS
|
EXECUTE ON [sys].[sp_OADestroy]
EXECUTE ON [sys].[sp_OACreate]
EXECUTE ON [sys].[sp_OAMethod]
EXECUTE ON [sys].[sp_OASetProperty]
|
 | |
 | |
สร้าง role ใน CMAH database
| |
Role
|
Permission
|
CMAH_APP
|
Default
|
เพิ่ม user proxyCMAH เข้า role CMAH_APP
 | |
db_owner
|
Add CMAH_APP role to this role
|
 |
ตรวจสอบ Server Roles
ทำ User Mapping และตรวจสอบ Database role
การสร้าง user ด้วย SQL script
หากต้องการเปลี่ยนชื่อให้แทนที่ proxyCMAH_RPT ด้วยชื่อที่ต้องการ
|
USE [master]
GO
CREATE LOGIN [proxyCMAH_RPT] WITH PASSWORD=N'P@ssw0rd', DEFAULT_DATABASE=[CMAH], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
|
|
USE [master]
GO
CREATE ROLE [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OACreate] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OADestroy] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetErrorInfo] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAGetProperty] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAMethod] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OASetProperty] TO [CMAH_RPT]
GO
use [master]
GO
GRANT EXECUTE ON [sys].[sp_OAStop] TO [CMAH_RPT]
GO
|
|
USE [master]
GO
CREATE USER [proxyCMAH_RPT] FOR LOGIN [proxyCMAH_RPT] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [master]
GO
EXEC sp_addrolemember N'CMAH_RPT', N'proxyCMAH_RPT'
GO
|
|
USE [msdb]
GO
CREATE USER [proxyCMAH_RPT] FOR LOGIN [proxyCMAH_RPT] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [msdb]
GO
EXEC sp_addrolemember N'SQLAgentOperatorRole', N'proxyCMAH_RPT'
GO
|
|
USE [CMAH]
GO
CREATE ROLE [CMAH_RPT]
GO
|
|
USE [CMAH]
GO
EXEC sp_addrolemember N'db_owner', N'CMAH_RPT'
GO
|
|
USE [CMAH]
GO
CREATE USER [proxyCMAH_RPT] FOR LOGIN [proxyCMAH_RPT] WITH DEFAULT_SCHEMA=[dbo]
GO
USE [CMAH]
GO
EXEC sp_addrolemember N'CMAH_RPT', N'proxyCMAH_RPT'
GO
|
