sqlserver中创建登陆用户并赋予权限

最新推荐文章于 2025-05-16 17:04:03 发布

原创最新推荐文章于 2025-05-16 17:04:03 发布 · 5.4k 阅读

1 ·

CC 4.0 BY-SA版权

本文提供了一段SQL脚本，用于创建登录名和用户，并为该用户授予对所有表、视图及存储过程的SELECT权限，同时拒绝ALTER权限。此外还针对特定表授予了额外的INSERT、UPDATE和DELETE权限。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

exec sp_addlogin 'Luckysa','sql2008','WoLucky_DATA'
exec sp_adduser 'Luckysa','Luckysa'

declare @a varchar(40)
select @a=MAX(name) from sys.tables
while(LEN(@a)>0)
begin
exec('grant select on '+@a+' to Luckysa')
exec('deny alter on '+@a+' to Luckysa')
select @a=MAX(name) from sys.tables where name<@a
end

declare @b varchar(40)
select @b=MAX(name) from sys.views
while(LEN(@b)>0)
begin
exec('grant select on '+@b+' to Luckysa')
exec('deny alter on '+@b+' to Luckysa')
select @b=MAX(name) from sys.views where name<@b
end

declare @c varchar(40)
select @c=MAX(name) from sys.procedures
while(LEN(@c)>0)
begin
exec('grant EXECUTE on '+@c+' to Luckysa')
exec('deny alter on '+@c+' to Luckysa')
select @c=MAX(name) from sys.procedures where name<@c
end

grant insert on LoginLog to Luckysa
grant EXECUTE on SysCheckPower to Luckysa
grant delete on Pro_Stock to Luckysa
grant insert on Pro_Stock to Luckysa