Solved

Revoking all permissions from all user defined roles

Posted on 2004-08-29
10
485 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

617 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