Home > Articles > Data > SQL Server

Statement Permissions

  • Print
  • + Share This
Statement permissions allow a database user, database role, or Windows user or group to perform various tasks such as creating databases, creating objects, or backing up the database. Statement permissions allow a user to run a particular command (or set of commands) rather than merely manipulate a particular object.

Statement permissions allow a database user, database role, or Windows user or group to perform various tasks such as creating databases, creating objects, or backing up the database. Statement permissions allow a user to run a particular command (or set of commands) rather than merely manipulate a particular object.

You should carefully consider granting a user or role permissions to create an object. When a user creates an object, she becomes the owner of that object (unless the creator specifies the owner as dbo when she creates it) and has all the permissions associated with database object ownership.

Statement permissions should be granted only when explicitly needed. The haphazard granting of statement permissions can leave a database with unnecessary and even unusable objects.

You can grant statement permissions to individual database users, Windows users/groups, or database roles, including the public role. The statement permissions that can be granted, revoked, or denied include CREATE DATABASE, CREATE TABLE, CREATE PROCEDURE, CREATE DEFAULT, CREATE RULE, CREATE VIEW, CREATE FUNCTION, BACKUP DATABASE, and BACKUP LOG.

You can grant these permissions individually or all at once (by using the keyword ALL). Each command has implications that must be considered before you use it.

The CREATE DATABASE Permission

The CREATE DATABASE permission enables users to create their own databases and thus become the dbo of those databases. Database ownership can later be changed with the sp_changedbowner system stored procedure. Only members of the sysadmin or dbcreator fixed server role are allowed to grant a user the CREATE DATABASE permissions. Because permissions are always granted to users (and never to logins), you must grant this permission in the master database only. This statement permission doesn't exist in any other database. The CREATE DATABASE permission also grants you rights to use the ALTER DATABASE command. In other words, you can't use ALTER DATABASE unless you have the CREATE DATABASE permission.

TIP

Using the dbcreator fixed server role is much better than granting the CREATE DATABASE statement permission. You usually need the other rights granted by the dbcreator fixed server role anyway, and figuring out who has what rights is easier when you take advantage of SQL Server roles.

  • + Share This
  • 🔖 Save To Your Account

Related Resources

There are currently no related titles. Please check back later.