SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS OFF GO go DECLARE @CursorVar CURSOR declare @tablename varchar(40) declare @tb1 varchar(40) declare @dlycnt int --IsProcedure DECLARE Bti_cursor CURSOR FOR select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTable') = 1 and uid <> 1 --(默认情况下1就是指sa用户) set @CursorVar = Bti_cursor open @CursorVar set @dlycnt = 0 FETCH NEXT FROM @CursorVar into @tablename WHILE @@FETCH_STATUS = 0 BEGIN select @tb1 = 'ccuser.'+ @tablename --修改USER为你的帐户,必须以这个帐号登陆,因为SA已经不能访问了。 EXEC sp_changeobjectowner @tb1,'dbo' select @dlycnt = @dlycnt + 1 FETCH NEXT FROM @CursorVar into @tablename END CLOSE Bti_cursor SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
--做之前备份一下数据库。 1、 --用游标 declare @objname varchar(100),@xtype varchar(10) --定义游标 declare DZCursor CURSOR for select name,xtype from sysobjects where xtype in ('u','v','p') and name <>'dtproperties' --打开游标 open DZCursor --从游标取记录 fetch next from DZCursor into @objname, @xtype --当有记录 while @@fetch_status=0 begin if @xtype='p' exec ('grant exec on '+@objname+' to A,B,C') else exec ('GRANT SELECT INSERT, UPDATE, DELETE ON '+@objname+' TO A,B,C') --取下一条记录 fetch next from DZCursor into @objname, @xtype end --关闭游标 close DZCursor --删除游标引用 deallocate DZCursor 2、只要这些用户有对应的登录名和密码,就可以使用企业管理器,查询分析器,这个限制没法做。
GO
SET ANSI_NULLS OFF
GO
go
DECLARE @CursorVar CURSOR
declare @tablename varchar(40)
declare @tb1 varchar(40)
declare @dlycnt int --IsProcedure
DECLARE Bti_cursor CURSOR FOR
select name from dbo.sysobjects where OBJECTPROPERTY(id, N'IsTable') = 1 and uid <> 1 --(默认情况下1就是指sa用户)
set @CursorVar = Bti_cursor
open @CursorVar
set @dlycnt = 0
FETCH NEXT FROM @CursorVar into @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
select @tb1 = 'ccuser.'+ @tablename --修改USER为你的帐户,必须以这个帐号登陆,因为SA已经不能访问了。
EXEC sp_changeobjectowner @tb1,'dbo'
select @dlycnt = @dlycnt + 1
FETCH NEXT FROM @CursorVar into @tablename
END
CLOSE Bti_cursor
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
1、
--用游标
declare @objname varchar(100),@xtype varchar(10)
--定义游标
declare DZCursor CURSOR for select name,xtype from sysobjects where xtype in ('u','v','p') and name <>'dtproperties'
--打开游标
open DZCursor
--从游标取记录
fetch next from DZCursor into @objname, @xtype
--当有记录
while @@fetch_status=0
begin
if @xtype='p'
exec ('grant exec on '+@objname+' to A,B,C')
else
exec ('GRANT SELECT INSERT, UPDATE, DELETE ON '+@objname+' TO A,B,C')
--取下一条记录
fetch next from DZCursor into @objname, @xtype
end
--关闭游标
close DZCursor
--删除游标引用
deallocate DZCursor 2、只要这些用户有对应的登录名和密码,就可以使用企业管理器,查询分析器,这个限制没法做。