How to Grant Select on (All Tables) 3

[DeveloperJr]

Is there any command to do this without listing each table?

 

[klewis10367]

Run this, where the bold being the user, group, or role you want to grant access to:

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

 

[ptheriault]

You could just run this.

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

- Paul

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

 

[Crowley16]

Ooo, undocumented sp_...

not seen that one before, pretty useful.

have a star...

--------------------
Procrastinate Now!

 

[ptheriault]

There is also sp_MSforeachdb.

- Paul

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

 

[Crowley16]

is there one for all views or sprocs or functions?

--------------------
Procrastinate Now!

 

[ptheriault]

not that I know of.

Klewis,
If you want to see what tables you have granted select on I have add a print statement.

sp_MSforeachtable @command1="PRINT 'Granting select on ''?''' GRANT SELECT ON ? TO [b]user[/b]"

- Paul

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

 

[ptheriault]

Crowley,
If you want to see what it does just run.
sp_helptext sp_MSforeach_worker

forEach procs call sp_MSforeach_worker.



- Paul

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

 

[LNBruno]

You could try Googling this:

"SQL Server undocumented stored procedures"

< M!ke >
"Believe, but verify.

 

[klewis10367]

What about views...would you just replace the table part with view?

 

[ptheriault]

No this only works with user tables.

- Paul

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

 

[DeveloperJr]

Thanks Paul, have another star.

 

[jrbarnett]

Would not giving the membership of the db_datareader database role accomplish the same thing and be easier?

John