Permission issues

Dear all,

I have 500 tables in a database.

How can i grant select ,update and insert permission to a user on all 500 tables of the database at once.

Thanks

Mohd Sufian

The fixed database role db_datareader will work for SELECT.

You can use a cursor. ...might want to use sp_executesql

DECLARE @.User sysname

DECLARE @.Table nvarchar(500)

DECLARE @.cmd nvarchar(2000)

SET @.User = 'MyUser'

SET @.cmd = ''

DECLARE GrantUser CURSOR

LOCAL

FAST_FORWARD

FOR

SELECT s.Name + + '.' + o.name

FROM sys.objects o

INNER JOIN sys.schemas s

ON o.schema_id =s.schema_id

WHERE type = 'U'

IF EXISTS (SELECT * FROM sys.sysusers WHERE name = @.User)

BEGIN

OPEN GrantUser

FETCH NEXT FROM GrantUser INTO @.Table

WHILE @.@.FETCH_STATUS = 0

BEGIN

SET @.cmd = ''

SET @.cmd = ' GRANT SELECT ON ' + @.Table + ' TO' + @.User

SET @.cmd = @.cmd + ' GRANT UPDATE ON ' + @.Table + ' TO' + @.User

SET @.cmd = @.cmd + ' GRANT INSERT ON ' + @.Table + ' TO' + @.User

EXEC (@.cmd)

FETCH NEXT FROM GrantUser INTO @.Table

END

CLOSE GrantUser

DEALLOCATE GrantUser

END

|||

If this is SQL SERVER 2005, you can grant all these permissions at the database level itself.

So,

GRANT INSERT ON DATABASE::Database_Name to User_Name

GRANT UPDATE ON DATABASE::Database_Name to User_Name

GRANT SELECT ON DATABASE::Database_Name to User_Name

You must be connected to the database on which you are granting the permissions