Set permissions on SQL objects

[freewilly]

Hi team

Is there a way to set the permissions on database objects with one command? Currently we have to set the permissions one object at a time. We would like to be able to select a user or a role and give that user/role permission to do things on the objects in a database by running one stored procedure. Can this be done?

Thanks FW

 

[tlbroadbent]

In SQL 7 and 2000, you can give a user permissions on all tables and views by adding the user to the db_datareader and db_datawriter roles. Of course, db_datareader grants select permissions and db_datawriter grants update, insert, delete permissions. If you want more limited permissions, you could use something like the following. Modify as needed for your site.

declare @sql varchar(100)

declare objs cursor for
select
'grant select, update, insert on ' + ' ' +
name + ' to username' -- or role
from sysobjects
where (type = 'v' or type = 'u')
and left(name, 3) Not In ('sys', 'dtp')
--Add a list of objects not to grant permission on
and name not (<exclusion list>)

open objs

fetch next from objs into @sql
if @@fetch_status <> 0
begin
print 'no user tables or views in database'
return
end

while @@fetch_status = 0
begin
print @sql -- print statement is optional
exec(@sql)
if @@error <> 0
begin
print 'error'
return
end
fetch next from objs into @sql
end

close objs
deallocate objs

You could write a similar script to grant execute permissions on stored procedures. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.