FAQ on Permissions in SQL Server

FAQ on Permissions in SQL Server

149. A user is a member of Public role and Sales role. Public role has
the permission to select on all the table, and Sales role, which
doesn't have a select permission on some of the tables. Will that user
be able to select from all tables?
**

150. If a user does not have permission on a table, but he has
permission to a view created on it, will he be able to view the data
in table?
Yes.

151. Describe Application Role and explain a scenario when you will
use it?
**

152. What is the difference between the REPEATABLE READ and SERIALIZE
isolation levels?

The level at which a transaction is prepared to accept inconsistent
data is termed the isolation level. The isolation level is the degree
to which one transaction must be isolated from other transactions. A
lower isolation level increases concurrency, but at the expense of
data correctness. Conversely, a higher isolation level ensures that
data is correct, but can affect concurrency negatively. The isolation
level required by an application determines the locking behavior SQL
Server uses.
SQL-92 defines the following isolation levels, all of which are
supported by SQL Server:
• Read uncommitted (the lowest level where transactions are isolated
only enough to ensure that physically corrupt data is not read).
• Read committed (SQL Server default level).
• Repeatable read.
• Serializable (the highest level, where transactions are completely
isolated from one another).
Isolation level Dirty read Nonrepeatable read Phantom
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Serializable No No No

153. Uncommitted Dependency (Dirty Read) - Uncommitted dependency
occurs when a second transaction selects a row that is being updated
by another transaction. The second transaction is reading data that
has not been committed yet and may be changed by the transaction
updating the row. For example, an editor is making changes to an
electronic document. During the changes, a second editor takes a copy
of the document that includes all the changes made so far, and
distributes the document to the intended audience.
Inconsistent Analysis (Nonrepeatable Read) Inconsistent analysis
occurs when a second transaction accesses the same row several times
and reads different data each time. Inconsistent analysis is similar
to uncommitted dependency in that another transaction is changing the
data that a second transaction is reading. However, in inconsistent
analysis, the data read by the second transaction was committed by the

transaction that made the change. Also, inconsistent analysis involves
multiple reads (two or more) of the same row and each time the
information is changed by another transaction; thus, the term
nonrepeatable read. For example, an editor reads the same document
twice, but between each reading, the writer rewrites the document.
When the editor reads the document for the second time, it has changed.
Phantom Reads Phantom reads occur when an insert or delete action is
performed against a row that belongs to a range of rows being read by
a transaction. The transaction's first read of the range of rows shows
a row that no longer exists in the second or succeeding read, as a
result of a deletion by a different transaction. Similarly, as the
result of an insert by a different transaction, the transaction's
second or succeeding read shows a row that did not exist in the
original read. For example, an editor makes changes to a document
submitted by a writer, but when the changes are incorporated into the
master copy of the document by the production department, they find
that new unedited material has been added to the document by the
author. This problem could be avoided if no one could add new material
to the document until the editor and production department finish
working with the original document.

154. After removing a table from database, what other related objects
have to be dropped explicitly?
(view, SP)

155. You have a SP names YourSP and have the a Select Stmt inside the
SP. You also have a user named YourUser. What permissions you will
give him for accessing the SP.
**

156. Different Authentication modes in Sql server? If a user is logged
under windows authentication mode, how to find his userid?
There are Three Different authentication modes in sqlserver.
0. Windows Authentication Mode
1. SqlServer Authentication Mode
2. Mixed Authentication Mode
"system_user" system function in sqlserver to fetch the logged on user

name.

157. Give the connection strings from front-end for both type
logins(windows,sqlserver)?
This are specifically for sqlserver not for any other RDBMS
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Integrated
Security=SSPI (windows)
Data Source=MySQLServer;Initial Catalog=NORTHWIND;Uid=" ";Pwd="
"(sqlserver)

158. What are three SQL keywords used to change or set someone's
permissions?
Grant, Deny and Revoke


For More SQL SERVER Frequently Asked Interview Questions