Microsoft SQL Server object privileges can be granted to, denied from, and revoked from other database users, database groups, and the public role. SQL Server does not allow an object owner to grant ALTER TABLE and CREATE INDEX privileges for the object as Oracle does. Those privileges must remain with the object owner.
The GRANT statement creates an entry in the security system that allows a user in the current database to work with data in the current database or to execute specific Transact-SQL statements. The syntax of the GRANT statement is identical in Oracle and SQL Server.
The DENY statement creates an entry in the security system that denies a permission from a security account in the current database and prevents the security account from inheriting the permission through its group or role memberships. Oracle does not have a DENY statement. The REVOKE statement removes a previously granted or denied permission from a user in the current database.
| Oracle | Microsoft SQL Server |
|---|---|
| GRANT {ALL [PRIVILEGES][column_list] | permission_list [column_list]} ON {table_name [(column_list)] | view_name [(column_list)] | stored_procedure_name} TO {PUBLIC | name_list } [WITH GRANT OPTION] |
GRANT {ALL [PRIVILEGES] | permission[,…n]} { [(column[,…n])] ON {table | view} | ON {table | view}[(column[,…n])] | ON {stored_procedure | extended_procedure} } TO security_account[,…n] [WITH GRANT OPTION] [AS {group | role}] REVOKE [GRANT OPTION FOR] DENY |
For more information about object-level permissions, see SQL Server Books Online.
In Oracle, the REFERENCES privilege can be granted only to a user. SQL Server allows the REFERENCES privilege to be granted to both database users and database groups. The INSERT, UPDATE, DELETE, and SELECT privileges are granted in the same way in both Oracle and SQL Server.