• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 905
  • Last Modified:

How do you grant insert, update, delete (etc.) to multiple tables

I need to grant privilleges to a new user I have created.

Say user: "SIFA"

I can grant access rights to a single table:

GRANT insert, update, delete
ON S_Table

However I want to grant rights to all tables in the database with on SQL query.

  • 5
  • 3
  • 2
1 Solution
GRANT insert, update, delete
ON S_Table, TABLE2, TABLE3, TABLE4.....
VDAuthor Commented:
Sorry, I should have been a little more elaberate, is there an automatic way of selecting all the tables in the database, as there are about a hundred tables?
ah ok....this example uses dynamic SQL:

USE YourDatabase

DECLARE @pstrTables VARCHAR(8000)
SET @pstrTables = ''

-- Get list of tables in the database
SELECT @pstrTables = @pstrTables + name + ','
FROM sysobjects
WHERE xtype='U'

-- Remove ending comma...
IF ( RIGHT(@pstrTables, 1) = ',')
     SET @pstrTables = LEFT(@pstrTables, LEN(@pstrTables) -1)

IF ( LEN(@pstrTables) > 0 )
     EXECUTE ('GRANT insert, update, delete
     ON ' + @pstrTables + ' TO SIFA')
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

You can just give the user admin or idividual rights to the database itself which will propagate throughout the tables and everthing else.
VDAuthor Commented:
I just went in and selected all the rights using SQL Enterprise manager, which I normally do.

Your code is not neat enough for what I was looking for.  I had a go at putting your code together for interest, but don't really have the time to play around with it to make it work.

Good effort on your part though, looks too complicated, and had forgotten "sysobjects", looking for simplicity

Looking for an easier way of doing it, like

grant insert,update,delete
on sysobjects

But that doesn't work.

sysprotects holds the data to grant (205), revoke (206) etc, and I was looking at

update sysprotects set protecttype = 205 where uid = 5

But I have to grant SQL Server permissions to update the system catalogs, which I don't think is a good idea, as I don't know what effects this would have on the system stability, i.e. what other tables need to be updated to do this.

If you have any more ideas, I would be interested in hearing them.
VDAuthor Commented:
Thanks Aycex, being a little silly.

Sorry didn't think anyone else was interested in replying.

Can you use the GRANT to a table?
If you go into and give the grant privilage to the user it automatically propagates it through out the entire database oe table stored procedures and so on.  By giving the user db_datareader and db datawriter it should give them all the permissions you are looking for.
VDAuthor Commented:
Thanks again, I'm sorry I don't quite get it.

I understand how to manually do this through Enterprise Manager, but what if I wanted to fire a SQL command to do this?

This is the only thing i know will work in a script.  In our business rules we never do this because if someone can fire this script they have total acces to your database.

    { ALL [ PRIVILEGES ] | permission [ ,...n ] }
        [ ( column [ ,...n ] ) ] ON { table | view }
        | ON { table | view } [ ( column [ ,...n ] ) ]
        | ON { stored_procedure | extended_procedure }
        | ON { user_defined_function }
TO security_account [ ,...n ]
[ AS { group | role } ]


A placeholder indicating that the item can be repeated in a comma-separated list.


Specifies the security account list.


Is the security account to which the permissions are applied. The security account can be a:

Microsoft. SQL Server user.

SQL Server role.

Microsoft Windows NT. user.

Windows NT group.
When a permission is granted to a SQL Server user or Windows NT user account, the specified security_account is the only account affected by the permission. If a permission is granted to a SQL Server role or a Windows NT group, the permission affects all users in the current database who are members of the group or role. If there are permission conflicts between a group or role and its members, the most restrictive permission (DENY) takes precedence. security_account must exist in the current database; permissions cannot be granted to a user, role, or group in another database, unless the user has already been created or given access to the current database.

Two special security accounts can be used with GRANT. Permissions granted to the public role are applied to all users in the database. Permissions granted to the guest user are used by all users who do not have a user account in the database.

When granting permissions to a Windows NT local or global group, specify the domain or computer name the group is defined on, followed by a backslash, then the group name. However, to grant permissions to a Windows NT built-in local group, specify BUILTIN instead of the domain or computer name.


Is an optional keyword that can be included for SQL-92 compliance.


Is an object permission that is being granted. When object permissions are granted on a table, table-valued function, or a view, the permission list can include one or more of these permissions: SELECT, INSERT, DELETE, REFERENCES, or UPDATE. A column-list can be supplied along with SELECT and UPDATE permissions. If a column-list is not supplied with SELECT and UPDATE permissions, then the permission applies to all the columns in the table, view, or table-valued function.

Object permissions granted on a stored procedure can include only EXECUTE. Object permissions granted on a scalar-valued function can include EXECUTE and REFERENCES.

SELECT permission is needed on a column in order to access that column in a SELECT statement. UPDATE permission is needed on a column in order to update that column using an UPDATE statement.

The REFERENCES permission on a table is needed in order to create a FOREIGN KEY constraint that references that table.

The REFERENCES permission is needed on an object in order to create a FUNCTION or VIEW with the WITH SCHEMABINDING clause that references that object.


Is the name of a column in the current database for which permissions are being granted.


Is the name of the table in the current database for which permissions are being granted.


Is the name of the view in the current database for which permissions are being granted.


Is the name of the stored procedure in the current database for which permissions are being granted.


Is the name of the extended stored procedure for which permissions are being granted.


Is the name of the user-defined function for which permissions are being granted.


Specifies that the security_account is given the ability to grant the specified object permission to the other security accounts. The WITH GRANT OPTION clause is valid only with object permissions.

AS {group | role}

Specifies the optional name of the security account in the current database that has the authority to execute the GRANT statement. AS is used when permissions on an object are granted to a group or role, and the object permissions need to be further granted to users who are not members of the group or role. Because only a user, rather than a group or role, can execute a GRANT statement, a specific member of the group or role grants permissions on the object under the authority of the group or role.

VDAuthor Commented:
It's a good point about security.

Thanks for your input.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now