Setting permission for multiple tables the same

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Setting permission for multiple tables the same

Author

Topic

Razzle00
Starting Member

35 Posts

Posted - 2007-03-22 : 11:14:42

Hi,

How can I set the permissions on all tables in a database to be exactly the same for 1 user name? Let's say I need all the tables in the DB to allow the user to Select,Update,Delete. Can this be done without going to the properties of each individual table?

Thanks,
Razzle

pareshmotiwala
Constraint Violating Yak Guru

323 Posts

Posted - 2007-03-22 : 11:34:38

Razzle00
Try using this script.
It is combined for both tables/views and stored procedures.

declare @tablename varchar(255),@tempstr varchar(40)
declare GrantTableAccess_curs insensitive cursor
for
select name from sysobjects where (type = 'U' OR type = 'V' OR type = 'FN') AND uid = 1
for READ ONLY
open GrantTableAccess_curs
print''
print' Name '
print'.......................................................'
fetch GrantTableAccess_curs into @TableName
while @@fetch_status = 0 begin
exec (' Grant SELECT,INSERT,DELETE,UPDATE on [' + @TableName + '] to user1 ')
select @tempstr = convert(char(40),@tablename)
print @tempstr
fetch GrantTableAccess_curs into @TableName
end
close GrantTableAccess_curs
deallocate GrantTableAccess_curs

go

declare @procname varchar(255),@tempstr varchar(255)
declare GrantProcAccess_curs insensitive cursor
for
select name from sysobjects where type = 'p' AND uid = 1
for READ ONLY
open GrantProcAccess_curs
print''
print''
print' Procedure Name '
print'.......................................................'
fetch GrantProcAccess_curs into @procName
while @@fetch_status = 0 begin
exec (' Grant EXEC on ' + @procName + ' to user1 ')
select @tempstr = convert(char(255),@Procname)
print @tempstr
fetch GrantProcAccess_curs into @procName
end
close GrantProcAccess_curs
deallocate GrantProcAccess_curs

Razzle00
Starting Member

35 Posts

Posted - 2007-03-23 : 09:03:25

Thanks, that works nice.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources