SQL Server authorizes the specified user to access the specified table

I. BACKGROUND

External companies are authorized to access our company's database, data interface calls, to give them a view of the specified view and authorized account, so in the database, to the specified user authorized access to the specified table

Second, SQL Server script

---Create a view
CREATE VIEW [dbo]. [View_runningdata] Asselect     TOP (+) PERCENT a.areaorganize, c.f_fullname as Areaorganizename, A.recorddate, D.f_itemcode, d.f_ ItemName, A.valuefrom         dbo. Item_record as a left OUTER JOIN                      dbo. Sys_organize as C on a.areaorganize = c.f_id left OUTER JOIN                      dbo. Sys_itemsdetail as D on a.itemid = d.f_id left OUTER JOIN                      dbo. Sys_items as E on d.f_itemid = e.f_id left OUTER JOIN                      dbo. Sys_items as F on e.f_parentid = F.f_idwhere     (f.f_encode = ' yxsj ') ORDER by A.recorddate, D.f_itemcode

　　

---Specifies that the specified user is authorized to access the specified table
exec sp_addrole ' Seeview ' GRANT SELECT on  view_runningdata to seeviewexec sp_addlogin ' yhsjjk ', ' yh123 ', ' da_dev517 ' exec sp_adduser ' yhsjjk ', ' yh123 ', ' Seeview '

　　

Second, analysis

1. In SQL Server, select the database role that you want to manipulate.

2. Current Database creation role (new permission Seeview)   

  exec sp_addrole ' Seeview '

3. Give permissions to create roles, assign View permissions

(meaning that the specified view list specifies the name of the seeview that this role can view; that is, the role can see the information given to these views below, except that all of the things are not visible)

   GRANT SELECT on  view_runningdata to Seeview

4. Add only users who are allowed access to the specified view: EXEC sp_addlogin ' login name ', ' Password ', ' Default database name '

Note: It may not be possible to do this here, require password strength, then you manually create

    exec sp_addlogin ' yhsjjk ', ' yh123 ', ' da_dev517 '

5. Add only users who are allowed access to the specified view into the RCRM role: exec sp_adduser ' login name ', ' username ', ' role '

  exec sp_adduser ' yhsjjk ', ' yh123 ', ' Seeview '

　　

Third, the Operation procedure

1. Select the database to be accessed, execute the View script, and create the view successfully.

2, give the specified user authorization to access the specified table, click Execute Data script, script execution success.

3, login name, password creation success.

4, the user authorization is successful, can only view the specified view.

SQL Server authorizes the specified user to access the specified table