GRANT SELECT permission from one user's tables/views to another

Question

Viewing 4 posts - 1 through 3 (of 3 total)

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

Ray M SSC-Insane Points: 21093 More actions · Answer 1

The following script will kick out a row for each table.

Set your query window to Results to Text, Execute this script

Copy results to clipboard,

then paste them into a new window and execute.

declare @UserB varchar(50)

set @UserB = 'UserB'

select 'grant select on ' + so.name + ' to [' + @UserB + '] '

from sysobjects so

join sysusers su on su.uid = so.uid

where so.type in ('U')

and su.name = 'UserA'

Godin Old Hand Points: 331 More actions · Answer 2

Thanks for the info.

One last question , once I grant the SELECT permission to userB

How does userB access userA tables without specifying the owner of the table ?

.

Right now when userB tries to access userA tables,

ex: "select * from allotment1" it indicates that the table is not found, but if you include the owner in front of the table then it works.

ex: "select * from userA.allotment1"

.

This is SQL Server 2000 so we cannot use synonyms.

.

Thanks

Ray M SSC-Insane Points: 21093 More actions · Answer 3

Just because he has been given grant permissions does not mean he does not have to specify the table owner.

Its all part of the security chain.

UserA and UserB can both have a table called MyTable

so UserB must declare the Owner prior to the Table so it finds the correct Table.

Using the Owner.TableName Syntax is a best practice for any query you write. You should get in the habit of doing this