none
Grant SELECT, INSERT, UPDATE, DELETE on all table

    Question

  • hi at all
    i'm new user sqlserver
    I want to do GRANT on all table for userA with transact-sql instruction
    can I ? how

    thank you very much
    Monday, July 06, 2009 10:05 PM

Answers

  • How about just adding the user to db_datareader and db_datawriter roles if you need access to all tables in the database. Its short & simple.

    USE [DBName]
    GO
    EXEC sp_addrolemember N'db_datawriter', N'UserName'
    GO
    EXEC sp_addrolemember N'db_datareader', N'UserName'
    GO


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, July 06, 2009 11:06 PM
    Moderator
  • Leonardo, Sankar and Kio, I personally am not a big fan of using these roles or giving users access to ALL tables.   The problem is that it doesn't allow me as a dbo to add a table that they do not have access to, like if I want to move some data, or to do some utility action like keep a list of user access, or even a daily rowcount table.  If you simply grant rights to tables that you have (and remember to grant rights when you add new tables,) it is a lot better.

    However, in SQL Server 2005 and later, they have added an easy way to do this by granting rights a schema, such as:

    GRANT SELECT, INSERT, DELETE, UPDATE on SCHEMA::SchemaName to Principal --often DBO for Schema

    For the Principal, it is FAR preferrable to us a role and not a single user.  Unless you just have a few users, it usually simplifies your management. 

    Now, if I add a utility schema, the user has no access to the data, but, if a table is added to the SchemaName schema, they automatically have access...
    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    Tuesday, July 07, 2009 2:39 AM
    Moderator
  • I couldn't agree more.  While the script in the link "gets the job done", it does so the very hard way.  Instances, databases, and schemas are all securables.  You grant permissions on a securable to a principal.

    So, you could GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::<database name> TO <principal>.  Which is exactly equivalent to adding them to db_datareader and db_datawriter.  However, it has the added flexibility of allowing you to do things like granting the ability to insert and update, without also giving them delete authority.

    As Louis stated, it be definitely preferrable to create a role, assign permissions to the role, and then add users to the role in order to grant them the necessary permissions.  This keeps your permission management in a centralized location and all you have to do is manage role membership.  It's a lot easier to just add someone to a role than it is to keep granting a set of permissions to every user added to a database.  Sooner or later, you will forget a permission and spend a lot of time trying to figure out why things work for one user and not for another.

    I grant permissions at whatever level I need to.  If someone needs permissions across the entire database, I grant permissions on the database, not on every schema in the database.  How I handle the situation that Louis mentioned is by simply issuing an DENY.  If I add something to a database that I don't want someone to have access to, I explicitly deny access to the object to all users in the database.  The reason is very simple.  I'm not always the one managing the database.  By denying access, I can ensure that someone accidentally granting access to an object doesn't allow the user to get to it.  That's because they have to explicitly issue a revoke, which is generally not something done by accident.  The reason that it isn't done by accident is because in general, someone isn't going to see that in a GUI, but it is trivial for someone to open up the permissions GUI and blindly start adding users to roles, granting permissions, etc.  No matter how many grants are done, the deny will always override them.  But, that's my preference to explicitly grant or deny permission when required instead of relying on the absence of a permission to keep someone out.

    This is really just scratching the surface of security and what you can do with the security infrastructure.


    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, July 07, 2009 3:40 AM
    Moderator

