如何在deploy的时候 为数据库，表，存储过程 设置权限

创建用户访问数据库 。
 1 USE [master]
 2 GO
 3 
 4 IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'WfpApp') 
 5     DROP LOGIN [WfpApp]
 6 GO
 7 
 8 CREATE LOGIN [WfpApp] WITH PASSWORD='WfpAppP@ssw0rd.', DEFAULT_DATABASE=[Wfp], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
 9 GO
10 
11 DENY VIEW ANY DATABASE TO [WfpApp]; 
12 GO
13  
14 USE [Wfp]
15 GO
16 
17 IF EXISTS (SELECT Name FROM sys.sysusers WHERE Name = 'WfpApp')
18     DROP User [WfpApp] 
19 GO
20 
21 CREATE USER [WfpApp] FROM LOGIN [WfpApp]
22 GO
23 
24 USE [master]
25 GO
26 
27 ALTER AUTHORIZATION ON DATABASE::[Wfp] TO [WfpApp]
28 GO
29 
30 
31 IF EXISTS (SELECT * FROM sys.server_principals WHERE name = 'WfpEtl') 
32     DROP LOGIN [WfpEtl]
33 GO
34 
35 CREATE LOGIN [WfpEtl] WITH PASSWORD='WfpEtlP@ssw0rd.', DEFAULT_DATABASE=[Wfp], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
36 GO
37 
38 DENY VIEW ANY DATABASE TO [WfpEtl]; 
39 GO
40  
41 USE [Wfp]
42 GO
43 
44 IF EXISTS (SELECT Name FROM sys.sysusers WHERE Name = 'WfpEtl')
45     DROP User [WfpEtl] 
46 GO
47 
48 CREATE USER [WfpEtl] FROM LOGIN [WfpEtl]
49 GO
50 
51 USE [master]
52 GO
53 
54 ALTER AUTHORIZATION ON DATABASE::[Wfp] TO [WfpEtl]
55 GO
View Code
为创建的用户设置权限，可以操作的表 存储过程，视图  等权限 
 1 USE [WFP]
 2 GO
 3 GRANT EXECUTE ON dbo.P_ACTIVITY_I                                       TO [WfpApp]
 4 GRANT EXECUTE ON dbo.P_IC_ROLES_GET_ROLES_FOR_USER                      TO [WfpAdminApp]     
 5 GRANT EXECUTE ON dbo.P_ACTIVITY_MEMBER_ACCESS_REPROT                    TO [WfpApp]
 6 
 7 
 8 GRANT EXECUTE ON dbo.F_IC_CHECK_USER_NAME                            TO [WfpApp]    
 9 GRANT EXECUTE ON dbo.F_IC_CODE_GET_CODE_BY_ID                       TO [WfpApp]    
10 GRANT EXECUTE ON dbo.F_IC_CODE_GET_ID_BY_CODE_CODECATEGORY          TO [WfpApp]    
11 
12 
13 -- Grant access to tables
14 GRANT SELECT,INSERT,UPDATE    ON [dbo].[T_ETL_JOB_EXECUTION] TO  [WfpApp]  
15 GRANT SELECT,INSERT,UPDATE    ON [dbo].[T_REPORT_FREQUENCY] TO  [WfpApp]  
16 GRANT SELECT,INSERT,UPDATE    ON [dbo].[T_ETL_JOB_STATUS] TO  [WfpApp]  
17 GRANT SELECT,INSERT,UPDATE    ON [dbo].[T_IC_ROLES_ACTIONS] TO  [WfpApp]  
18 
19 
20 
21 -- Grant access to View
22 GRANT SELECT     ON dbo.V_CHECK_CONSOLIDATED_PAYMENT      TO  [WfpApp]    
23 GRANT SELECT     ON dbo.V_CHECK_MEDISAVE_PAYABLE        TO  [WfpApp]    
24 GRANT SELECT     ON dbo.V_CHECK_PAYMENT                 TO  [WfpApp]    
25 GRANT SELECT     ON dbo.V_CHECK_PAYMENT_DETAIL          TO  [WfpApp]    
26 GRANT SELECT     ON dbo.V_CHECK_PAYMENT_DETAIL_UPDATE   TO  [WfpApp]
View Code

 

posted @ 2015-01-10 11:25  just so  阅读(171)  评论(0)  编辑  收藏  举报