To give SQL Server database logon user permissions using SQL statements

server| Data | database | user RIGHTS | statement GRANT
Create projects in the security system so that users in the current database can process data in the current database or execute specific Transact-SQL statements.
Grammar
Statement permissions:

GRANT {ALL | statement [,... n]} to security_account [,... N]

Object permissions:

GRANT {All [privileges] | permission [,... n]} {[(column [,... N])] on {table | view} | On {table | view} [(column [,... n])] | on {stored_procedure | extended_procedure} | on {user_defined_function}} to Security_account [,... n] [with GRANT OPTION] [as {group | role}]
Parameters
All

Indicates that all available permissions are granted. For statement permissions, all is available only to members of the sysadmin role. For object permissions, all can be used by both sysadmin and db_owner role members and database object owners.

Statement

Is the statement that is granted permission. The statement list can include:
CREATE databasecreate defaultcreate functioncreate procedurecreate rulecreate tablecreate VIEWBACKUP LOG
N

A placeholder that indicates that this item can be duplicated in a comma-delimited list.

To

Specify a list of security accounts.

Security_account

is the security account to which the permission will be applied. The security account can be:
Microsoft®sql Server™ users. SQL Server role. Microsoft Windows NT® users. Windows NT Group.
When permissions are granted to a SQL Server user or a Windows NT user account, the specified security_account is the only account that the permission can affect. If permissions are granted to the SQL Server role or to a Windows NT group, permissions can affect all users of that group or member of that role in the current database. If there is a conflict of rights between groups or roles and their members, the most restrictive permission (DENY) takes precedence. Security_account must exist in the current database, and you cannot grant permissions to users, roles, or groups in other databases unless you have created or granted access to the user in the current database.

Two special security accounts are available for the GRANT statement. The permissions granted to the public role can be applied to all users in the database. The permissions granted to the guest user are available to all users who do not have a user account in the database.

When you grant a Windows NT local group or global group permission, specify the domain name or computer name on which the group is defined, and then enter the backslash and group name in turn. However, to grant access to Windows NT built-in local groups, specify BUILTIN instead of the domain name or computer name.

Privileges

is an optional keyword that can be included in statements that conform to the SQL-92 standard.

Permission

Is the currently granted object permission. When you grant object permissions on a table, table-valued function, or view, the list of permissions can include one or more of these permissions: SELECT, INSERT, DELETE, refenences, or UPDATE. The column list can be provided with the SELECT and UPDATE permissions. If the column list is not provided with the SELECT and UPDATE permissions, the permission applies to all columns in the table, view, or table-valued function.

Object permissions granted on a stored procedure can include only EXECUTE. Object permissions granted on scalar-valued functions can include EXECUTE and REFERENCES.

To access a column in a SELECT statement, a SELECT permission is required on the column. To update a column with the UPDATE statement, update permissions are required on the column.

To create a FOREIGN KEY constraint that references a table, REFERENCES permissions are required on the table.

To create a FUNCTION or VIEW using the WITH SCHEMABINDING clause that references an object, you need REFERENCES permissions on that object.

Column

is the name of the column that is granted permissions in the current database.

Table

is the name of the table that is granted permissions in the current database.

View

is the name of the view in the current database that has been granted permissions.

Stored_procedure

is the name of the stored procedure that is granted permissions in the current database.

Extended_procedure

is the name of the extended stored procedure that is granted permissions in the current database.

User_defined_function

is the name of the user-defined function that is granted permissions in the current database.

With GRANT OPTION

Represents the ability to grant security_account the specified object permissions to other security accounts. The WITH GRANT OPTION clause is only valid for object permissions.

As {Group | role}

The optional name of the security account in the current database that has the power to execute the GRANT statement. Object permissions need to be further granted to users who are not members of a group or role when the permissions on the object are granted to a group or role. Because only the user (not the group or role) can execute the GRANT statement, the specific members of the group or role grant permissions to objects under the group or role authority.
Comments
Cross-database permissions are not allowed; only the permissions of objects and statements in the current database can be granted to users in the current database. If the user needs permissions for an object in another database, create a user account in the database, or authorize a user account to access the database and the current database.



Indicates that the system stored procedure is an exception because EXECUTE permission has been granted to the public role, allowing anyone to execute it. However, after the system stored procedure is executed, the user's role membership is checked. If this user is not a member of the appropriate fixed server or database role that is required to run this stored procedure, the stored procedure will not continue to execute.



The REVOKE statement can be used to remove granted permissions, which can be used to prevent users from obtaining permissions to their user accounts through the GRANT statement.

Grant permission to delete the denied or revoked permissions on the granted level (user, Group, or role). The same permission that is denied on another level, such as a group or role that contains this user, takes precedence. However, although the same permissions that were abolished at another level still apply, it does not prevent users from accessing the object.

