June 27, 2010 at 8:45 pm
I need to Revoke all database role permissions for all users in all databases
and grant read only to all the users.
any Query to do all the above task ?????
Thanks in advance...
June 27, 2010 at 11:37 pm
Try this
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'up_AllProcPermissionsForDB'
AND type = 'P')
DROP PROCEDURE up_AllProcPermissionsForDB
GO
CREATE PROCEDURE up_AllProcPermissionsForDB
@db sysname,
@ProcLike varchar(50) = 'up_', -- used (like) in where clause
@Revoke bit = 0 -- if 1 then will revoke permissions instead of granting
AS
set nocount on
if isnull(@db,'') = '' BEGIN
RAISERROR('The parameter ''%s'' was not supplied.',12,1,'db') WITH NOWAIT
RETURN(1)
END
--revoke & proclike parameters can't be null
set @revoke = Isnull(@revoke,0)
set @ProcLike = Isnull(@ProcLike,'')
if right(@ProcLike,1) <> '%'
set @ProcLike = @ProcLike + '%'
declare @proc varchar (1000)
declare @user varchar(200)
DECLARE @Exec Nvarchar(4000)
create table #tmpUsers (
[User] nvarchar(150)
)
create table #tmpProcs (
[Proc] nvarchar(150)
)
insert #tmpUsers
exec ('select name
from ' + @db + '.dbo.sysusers
where
name not in(''dbo'',''guest'',''public'')
and
name not like ''db_%''')
insert #tmpProcs
exec ('select name
FROM ' + @db + '.dbo.sysobjects
WHEREtype = ''p''
andname like ''' + @ProcLike + '''
orderby name')
DECLARE curExec CURSOR
READ_ONLY
FOR
select [Proc], from #tmpProcs, #tmpUsers
OPEN curExec
FETCH NEXT FROM curExec INTO @proc, @user
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
IF @Revoke = 0
SELECT @Exec = 'USE ' + @db + '; GRANT EXEC ON [DBO].[' + @proc + '] TO [' + @user + ']'
ELSE
SELECT @Exec = 'USE ' + @db + '; REVOKE EXEC ON [DBO].[' + @proc + '] TO [' + @user + ']'
PRINT @Exec
execute sp_executesql @Exec
END
FETCH NEXT FROM curExec INTO @proc, @user
END
CLOSE curExec
DEALLOCATE curExec
drop table #tmpUsers
drop table #tmpProcs
GO
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
June 28, 2010 at 6:52 pm
No My Friend, this didnt work
June 29, 2010 at 12:21 am
why ? what issue you got ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
July 1, 2010 at 7:23 am
Hi
A great script for SQL DBA
Regards
Jayant Dass
0091 9313406257
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply