Configuring Permissions on Database Objects

Configuring Permissions on Database Objects

Creating a Login

To access the Database Engine, users require a login. The login can represent the user's identity as a Windows account or as a member of a Windows group, or the login can be a SQL Server login that exists only in SQL Server. Whenever possible you should use Windows Authentication. By default, administrators on your computer have full access to SQL Server. For this lesson, we want to have a less privileged user; therefore, you will create a new local Windows Authentication account on your computer. To do this, you must be an administrator on your computer. Then you will grant that new user access to SQL Server.

Procedures

·         To create a new Windows account

1. Click Start, click Run, in the Open box, type %SystemRoot%\system32\compmgmt.msc /s, and then click OK to open the Computer Management program.

2. Under System Tools, expand Local Users and Groups, right-click Users, and then click New User.

3. In the User name box type Deepanshu.

4. In the Password and Confirm password box, type a strong password, and then click Create to create a new local Windows user.(as show below)

·         To create a login
1. In a Query Editor window of SQL Server Management Studio, type and execute the following code replacing computer_name with the name of your computer. FROM WINDOWS indicates that Windows will authenticate the user. The optional DEFAULT_DATABASE argument connects Deepanshu to the TestData database, unless her connection string indicates another database. This statement introduces the semicolon as an optional termination for a Transact-SQL statement. CREATE LOGIN [computer_name\Deepanshu] FROM WINDOWS WITH DEFAULT_DATABASE = [TestData]; GO This authorizes a user name Deepanshu, authenticated by your computer, to access this instance of SQL Server. If there is more than one instance of SQL Server on the computer, you must create the login on each instance that Deepanshu must access.
Note Because Deepanshu is not a domain account, this user name can only be authenticated on this computer.

Granting Access to a Database

Deepanshu now has access to this instance of SQL Server, but does not have permission to access the databases. She does not even have access to her default database TestData until you authorize her as a database user. To grant Deepanshu access, switch to the TestData database, and then use the CREATE USER statement to map her login to a user named Deepanshu.

Procedures

1.       Type and execute the following statements (replacing computer_name with the name of your computer) to grant Deepanshu access to the TestData database. USE [TestData]; GO CREATE USER [Deepanshu] FOR LOGIN [computer_name\Deepanshu]; GO Now, Deepanshu has access to both SQL Server and the TestData database.

Granting Access to a Database Object

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

1. Execute the following statement to give Deepanshu the EXECUTE permission for the pr_Names stored procedure. GRANT EXECUTE ON pr_Names TO Deepanshu; GO

Note : If the table, the view, and the stored procedure are not owned by the same schema, granting permissions becomes more complex.

About GRANT

You must have EXECUTE permission to execute a stored procedure. You must have SELECT, INSERT, UPDATE, and DELETE permissions to access and change data. The GRANT statement is also used for other permissions, such as permission to create tables.

Summary: Configuring Permissions on Database Objects

Logins give users permissions to connect to SQL Server. Users are logins that can access a specific database. Use the GRANT statement to give users permission to read and to access and change the data. A view is a single SELECT statement and looks like a table to the user. A stored procedure is one or more Transact-SQL statements that execute as a batch.