If the user activates the application role, GRANT is null for any object that this user accesses through the application role. Therefore, although a user may be granted access to a specified object in the current database, if the user uses an application role that does not have access to the object, the user does not have access to the object during application role activation.

sp_helprotect System stored Procedures report permissions on database objects or users.
Permissions
Grant permissions depend on the given statement permissions and the objects involved in the permission. Members in the sysadmin role can grant any permissions in any database. Object owners can grant permissions to the objects they own. A member of the db_owner or db_securityadmin role can grant any permission on any statement or object in its database.

Statements that require permissions are those that add objects to the database or perform administrative activities on the database. Each statement that requires permission has a specific set of roles that automatically has permission to execute this statement. For example, members of the sysadmin, db_owner, and db_ddladmin roles have create TABLE permissions by default. The sysadmin and db_owner roles, and the owner of the table, default to the right to execute a SELECT statement on a table.

Some Transact-SQL statements cannot be granted permissions, and the execution of these statements requires membership in a fixed role that has the implied ability to execute special statements. For example, to execute a SHUTDOWN statement, the user must be added as a member of the serveradmin role.

Members of the dbcreator, Processadmin, securityadmin, and serveradmin fixed server roles are only authorized to execute the following Transact-SQL statements.
Statement Dbcreatorprocessadminsecurityadminserveradminbulkadminalter databasexcreate Databasexbulk INSERTXDBCCX (1) DENYX ( 2) Grantx (2) Killxreconfigurexrestorexrevokex (2) Shutdownx
(1) For more information, see DBCC statements. (2) applies only to CREATE DATABASE statements.



Explains that members of the diskadmin and setupadmin fixed server roles do not have permission to execute any Transact-SQL statements, they can only perform specific system stored procedures. However, members of the sysadmin fixed server role have permission to execute all Transact-SQL statements.



The members of the following fixed database role have permission to execute the specified Transact-SQL statement.
Statement Db_ownerdb_datareaderdb_datawriterdb_ddladmindb_backupoperatordb_securityadminalter DATABASEXXALTER Functionxxalter procedurexxalter TableX (1) xalter triggerxxalter viewx (1) xbackupxxcheckpointxxcreate DEFAULTXXCREATE Functionxxcreate Indexx (1) xcreate procedurexxcreate rulexxcreate tablexxcreate Triggerx (1) XCREATE VIEWX XDBCCXX (2) Deletex (1) Xdenyxxdeny on OBJECTXDROPX (1) Xexecutex (1) grantxxgrant on Objectx (1) insertx (1) xreadtextx (1) XREFERENC ESX (1) Xrestorexrevokexxrevoke on Objectx (1) selectx (1) xsetuserxtruncate TableX (1) Xupdatex (1) xupdate STATISTICSX ( 1) updatetextx (1) xwritetextx (1) X
(1) The permission also applies to the object owner. (2) For more information, see DBCC statements.



Indicates that members of the db_accessadmin fixed database role do not have permission to execute any Transact-SQL statements, and only specific system stored procedures are executed.



Transact-SQL statements that do not require permissions are (automatically granted public):
BEGIN transactioncommit transactionprintraiserrorrollback Transactionsave transactionset
For more information about the permissions required to execute system stored procedures, see the appropriate system stored procedures.
Example A. GRANT statement permission
The following example grants multiple statement permissions to users Mary and John and Windows NT group Corporate\BobJ.

GRANT Create DATABASE, create Tableto Mary, John, [Corporate\BobJ]
B. Granting object permissions in the permission hierarchy
The following example displays the precedence order of permissions. First, grant a SELECT permission to the public role. Then, give the user Mary, John, and Tom a specific permission. These users then have all the permissions on the authors table.

Use pubsgogrant Selecton authorsto publicgo GRANT INSERT, UPDATE, Deleteon authorsto Mary, John, Tomgo
C. grant permissions to SQL Server role
The following example grants the CREATE TABLE permission to all members of the Accounting role.

GRANT CREATE TABLE to Accounting
D. Granting permissions with the AS option
User Jean owns the table Plan_Data. Jean grants the SELECT permission for the table Plan_Data to the Accounting role (specifying the WITH GRANT OPTION clause). The user, Jill, is a member of the Accounting who will grant the SELECT permission on the table Plan_Data to the user jack,jack is not a member of Accounting.

Because the permissions granted to a table plan_data the other user's SELECT permission with the GRANT statement are granted to the Accounting role instead of being explicitly granted to Jill, you cannot allow Jill to grant permissions to the table because it has been granted to members of the Accounting role. Jill must use the AS clause to get permission to grant the Accounting role.

/* User Jean */grant Select on Plan_Data to Accounting and GRANT option/* User Jill */grant SELECT on Plan_Data to Jack A S Accounting