.::: Create user limited to access only spesific table or specific view, store procedure,function in MSSQL SQL Server :::.

 
1. create user & password
USE [master]
GO
CREATE LOGIN [onlyone] WITH PASSWORD=N'admin', DEFAULT_DATABASE=[teguhth], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
use [master];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
use [teguhth];
GO
USE [teguhth]
GO
CREATE USER [onlyone] FOR LOGIN [onlyone]
GO
  

2. additional priviledge

use teguhth

--- grant select
GRANT SELECT ON teguhth.dbo.barang TO "onlyone"
GRANT SELECT ON teguhth.dbo.pembelian TO "onlyone"

GRANT SELECT,update,insert,delete ON "dbo"."pasok" TO "onlyone"

-- grant view and execute procedure
GRANT select ON  "teguhth.dbo.pembelian_view" TO "onlyone"
GRANT VIEW DEFINITION ON  "pembelian_view" TO "onlyone"
GRANT execute ON  teguhth.dbo.Sp_Simple_Calculator TO onlyone

3. test login