Geek Answers Handbook

"GRANT ALL TO role" in SQL Server

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 a statement

 CREATE ROLE pax_writer AUTHORIZATION dbo
+3
source share
2 answers

Allows access to the database

This is the only time you can leave an ON ThingsAndStuff .

ALL

This option does not provide all possible permissions. Giving 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.

+3
source

GRANT ALL FOR "MAN" provides all permissions for all objects available in the database. But I'm not sure if this should work in sql server 2005, as far as I know, they left "GRANT ALL" just for backward compatibility.

According to an article in SQL Server 2005, it works as follows:

  • If the database to be protected, “ALL” means BACKUP DATABASE, BACKUP LOG, CREATE DATABASES, CREATE DEFAULT, CREATE FUNCTION, CREATE PROCEDURE, CREATE RULE, CREATE TABLE and CREATE VIEW.
  • If reliable is a scalar, the "ALL" function means EXECUTE and RECOMMENDATIONS.
  • If the protected table is a table function "ALL" means DELETE, INSERT, LINKS, SELECT and UPDATE.
  • If the protected is stored, the procedure "ALL" means EXECUTE.
  • If the table is protected, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.
  • If the view is protected, "ALL" means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

Hope someone answers correctly.

MSDN - GRANT

+2
source

More articles:


All Articles