创建login帐号及为其添加安全权限

--创建login帐号及为其添加安全权限,重要

use dbname

set nocount on

declare @dbname varchar(300),@username varchar(300),@pwd varchar(300)

select @dbname=db_name(),

@username='project_dbname',--这里是你的需要创建的新帐号

@pwd='ExO[!g-k9EQ]#y'--这里是你的密码，要求包含数字、字母、特殊符号,长度位.

declare @sql varchar(max)

declare @t table(name varchar(256)) --这个表存储你要禁止访问的系统表/视图

insert into @t select 'sysobjects' union all select 'sys.objects '

union all select 'syscolumns' union all select 'sys.columns'

union all select 'sys.tables' union all select 'sys.all_columns'

union all select 'sys.all_objects' union all select 'sys.indexes'

union all select 'sys.index_columns'

set @sql = 'use master;create login ['+ @username +'] with password = '''+@pwd+''',CHECK_EXPIRATION=OFF, CHECK_POLICY=ON'+char(13)

set @sql = @sql + 'use ['+@dbname+']; create user ['+@username +'] for login ['+@username+'] with DEFAULT_SCHEMA =dbo'+char(13)

select @sql = @sql + 'grant select,insert,update,delete on ['+name+'] to ['+@username+']' +char(13) from sys.tables where type='u' and is_ms_shipped !=1

select @sql = @sql + 'grant exec on ['+name+'] to ['+@username+']' +char(13) from sys.procedures where type='P' and schema_id=1 and is_ms_shipped !=1

select @sql = @sql + 'grant exec on ['+name+'] to ['+@username+']' +char(13) from sys.objects where type in ('FN') and is_ms_shipped !=1

select @sql = @sql + 'grant select on ['+name+'] to ['+@username+']' +char(13) from sys.objects where type in ('TF','IF') and is_ms_shipped !=1

select @sql = @sql + 'grant select on ['+name+'] to ['+@username+']' +char(13) from sys.views where type='V' and is_ms_shipped !=1

select @sql = @sql + 'deny select on '+name+' to ['+@username+']' +char(13) from @t

print(@sql)

exec(@sql)