What is the difference between being a db_owner and having GRANT ALL?

I have been using the following SQL to create a user, login and database.

USE master;
GO
CREATE DATABASE TEST;
GO
USE TEST;
GO
CREATE LOGIN TEST_USER WITH PASSWORD = 'password';
EXEC sp_defaultdb @loginame='TEST_USER', @defdb='TEST'
CREATE USER TEST_USER FOR LOGIN TEST_USER WITH DEFAULT_SCHEMA = DBO;
EXEC sp_addrolemember 'db_owner','TEST_USER'
GO
GRANT ALL TO TEST_USER;
GRANT ALTER ON SCHEMA::dbo TO TEST_USER
GO

When asked, I found myself being unable to explain the difference between making my user the owner of the database (in order to create/alter/drop tables etc):

EXEC sp_addrolemember 'db_owner','TEST_USER'

and granting all to the user, which I thought gives essentially the same privileges.

GRANT ALL TO TEST_USER;

Are they the same? Or is it true what my Mother always said: you can lead a horse to w开发者_如何学Cater, but that doesn't make him a DBA?

Rob

:)

Owners of a securable cannot be denied permissions. But a user that is granted ALL can still be denied by explicit DENY on a securable (DENY takes precedence over all/any GRANT). So user that is member of the group Foo can have ALL permissions granted trough the group membership, but if he is also member of group Bar that is denied SELECT on table X then the user will be denied to select from said table. If the user would be owner of table X then the DENY would not affect him. This applies to any object, at any level, including database ownership and dbo.

This would be the difference between granting ALL and ownership, but note that membership to the fixed role db_owners is different from being dbo though. Specifically, groups do not own securables, users do (or at least that how it should be... but I digress).

In addition to the owner being immune to DENY, in SQL Server GRANT ALL is deprecated, and it doesn't grant all possible permissions. (DENY ALL and REVOKE ALL are also deprecated.)

This option is deprecated and maintained only for backward compatibility. It does not grant all possible permissions. Granting ALL is equivalent to granting the following permissions.

• 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.

• If the securable is a scalar function, ALL means EXECUTE and REFERENCES.

• If the securable is a table-valued function, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

• If the securable is a stored procedure, ALL means EXECUTE.

• If the securable is a table, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

• If the securable is a view, ALL means DELETE, INSERT, REFERENCES, SELECT, and UPDATE.

And you can GRANT ALL without also using WITH GRANT OPTION.