Solved

Revoking all permissions from all user defined roles

Posted on 2004-08-29
10
484 Views
Last Modified: 2012-06-27
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
Comment
Question by:teiwaz
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 6

Accepted Solution

by:
robertjbarker earned 500 total points
ID: 11926744
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
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926763
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
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926893
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:teiwaz
ID: 11926901
"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
 
LVL 1

Author Comment

by:teiwaz
ID: 11926907
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
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11926985
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
 
LVL 1

Author Comment

by:teiwaz
ID: 11927018
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
 
LVL 1

Author Comment

by:teiwaz
ID: 11927146
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
 
LVL 1

Author Comment

by:teiwaz
ID: 11927174
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
 
LVL 6

Expert Comment

by:robertjbarker
ID: 11927223
thank you!

for the points and the final info.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question