"GRANT ALL TO role" in SQL Server

Question

Please can someone explain what the following statement does in SQL Server 2005:

GRANT ALL TO pax_writer

pax_writer is a database role previously created using the statement

CREATE ROLE pax_writer AUTHORIZATION dbo

Answer 1

GRANT ALL TO "someone" grants all permissions for all objects available in the database. But I'm not sure this is suppose to work in sql server 2005 as far as I'm concerned they left "GRANT ALL" only for backward compatibility.

According to the article in sql server 2005 works like this:

1. If the securable is a database, "ALL" means BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.
2. If the securable is a scalar function, "ALL" means EXECUTE and REFERENCES.
3. If the securable is a table-valued function, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
4. If the securable is a stored procedure, "ALL" means EXECUTE.
5. If the securable is a table, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
6. If the securable is a view, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

Hopefully someone will the right answer

MSDN - GRANT

Answer 2

Grants database permissions

This is the only time you can leave the ON ThingsAndStuff clause out.

ALL

This option does not grant all possible permissions. Granting ALL is equivalent to granting the following permissions: BACKUP DATABASE, BACKUP LOG, CREATE DATABASE, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE, and CREATE VIEW.