teiwaz
asked on
Revoking all permissions from all user defined roles
Is there any way to revoke all permissions from all user defined roles, without knowing the roles or objects? I know how to do it when I know the name of the role and object, but am looking for a way to clear all permissions so I can reset all of them.
Thanks!
Teiwaz
Thanks!
Teiwaz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
To revoke all permissions granted to a role you can use "revoke all from <role>". That way you don't need to know the object names.
Please ignore the "revoke all from <role>" comment. That does not seem to be working.
So all that is usefull right now is getting the list of roles. Sorry.
Working...
So all that is usefull right now is getting the list of roles. Sorry.
Working...
ASKER
"revoke all from <role>" isn't working. I.e. I execute this statement , the run sp_helprotect and all the role's permissions are still there. Also, I can still log on as a user of this role (with no permissions explicitly set) and still access data. Any idea why?
ASKER
Oops, when I submitted my last comment, I got your comment to the same effect. I'm looking into the sp_helprotect sproc to see how to get a list of objects for a role.
OK, sorry for the false starts.
If you want to revoke all permissions on user created roles, then how about dropping all such roles and then recreating them, thus:
create table #role_names ([name] varchar(255))
insert #role_names
select [name] from pmdbreports.dbo.sysusers
where issqlrole = 1 and gid <> 0
declare @name nvarchar(40)
declare @sql nvarchar(255)
declare roll_names cursor for
select [name] from #role_names
open roll_names
FETCH NEXT FROM roll_names INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'sp_droprole ''' + @name + ''''
print 'drop ' + @sql
exec sp_executesql @sql
set @sql = 'sp_addrole ''' + @name + ''''
print 'add ' + @sql
exec sp_executesql @sql
FETCH NEXT FROM roll_names INTO @name
END
CLOSE roll_names
DEALLOCATE roll_names
drop table #role_names
If you want to revoke all permissions on user created roles, then how about dropping all such roles and then recreating them, thus:
create table #role_names ([name] varchar(255))
insert #role_names
select [name] from pmdbreports.dbo.sysusers
where issqlrole = 1 and gid <> 0
declare @name nvarchar(40)
declare @sql nvarchar(255)
declare roll_names cursor for
select [name] from #role_names
open roll_names
FETCH NEXT FROM roll_names INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql = 'sp_droprole ''' + @name + ''''
print 'drop ' + @sql
exec sp_executesql @sql
set @sql = 'sp_addrole ''' + @name + ''''
print 'add ' + @sql
exec sp_executesql @sql
FETCH NEXT FROM roll_names INTO @name
END
CLOSE roll_names
DEALLOCATE roll_names
drop table #role_names
ASKER
The problem I see with this approach is that then I'd have to handle storing away users in those roles, and adding them back to the roles.
I'm close on an alteration of sp_helprotect to get a list of objects belonging to a role. I'll post what I figure out.
I'm close on an alteration of sp_helprotect to get a list of objects belonging to a role. I'll post what I figure out.
ASKER
OK, figured out how to do this, with your help. Here is the script.
Declare @SQL varchar(500)
Declare @MoreRoles bit
Declare @CurrRole sysname
Declare @CurrObject varchar(128)
Declare DbRoles Cursor
For
Select [name]
From sysusers
Where isSQLRole=1 and gid<>0
Open DbRoles
Fetch Next From DbRoles Into @CurrRole
While @@FETCH_STATUS=0
Begin
Print @CurrRole + '...'
-- For each role, loop through all objects and drop them
Declare RoleObjects Cursor
For
Select Distinct object_name(id)
From sysprotects
Where user_name(uid)=@CurrRole
Open RoleObjects
Fetch Next From RoleObjects Into @CurrObject
While @@FETCH_STATUS=0
Begin
Print 'Revoking right on ' + @CurrObject
-- Drop all permissions for this object
Set @SQL = 'Revoke All On ' + @CurrObject + ' FROM ' + @CurrRole
Exec(@SQL)
-- Get next object
Fetch Next From RoleObjects Into @CurrObject
End
-- Clean up this cursor
Close RoleObjects
Deallocate RoleObjects
-- Get next role
Fetch Next From DbRoles Into @CurrRole
End
Close DbRoles
Deallocate DbRoles
GO
Declare @SQL varchar(500)
Declare @MoreRoles bit
Declare @CurrRole sysname
Declare @CurrObject varchar(128)
Declare DbRoles Cursor
For
Select [name]
From sysusers
Where isSQLRole=1 and gid<>0
Open DbRoles
Fetch Next From DbRoles Into @CurrRole
While @@FETCH_STATUS=0
Begin
Print @CurrRole + '...'
-- For each role, loop through all objects and drop them
Declare RoleObjects Cursor
For
Select Distinct object_name(id)
From sysprotects
Where user_name(uid)=@CurrRole
Open RoleObjects
Fetch Next From RoleObjects Into @CurrObject
While @@FETCH_STATUS=0
Begin
Print 'Revoking right on ' + @CurrObject
-- Drop all permissions for this object
Set @SQL = 'Revoke All On ' + @CurrObject + ' FROM ' + @CurrRole
Exec(@SQL)
-- Get next object
Fetch Next From RoleObjects Into @CurrObject
End
-- Clean up this cursor
Close RoleObjects
Deallocate RoleObjects
-- Get next role
Fetch Next From DbRoles Into @CurrRole
End
Close DbRoles
Deallocate DbRoles
GO
ASKER
Oh, the part from sp_helprotect is:
Select Distinct object_name(id)
From sysprotects
Where user_name(uid)=@CurrRole
Though sp_helprotect is a huge procedure, it turns out that this is the salient piece and its simple. (dont' look for this in sp_helprotect, this is from the bit that loads the temp table. The distinct is needed to remove duplicates.)
Select Distinct object_name(id)
From sysprotects
Where user_name(uid)=@CurrRole
Though sp_helprotect is a huge procedure, it turns out that this is the salient piece and its simple. (dont' look for this in sp_helprotect, this is from the bit that loads the temp table. The distinct is needed to remove duplicates.)
thank you!
for the points and the final info.
for the points and the final info.