All replies

  • How about just adding the user to db_datareader and db_datawriter roles if you need access to all tables in the database. Its short & simple.

    USE [DBName]
    GO
    EXEC sp_addrolemember N'db_datawriter', N'UserName'
    GO
    EXEC sp_addrolemember N'db_datareader', N'UserName'
    GO


    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    Monday, July 06, 2009 11:06 PM
    Moderator
  • Leonardo, Sankar and Kio, I personally am not a big fan of using these roles or giving users access to ALL tables.   The problem is that it doesn't allow me as a dbo to add a table that they do not have access to, like if I want to move some data, or to do some utility action like keep a list of user access, or even a daily rowcount table.  If you simply grant rights to tables that you have (and remember to grant rights when you add new tables,) it is a lot better.

    However, in SQL Server 2005 and later, they have added an easy way to do this by granting rights a schema, such as:

    GRANT SELECT, INSERT, DELETE, UPDATE on SCHEMA::SchemaName to Principal --often DBO for Schema

    For the Principal, it is FAR preferrable to us a role and not a single user.  Unless you just have a few users, it usually simplifies your management. 

    Now, if I add a utility schema, the user has no access to the data, but, if a table is added to the SchemaName schema, they automatically have access...
    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    Tuesday, July 07, 2009 2:39 AM
    Moderator
  • I couldn't agree more.  While the script in the link "gets the job done", it does so the very hard way.  Instances, databases, and schemas are all securables.  You grant permissions on a securable to a principal.

    So, you could GRANT SELECT, INSERT, UPDATE, DELETE ON DATABASE::<database name> TO <principal>.  Which is exactly equivalent to adding them to db_datareader and db_datawriter.  However, it has the added flexibility of allowing you to do things like granting the ability to insert and update, without also giving them delete authority.

    As Louis stated, it be definitely preferrable to create a role, assign permissions to the role, and then add users to the role in order to grant them the necessary permissions.  This keeps your permission management in a centralized location and all you have to do is manage role membership.  It's a lot easier to just add someone to a role than it is to keep granting a set of permissions to every user added to a database.  Sooner or later, you will forget a permission and spend a lot of time trying to figure out why things work for one user and not for another.

    I grant permissions at whatever level I need to.  If someone needs permissions across the entire database, I grant permissions on the database, not on every schema in the database.  How I handle the situation that Louis mentioned is by simply issuing an DENY.  If I add something to a database that I don't want someone to have access to, I explicitly deny access to the object to all users in the database.  The reason is very simple.  I'm not always the one managing the database.  By denying access, I can ensure that someone accidentally granting access to an object doesn't allow the user to get to it.  That's because they have to explicitly issue a revoke, which is generally not something done by accident.  The reason that it isn't done by accident is because in general, someone isn't going to see that in a GUI, but it is trivial for someone to open up the permissions GUI and blindly start adding users to roles, granting permissions, etc.  No matter how many grants are done, the deny will always override them.  But, that's my preference to explicitly grant or deny permission when required instead of relying on the absence of a permission to keep someone out.

    This is really just scratching the surface of security and what you can do with the security infrastructure.


    Mike Hotek BlowFrog Software, Inc. http://www.BlowFrogSoftware.com Affordable database tools for SQL Server professionals
    Tuesday, July 07, 2009 3:40 AM
    Moderator
  • >>I grant permissions at whatever level I need to.  If someone needs permissions across the entire database, I grant permissions on the database, not on every schema in the database.  How I handle the situation that Louis mentioned is by simply issuing an DENY.  <<

    I can see that, though I do prefer to avoid using DENY as little as possible.  I do agree with some of your reasoning though. Denying access to utility schemas would be a good way to go, though it is more difficult to deal with. As new groups are included and given full rights to the database, you have to be certain to DENY these other rights. 

    >>This is really just scratching the surface of security and what you can do with the security infrastructure.<<

    Agreed.  I didn't realize that you could grant rights at the database level like that :)


    Louis Blog: http://sqlblog.com/blogs/louis_davidson Book: http://drsql.org/ProSQLServerDatabaseDesign.aspx
    Tuesday, July 07, 2009 8:49 PM
    Moderator
  • Hello kio2008,

    it's a little late for a reply, but maybe the following stored proc can be helpful for you.

    Regards,

    Klaus

     

    CREATE PROCEDURE [dbo].[spSetRightsOnObjects]
     @mode VARCHAR(6) = 'GRANT',
     @objType VARCHAR(20) = 'TABLES',
     @user SYSNAME,
     @substring VARCHAR(256) = '%',
     @rights VARCHAR(256) = 'none',
     @checkOnly BIT = 0
    AS

    -- Stored Procedure for management of rights on *sets* of database objects (tables, views, stored procedures, user-defined functions).
    -- This service isn't offered by TSQL GRANT/REVOKE/DENY because of the missing possibility to place wildcards in object names.
    --
    -- Input Parameters:
    -- ================
    -- @mode       - Mode, = 'GRANT' (Default), 'REVOKE' or 'DENY'.
    -- @objType    - Object type,
    --               = 'TABLES': tables/views (= Default)
    --               = 'ROUTINES': stored procedures and user-defined functions
    -- @user       - Name of role/user (default = SQLServer-login)
    -- @substring  - Only database objects whose names contains this substring will be handled. Wildcards (% or _) are allowed.
    --               Default: @substring = '%' (alles)
    -- @rights     - Specification of access rights,
    --               for TABLES: = 'SELECT', 'INSERT', 'UPDATE', 'DELETE', 'ALTER', 'CONTROL', 'VIEW DEFINITION', 'TAKE OWNERSHIP'
    --               etc., or comma-separated combinations like 'SELECT, UPDATE'
    --               for ROUTINES: 'EXECUTE', 'ALTER', 'CONTROL', 'VIEW DEFINITION', 'TAKE OWNERSHIP'
    --               etc., or comma-separated combinations
    -- @checkOnly  - =1: test mode (GRANT/REVOKE/DENY-Statement are shown, but not executed
    --
    -- Examples:
    -- ========
    -- (1) exec dbo.spSetRightsOnObjects @mode='GRANT' @objType='TABLES', @user='Meier_F', @substring='TEST_%', @rights='SELECT, UPDATE, INSERT'
    --     grants access rights SELECT+UPDATE+INSERT to user 'Meier_F' on all database tables whose names start with 'TEST_'.
    --
    -- (2) exec dbo.spSetRightsOnObjects @user='Meier_F', @substring='TEST_%', @rights='SELECT, UPDATE, INSERT', @checkOnly=1
    --     Same effect as (1), but check-only run without changes at the database.
    --
    -- (3) exec dbo.spSetRightsOnObjects @mode='REVOKE', @user='Meier_F', @rights='DELETE'
    --     revokes access right DELETE from user 'Meier_F' on *all* tables/views of the database.
    --
    -- (4) exec dbo.setRightsOnObjects @user='Meier_F', @objType='ROUTINES'
    --     grants access right EXECUTE to user 'Meier_F' on *all* stored procedures of the database.
    --
    -- Prerequisites: SQLServer 2008.
    -- =============
    --
    -- Autor: Klaus Kuehne, Leibniz Institute for Applied Geophysics, Hannover

    DECLARE @sql VARCHAR(512), @objSchema VARCHAR(256), @objName VARCHAR(256), @rightsOn VARCHAR(256)

    SET @mode = UPPER(@mode)
    SET @rights = UPPER(@rights)
    SET @objType = UPPER(@objType)
    IF @mode <> 'GRANT' AND @mode <> 'REVOKE' AND @mode <> 'DENY' BEGIN
     PRINT '*Abort* Invalid @mode'
     RETURN
    END

    IF @objType = 'TABLES' BEGIN
     if @rights = 'none' SET @rights = 'SELECT'
     DECLARE c cursor for SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE 
      TABLE_NAME LIKE @substring AND TABLE_NAME NOT LIKE 'sys%' AND TABLE_NAME NOT LIKE 'sync%'
    END ELSE IF @objType = 'ROUTINES' BEGIN
     if @rights = 'none' SET @rights = 'EXECUTE'
     DECLARE c cursor for SELECT ROUTINE_SCHEMA, ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE 
      ROUTINE_NAME LIKE @substring
    END ELSE BEGIN
     PRINT '*Abort* Invalid @objType'
     RETURN
    END

    IF @checkOnly = 1 PRINT '*Warning* Check-only run without database changes!'

    OPEN c
    WHILE 1 = 1 BEGIN
     FETCH c INTO @objSchema, @objName
     IF (@@FETCH_STATUS <> 0) BREAK
     SET @rightsOn = @rights + ' ON [' + @objSchema + '].[' + @objName + ']'
     IF @mode = 'GRANT'
      SET @sql = 'GRANT ' + @rightsOn + ' TO ' + @user
     ELSE IF @mode = 'DENY'
      SET @sql = 'DENY ' + @rightsOn + ' TO ' + @user
     ELSE
      SET @sql = 'REVOKE ' + @rightsOn + ' FROM ' + @user 
     PRINT @sql
     IF @checkOnly <> 1 EXEC(@sql)
    END

    CLOSE c
    DEALLOCATE c

    Wednesday, December 22, 2010 11:31 AM