Run through all user tables, add an user

Question

Run through all user tables, add an user

saintor1

Mr or Mrs. 500

Points: 562
More actions
June 20, 2016 at 5:58 am

#319487

SQL 2008 full version here.
As stated in the title, I want to add an user to all my tables in one of the databases, with SELECT access.
I am able to request "GRANT.... ON ... TO" on one table at a time.
I checked in the scripts, I tried a few examples but I didn't find code to run through them all without error. There is always something.
as:
USE [W_SE-MFG]
FOR x IN (SELECT * FROM user_tables)
LOOP
EXECUTE IMMEDIATE 'GRANT SELECT ON ' || x.table_name || ' TO [MYUSER]';
END LOOP;
TIA

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18844 More actions · Answer 1

The simplest way would be to add the user to the db_datareader role.

With SQL2012, and above, this can be done with ALTER ROLE but with SQL2008 I think you will still need to use sp_addrolemember.

Try something like:

EXEC sp_addrolemember @rolename = 'db_datareader', @membername = 'MyUser';

If you want to add rights to most tables you are usually best generating the script with something like:

SELECT 'GRANT SELECT ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] TO [MyUser];'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE';

Y.B. SSChampion Points: 11649 More actions · Answer 2

Roles are your friend here...you should add the user to the db_datareader role which will have the same effect. You have to ask yourself, do you really want to manage individual users this way?

SELECT quote FROM brain WHERE original = 1
0 rows returned

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 3

db_datareader was already added manually but it doesn't seem to work.

SELECT 'GRANT SELECT ON [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '] TO [MyUser];'

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE';

This lists successfully all my tables, but doesn't actually grant select. Excuse my ignorance, but shouldn't the instruction EXECUTE be used?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

That query generates the statement, copy-paste once you've checked it, then run it.

I'll echo, use roles. Directly assigning users to objects causes major admin overhead and debugging pain. db_datareader gives the user select on all tables, views and functions, rather use that than the individual assignments. Alternately, if the permissions should be allocated to 'most' tables, then create a custom role, grant the permissions to the role, then add the user to the role.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 5

That query generates the statement, copy-paste once you've checked it, then run it.

Beautiful! Txs a lot.

Steve Jones - SSC Editor SSC Guru Points: 736291 More actions · Answer 6

I'll add another note for what Gail and Y.B. said.

Don't do this.

Create a role, your own role, and use the script to GRANT to the role. Then add the user to a role.

What you're doing to deferring a problem that will come back to cause you issues later.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Steve Jones - SSC Editor (6/20/2016)
What you're doing to deferring a problem that will come back to cause you issues later.

And if db_datareader didn't 'work', then manually adding exactly the same permissions as db_datareader added is probably also not going to work. Most likely the user is already a member of db_denydatareader, and deny overrules grant, and hence the cumulative effect is no permissions granted

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

saintor1 Mr or Mrs. 500 Points: 562 More actions · Answer 8

Well it is quite possible that we complicate things, made to be easier.:-)

IT provided me that user, attached to all users login (authenticated windows ). I know very little about active directory and such.

I understand now that a role should have been created. Actually I can create one, suscribe the generic [MYUSER] to it and try to figure out why datareader is not enough.

I'll try to rework it. txs again.

crmitchell SSCarpal Tunnel Points: 4557 More actions · Answer 9

saintor1 (6/20/2016)
IT provided me that user, attached to all users login (authenticated windows ). I know very little about active directory and such.

That sounds like you have been given the windows security group AUTHENTICATED_USERS - this is a standard security group that everyone who is allowed to logon to a Windows server using AD will be a member of.

In most cases this is extremely broad and may be a security risk - Do you really want everyone to be able to be able to run queries on the database?

Better would be to use a more restrictive security group containing only the accounts you wish to permit access - its much the same process as using roles and adding SQL users to the role except that it is administered within AD rather than SQL.

It also has the benefit that you don't need to remove the user from every database server when they leave the company but does require all access is via an AD authenticated domain which could be a problem if your users are logging in via the web or are using other OSs