SQL SERVER赋权限

--创建登录账户
use master
GO
EXEC sp_addlogin 'jacky', 'pwd'
--EXEC sp_droplogin 'jacky'  --删除登陆账户
 
use TestDB
GO
--创建数据库用户
EXEC sp_grantdbaccess 'jacky'
GO

--drop schema [jacky]    --删除架构
--drop user jacky        --删除数据库用户
 
 
--指定的数据库赋权限
grant select  on database::TestDB
to jacky
with grant option
 
 
--指定表赋查询权限
grant select,insert on  tbl_all_CommonEnumList 
to jacky
GO
/*
grant select 
on table:: tbl_all_CommonEnumList 
to jacky
WITH GRANT OPTION
GO
*/

--指定function查询权限
grant select
on object::dbo.FNLY_GetEnumName
to jacky
with grant option

grant exec
on object::stp_sys_SelectTable
to jacky
with grant option

/*
grant exec
on object::sp_helptext
to jacky
with grant option
*/

--查询当前登陆用户
select USER_NAME()
select CURRENT_USER as 'CurrentLoginUser'

 注：赋exec权限后，即使没有delete/update权限，在proc中有delete/update代码，依然可以正常执行

转载于:https://www.cnblogs.com/myjacky/p/3622150.html