?
Solved

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

Posted on 2003-03-03
10
Medium Priority
?
874 Views
Last Modified: 2007-12-19
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
TO SIFA

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


0
Comment
Question by:VD
[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
  • 3
  • 2
10 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 8058268
GRANT insert, update, delete
ON S_Table, TABLE2, TABLE3, TABLE4.....
TO SIFA
0
 
LVL 1

Author Comment

by:VD
ID: 8058419
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?
0
 
LVL 23

Accepted Solution

by:
adathelad earned 60 total points
ID: 8058473
ah ok....this example uses dynamic SQL:

USE YourDatabase
GO

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')
0
Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

 
LVL 1

Expert Comment

by:Aycex
ID: 8064339
You can just give the user admin or idividual rights to the database itself which will propagate throughout the tables and everthing else.
0
 
LVL 1

Author Comment

by:VD
ID: 8064574
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
to SIFA

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.
0
 
LVL 1

Author Comment

by:VD
ID: 8064637
Thanks Aycex, being a little silly.

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

Can you use the GRANT to a table?
0
 
LVL 1

Expert Comment

by:Aycex
ID: 8064680
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.
0
 
LVL 1

Author Comment

by:VD
ID: 8065286
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?

I.
0
 
LVL 1

Expert Comment

by:Aycex
ID: 8066437
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.



GRANT
    { 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 ]
[ WITH GRANT OPTION ]
[ AS { group | role } ]

Arguments


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

TO

Specifies the security account list.

security_account

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.

PRIVILEGES

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

permission

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.

column

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

table

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

view

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

stored_procedure

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

extended_procedure

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

user_defined_function

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

WITH GRANT OPTION

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.

0
 
LVL 1

Author Comment

by:VD
ID: 8070530
It's a good point about security.

Thanks for your input.

Van.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
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 video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

770 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