SQL Server Permissions – Why is dbo the Only Grantor?

permissionssql servert-sql

I can't understand why is dbo the only grantor in SQL Server? Or am I wrong?

I mean it as follow:
No matter as which user I am currently loged in in SQL Server Management Studio, when I go to properties of a database under Securables and grant some permissions to a user and save it and come back to Securables: dbo is shown as Grantor for those permissions, which I granted before and not the current loged in user.

Why is it so?

Under which scenario could I see another user than dbo as grantor?

Thanks

Best Answer

Probably because all your logins are sysadmin, and sysadmin always connects as dbo.

Try something like:

create user fred without login

create user joe without login

grant select to fred with grant option

execute as user='fred'
  grant select to joe
revert


select * from sys.database_permissions

Related Solutions

Sql-server – Understanding SQL Server permissions

db_datawriter has no items in the permissions page because it doesn't have explicit object permissions as such. Rights are implied by the role.

MSDN for db_datawriter says

Members of the db_datawriter fixed database role can add, delete, or change data in all user tables.

It has INSERT, UPDATE, DELETE on tables as per another MSDB page Permissions of Fixed Database Roles

Granted: DELETE, INSERT, UPDATE

Finally, what does the DB engine say (SQL Server 2008 R2)?

EXEC sp_dbfixedrolepermission 'db_datawriter'

db_datawriter   DELETE permission on any object
db_datawriter   INSERT permission on any object
db_datawriter   UPDATE permission on any object

The MSDN pages for SQL Server 2008 are here (different page hierarchy)

SQL Server – Hierarchy of Permissions for Schema

Quick answer: For developers, you can GRANT CONTROL on that schema.

Background:

To see what can be GRANTed: GRANT Schema Permissions
To see what each permission means: Permissions Naming Conventions

The intersection of these two give the schema permission meanings:

CONTROL implies the rest and is the highest permissions of any securable
SELECT, DELETE, INSERT, UPDATE is DML on objects in that schema
EXECUTE to run scalar UDFs and Stored Procedures in that schema
ALTER, REFERENCES is DDL (CREATE, ALTER, DROP) on objects in that schema
VIEW DEFINITION lets folk see the code and definitions

Except for CONTROL, these are mostly orthogonal to each other: folk can EXECUTE code or SELECT, from a view but not see the definition (VIEW DEFINITION) of these.

You also have the database and server permissions which are implied higher up then CONTROL on schemas: see Permissions Hierarchy

Best Answer

Related Solutions

Sql-server – Understanding SQL Server permissions

SQL Server – Hierarchy of Permissions for Schema

Related Question