locked
How to set deny permission to all the tables and stored procedures for db role RRS feed

  • Question

  • Using SQL Server 2008 R2

    I Have database where I added a new role as "DenyRole". I would like use SQL script to deny permission for all the tables and stored procedures. Since I have lots of tables and stored procedurse, it will take long time to deny permission to them individually from SQL Server Management tool.

    Is there a way to Deny permission to all the tables and stored procedures using SQL statement? If so could you please provide an example?

    Thanks.


    Ripon

    • Moved by Olaf HelperMVP Saturday, October 7, 2017 5:00 AM Moved from "Database Engine" to a more related forum
    Tuesday, September 26, 2017 6:16 PM

All replies

  • if (select object_id('tempdb..##AddRoleMember')) is not null
    drop table ##AddRoleMember
    
    -- create table to hold user names to process
    create table ##AddRoleMember(username char(128))
    
    -- Determine what users are in the database
    insert into ##AddRoleMember SELECT name as username FROM master..syslogins where NAME not in ('sa') order by name
    
    Print 'Start'
    -- Section 3: Create local variables needed
    declare @CNT int
    declare @username char(128)
    declare @sid varbinary(85)
    declare @cmd nchar(4000)
    declare @c int
    declare @hexnum char(100)
    declare @db varchar(100)
    
    -- Process through each user and add them
    select @cnt=count(*) from ##AddRoleMember
    print 'Users to adjust: '
    print @cnt
    while @CNT > 0
    begin
    
    --get top user
    select top 1 @username= username from ##AddRoleMember
    
    --delete top user
    delete from ##AddRoleMember where username = @username
    
    --build command to add Role back to users
    set @cmd='EXEC sp_addrolemember '+ ''+'''DenyRole'''+','+''''+(@username)+''''
     
    print @cmd
     
    --are there Users left
    select @cnt = count(*) from ##AddRoleMember
    end while @cnt > 0
    
    --Remove temp tables
    drop table tempdb..##AddRoleMember
    
    

    Give this script a try, it will create the addrolemember statements to the DenyRole role for you to run. 

    Tuesday, September 26, 2017 8:14 PM
  • Is there a way to Deny permission to all the tables and stored procedures using SQL statement? If so could you please provide an example?

    DENY SELECT, INSERT, UPDATE, DELETE, EXECUTE TO DenyRole

    Done.

    No need to assign permissions per object when you can do it on database level. Or schema level.

    However, I can tell you one thing: you will regret this. Keep in mind that by default, users have no access at all, and there should rarely be any need for DENY. And if you add principals to this roles, add only individuals. If you add, say, AD groups, you will find that you have painted yourself into a corner, the say someone in that AD group has a legit reason to have access to a table or an SP.

    Tuesday, September 26, 2017 9:12 PM
  • You can try with by creating user defined roles

    USE [test_dneyDB]
    GO
    CREATE ROLE [Deny_All]
    GO
    USE [test_dneyDB]
    GO
    ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [Deny_All]
    GO
    use [test_dneyDB]
    GO
    DENY DELETE TO [Deny_All]
    GO
    use [test_dneyDB]
    GO
    DENY EXECUTE TO [Deny_All]
    GO

    USE [test_dneyDB]
    GO
    ALTER ROLE [Deny_All] ADD MEMBER [dbo]
    GO


    Thanks, Satish Kumar. Please mark as this post as answered if my anser helps you to resolves your issue :)

    Wednesday, September 27, 2017 10:35 AM
  • Thanks Satish.

    I executed the Deny Delete command and I went to the role property window. Then in the securables, When I added all the tables I don't see the delete deny checked. Is that the correct behavior?


    Ripon

    Wednesday, September 27, 2017 9:13 PM
  • You can try with by creating user defined roles

    USE [test_dneyDB]
    GO
    CREATE ROLE [Deny_All]
    GO
    USE [test_dneyDB]
    GO
    ALTER AUTHORIZATION ON SCHEMA::[db_denydatawriter] TO [Deny_All]
    GO
    use [test_dneyDB]
    GO
    DENY DELETE TO [Deny_All]
    GO
    use [test_dneyDB]
    GO
    DENY EXECUTE TO [Deny_All]
    GO

    USE [test_dneyDB]
    GO
    ALTER ROLE [Deny_All] ADD MEMBER [dbo]
    GO


    ...what on earth is this confusing script supposed to do, logically?

    You lost me in the moment that you made the "Deny_All" role owner of a built-in schema.


    Andreas Wolter (Blog | Twitter)
    MCSM: Microsoft Certified Solutions Master Data Platform/SQL Server 2012
    MCM SQL Server 2008
    MVP Data Platform MCSE Data Platform
    MCSM Charter Member, MCITP Charter Member etc.
    www.SarpedonQualityLab.com
    (Founder)

    Monday, October 9, 2017 11:46 AM