Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 489
  • Last Modified:

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
0
teiwaz
Asked:
teiwaz
  • 5
  • 5
1 Solution
 
robertjbarkerCommented:
You can get a list of all user defined roles with the query:

select [name] from mydatabase.dbo.sysusers
  where issqlrole = 1 and gid <> 0 or isapprole = 1

If you wish to exclude application roles you can use:

select [name] from mydatabase.dbo.sysusers
  where issqlrole = 1 and gid <> 0

Then use what you always used to revoke permissions on these roles.
0
 
robertjbarkerCommented:
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.
0
 
robertjbarkerCommented:
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...
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
teiwazAuthor Commented:
"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?
0
 
teiwazAuthor Commented:
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.
0
 
robertjbarkerCommented:
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
0
 
teiwazAuthor Commented:
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.
0
 
teiwazAuthor Commented:
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
0
 
teiwazAuthor Commented:
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.)
0
 
robertjbarkerCommented:
thank you!

for the points and the final info.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now