Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to Grant Select on (All Tables) 3

Status
Not open for further replies.

DeveloperJr

Programmer
Mar 10, 2007
25
US
Is there any command to do this without listing each table?
 
Run this, where the bold being the user, group, or role you want to grant access to:

Code:
declare @cmd nvarchar(1000)
declare @objname sysname
declare @owner sysname
declare @objtype char(2)

declare objs cursor for
select [name], user_name(uid) as owner, type from dbo.sysobjects
where type in ('U', 'V') 
order by type, [name]
for read only

open objs
fetch next from objs into @objname, @owner, @objtype

while (@@FETCH_STATUS != -1)
begin
if (@@FETCH_STATUS != -2)
begin
select @cmd = 'grant ' +
case (@objtype)
when ('U') then ('select')
when ('V') then ('select')
end + ' on [' + @owner + '].[' + @objname + '] to [b]dyn_Risk'[/b]

exec (@cmd)
end

fetch next from objs into @objname, @owner, @objtype
end

close objs
deallocate objs
 
You could just run this.

Code:
sp_MSforeachtable @command1="GRANT SELECT ON ? TO [b]user[/b]"

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Ooo, undocumented sp_...

not seen that one before, pretty useful.

have a star...

--------------------
Procrastinate Now!
 
There is also sp_MSforeachdb.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
is there one for all views or sprocs or functions?

--------------------
Procrastinate Now!
 
not that I know of.

Klewis,
If you want to see what tables you have granted select on I have add a print statement.
Code:
sp_MSforeachtable @command1="PRINT 'Granting select on ''?''' GRANT SELECT ON ? TO [b]user[/b]"

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Crowley,
If you want to see what it does just run.
sp_helptext sp_MSforeach_worker

forEach procs call sp_MSforeach_worker.



- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
You could try Googling this:

"SQL Server undocumented stored procedures"

< M!ke >
"Believe, but verify.
 
No this only works with user tables.

- Paul
10qkyfp.gif

- If at first you don't succeed, find out if the loser gets anything.
 
Would not giving the membership of the db_datareader database role accomplish the same thing and be easier?

John
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top