none
Remove permissions for a specific user in a database for all objests RRS feed

  • Question

  • Hi,

    i am having an issue while removing permissions for a specific user in a database.

    i have a database in that there is 200 tables. in those tables i need some specific tables to remove permissions for a user. those tables no need to have Grant Deny Revoke WithGrant permissions for that specific user.

    some one please help me on this.


    • Edited by bhushayya Tuesday, May 16, 2017 2:32 PM
    Tuesday, May 16, 2017 2:31 PM

All replies

  • In your Database create a Database Role as:

     

    Double Click, Securables, Search, All Objects of the Types, Tables and set the tables that can be seen in that role. After that set the user giving him the role (that you have just created following the instruction above) permission. Do not forget to untick the usual permission as DboReader or DBWriter etc.

    Please mark as answer if this post helped you 

    Tuesday, May 16, 2017 2:45 PM
  • i dont want to assign permissions for a user.

    i want to remove permissions Deny permissions on SELECT INSERT UPDATE DELETE operations in all tables of a Database

    Tuesday, May 16, 2017 2:56 PM
  • use [yourDB]
    GO
    DENY SELECT TO [specificUser]
    GO
    

    Tuesday, May 16, 2017 3:01 PM
  • You've written: "in those tables i need some specific tables to remove permissions for a user."
    Tuesday, May 16, 2017 3:02 PM
  • From the system view [INFORMATION_SCHEMA].[TABLES] to list your tables that you want to remove some permissions and use GRANT SELECT / INSERT / UPDATE /DELETE ON TableName to UserName. 

    A Fan of SSIS, SSRS and SSAS

    Tuesday, May 16, 2017 3:11 PM
  • That user is not a member of db_owner database role, right?

    SELECT 'DENY  ON ' + quotename(s.name) + '.' + 
                                  quotename(o.name) + ' TO  UserJoe'
    FROM   sys.objects o
    JOIN   sys.schemas s ON o.schema_id = s.schema_id
    WHERE  o.type = 'U'


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Wednesday, May 17, 2017 6:39 AM
    Answerer
  • Check below sample

    declare @tables table(tablename varchar(100))
    declare @table varchar(100)
    declare @query varchar(100)
    
    insert into @tables values('dbo.Calender')
    insert into @tables values('dbo.customer')
    insert into @tables values('dbo.data')
    
    while exists (select * from @tables)
    begin
    select  top 1 @table = tablename from @tables
    set @query = 'DENY SELECT, INSERT, UPDATE ON ' +  @table + ' TO  [DOMAIN\User]'
    exec(@query)
    delete top (1) from @tables
    end
    Wednesday, May 17, 2017 9:18 AM