This answer is about creating login and configure users for databases with Transact-SQL statements. Granting a user access to a database involves three steps. First, you create a login. The login lets the user connect to the SQL Server Database Engine. Then you configure the login as a user in the specified database. And finally, you grant that user permission to database objects. This answer shows you these three steps, and shows you how to create a view and a stored procedure as the object. This tutorial assumes that you are running SQL Server Management Studio Express.

As an administrator, you can execute the SELECT from the Products table and the vw_Names view, and execute the pr_Names procedure; however, Mary cannot. To grant Mary the necessary permissions, use the GRANT statement.

Procedure Title - Execute the following statement to give Mary the EXECUTE permission for the pr_Names stored procedure.

GRANT EXECUTE ON pr_Names TO Mary;

GO