When drawing out a logical model, always explicitly read out the one-to-one, one-to-many and many-to-many relationships. That way you will never miss out on an additional table being needed!
After answering the question, always re-read the scenario carefully, looking for factors you may have overlooked. For instance, if you’re setting up an application role, does the scenario slip in an additional function that someone needs to do outside of the application?
To enforce uniqueness in columns other than the Primary Key, you should specify a UNIQUE CONSTRAINT. This automatically creates a unique index on that column or set of columns.
With the ntext data type, you can only do comparisons IS NULL or LIKE.
To support the new bigint data type, we have two new built-in functions: BIG_COUNT and ROWCOUNT_ BIG.
Returns the last IDENTITY value inserted into an IDENTITY column in the same procedure, function or batch. Use with @@IDENTITY global variable.
In assignments from sql_variant objects to an object with any other data type, the sql_variant value must be explicitly CAST to the data type of the destination.
Temporary storage of rows. Use these in preference to temp tables. You can treat a TABLE variable like a normal table except you can’t do INSERT/SELECT INTO. Eg:
CREATE FUNCTION dbo.MyProductsTable ()
RETURNS TABLE
AS
RETURN SELECT TOP 5
ProductName AS MyProducts, UnitPrice
FROM Products
ORDER BY Products.UnitPrice
Want to update system tables? You can, if you use sp_configure to set the allow updates option on. But DON’T!! It's risky and ill-advised!
Views can only say ORDER BY if they also have a TOP clause.
When should you use views for security? Suppose you have a group of people that need access to a subset of columns from a table - use a view.
IF, however, you have all sorts of different users needing permissions to various different columns in tables, then assign column-level permissions.
Indexed Views are only possible with the Enterprise and Developer editions.
To make an indexed view, you must:
Eg:
CONSTRAINT FK_myOrder_Details FOREIGN KEY
(OrderID) REFERENCES dbo.myOrders(OrderID)
ON UPDATE NO ACTION
ON DELETE NO ACTION
NOTE: NOCHECK CONSTRAINT ALL in a table definition disables all FOREIGN KEY and CHECK constraints.
Check Constraints -
When an Instead Of Update Trigger fires, it copies the rows to be changed to a special table called deleted and copies the updated rows to a special table called inserted. You can then access these rows! How COOL is that?
Users can define 1 or more extended properties for db objects. These have a name and a value, the latter is of type SQL_VARIANT and is up to 7,500 bytes.
Manage with: sp_add/update/dropextendedproperty.
You can get the value of an extended property: FN_LISTEXTENDEDPROPERTY eg:
SELECT *
FROM ::FN_LISTEXTENDEDPROPERTY('Caption', 'User','dbo','table', 'TestExProp', default, default)
You must have CREATE FUNCTION permissions to create, alter, or drop UDFs.
A UDF cannot be used for “FOR XML” results. A “FOR XML” result must be in a stored procedure.
When a CREATE FUNCTION statement is executed successfully, the function name is stored in the sysobjects system table with types FN for scalar and TF for table valued functions.
The text of the CREATE FUNCTION statement is stored in the syscomments system table. When the function executes the first time, the function is compiled.
When calling a scalar user-defined function, you must supply at least a two-part name:
SELECT *, MyUser.MyScalarFunction()
FROM MyTable
Table-valued functions can be called by using a one-part name:
SELECT *
FROM MyTableFunction()
However, when you call SQL Server built-in functions that return a table, you must add the prefix :: to the name of the function:
SELECT * FROM ::fn_helpcollations()
Only the table or view owner can create an index.
UNIQUE - only for clustered indexes. A transaction is rolled back if it violates the uniqueness of the index - including any row updates that were valid.
IGNORE_DUP_KEY - makes it roll back ONLY those transactions that were invalid (this is true for INSERTs. For DELETEs, IGNORE_DUP_KEY has no effect).
Indexes are useful for:
Exposes the percent of each page used to store index data when the index is created. You can specify this to leave extra gaps, reducing the potential for future fragmentation.
The default fillfactor is 0. This means that the tables are 100% full but extra room is left in the index. Don’t override the default unless you can accurately predict future increases in data.
A fillfactor of 100 means that both table and index are 100% full, and should only be used for read-only tables.
A fillfactor of 10 is reasonable if you know that only a small proportion of the data exists and lots more will be added.
When you create a view or function you can specify SCHEMABINDING. This means that you’ll be warned if you try to make changes to the base tables that would invalidate the view etc. If you proceed in spite of the warning, then the view etc will have SCHEMABINDING set off so you won’t be warned about any further changes. INDEXED VIEWS automatically get SCHEMABINDING set on when you create them. Again, if you ignore the warning and make unsuitable changes to the base tables, the SCHEMABINDING will be set off AND the index/es will be deleted.
When the definition of a stored procedure, function, trigger, or view is saved in the syscomments system table, it can be encrypted (for example, if a SQL Server system contains proprietary stored procedures, functions, triggers, or views whose definitions should not be viewed by users and third parties). use WITH ENCRYPTION. This also prevents the view etc being published in replication.
SQL Server can find rows that match both criteria in a WHERE statement in a single index search operation. Put the column with more unique values first!
IMPORTANT: If you have a choice between a table-valued function and a stored procedure, choose the stored procedure if no further manipulation of the data is needed.
1. To create the stored procedure:
CREATE PROCEDURE AvgTotal @year int, @city nvarchar(20),
@total money OUTPUT AS
SELECT @total = AVG(Total)
FROM Orders o JOIN Customers c ON o.CustomerID = c.CustomerID
WHERE DATEPART = (yy, o.OrderDate) = @year AND c.City = @city
2. To call the stored procedure:
DECLARE @avg money
EXEC AvgTotal ‘2000’,’Madrid’, @avg OUTPUT
PRINT ‘Average Total is:’ + CONVERT (nvarchar,@avg)
You can’t create the following in a stored procedure:
You can also reference temp tables in a stored procedure, but if you create them in a stored procedure then they only last for its duration - so you can reference the table in a nested procedure as well.
Remote stored procedures don’t take part in transactions, so you can’t roll changes back.
If you create a stored procedure that you want others to be able to execute, make sure you qualify the object names with the owner, otherwise when it executes, it will look for the object under the name of the person who’s running it!
If you are creating a stored procedure and you want to make sure that the procedure definition cannot be viewed by other users, you can use the WITH ENCRYPTION clause. The procedure definition is then stored in an unreadable form.
After a stored procedure is encrypted, its definition cannot be decrypted and cannot be viewed by anyone, including the owner of the stored procedure or the system administrator.
When you mark a stored proc for automatic execution using sp_procoption, then scan for startup procs is set on (advanced config option. Then, the stored procs run whenever SQL Server starts. EG to create a global temp table. Use the same permissions as a sysadmin. Don’t return result sets as there is nowhere for them to go!
You CAN manually turn scan for startup procs off (sp_configure, show advanced options), but this will be done automatically when you turn the last auto proc off using sp_procoption.
You can alter a stored proc instead of deleting and re-creating it as this way it retains its permissions.
sp_helptext - shows you the text of an unencrypted sp, or a view, rule, trigger, user-defined function or a default.
sp_help - shows you info about an object. For an sp, it’s particularly useful for showing info about the sp’s parameters.
sp_depends - shows you info about an objects’ dependencies (eg for a table it will show you the views that depend on that table).
sp_helpextendedproc - information about extended stored procedures.
You can have multiple stored procedures with the same name, identified by a unique number. Members of these groups can’t be deleted individually - you have to delete the whole group.
Stored procedures are more efficient than ad hoc queries from clients, as they create less network traffic.
Replication model: Publisher, Distributor, Subscribers, Publications, articles, and subscriptions.
Replication processes: Snapshot Agent, Distribution Agent, Log Reader Agent, Queue Reader Agent, and Merge Agent.
Snapshot (complete refresh of data), Transactional (continuous or scheduled, transactional boundaries preserved) and Merge (sites work autonomously, data synchronised to schedule or on demand. Conflicts can occur on merge, so default and custom choices for conflict resolution can be defined. You may be able to avoid conflicts by using horizontal partitions).
This applies to merge replication. You can also set it up with an option for Transactional and Snapshot.
A partitioned view joins horizontally partitioned data from a set of member tables across one or more servers, making the data appear as if from one table. SQL Server 2000 distinguishes between local and distributed partitioned views. In a local partitioned view, all participating tables and the view reside on the same instance of SQL Server. In a distributed partitioned view, at least one of the participating tables resides on a different (remote) server. In addition, SQL Server 2000 differentiates between partitioned views that are updatable and views that are read-only copies of the underlying tables.
Distributed partitioned views can be used to implement a federation of database servers. A federation is a group of servers administered independently, but which cooperate to share the processing load of a system. Forming a federation of database servers by partitioning data is the mechanism that enables you to scale out a set of servers to support the processing requirements of large, multitiered Web sites.
To implement a partitioned view:
Define these on the TABLE WITH THE FOREIGN KEY! So if you’re changing say an Employee ID in the Employee table, and that column is used as a foreign key in both the Benefits and AssignedProjects tables, then you would specify ON UPDATE CASCADE in the REFERENCES clause for the Benefits and AssignedProjects tables but not in the REFERENCES clause for the Employee table.
These let you insert and retrieve large amounts of data.
Run from the command prompt, you can use it in .bat and .cmd scripts.
Used in T-SQL, stored procs and triggers. This is the quickest if the file is on the server and doesn’t have to go across the network. It’s run on the server in the context of the MSSQLServer Service. BUT you can’t transform data with BULK INSERT!
IMPORTANT: If you have a clustered index and one or two non-clustered indexes, and are going to insert 25% as much again data as is already there, then drop the indexes and then re-create them. You can set the ON UPDATE CASCASE option in the REFERENCES clause of foreign keys that use this clustered index so they are OK.
Update triggers don’t get fired when you do a BULK INSERT. Or an INSERT, for that matter. That’s why they’re called UPDATE triggers!
BULK INSERT is faster than bcp
Empty Table, Single Client
Use:
Tools that let you manipulate data from disparate sources.
DTS Package - collection of connections, DTS tasks, DTS transformations and workflow constraints.
DTS tasks are part of the DTS Object Model and include:
DTS packages can be saved to:
Usually, a package run from DTS Designer, the DTS Import/Export Wizard, the DTS Run utility, or from the command prompt executes under the security context of the user who is currently logged in. However, a package scheduled for execution runs under the security context of the owner of the SQL Server Agent job that runs the package.
When you perform a bulk copy operation that imports data into SQL Server 2000, the recovery mode of the destination database affects performance. If the database receiving the data is set for full recovery, all row insertions are fully logged. This generates a substantial number of log records during a large bulk copy operation, which can fill up the transaction log and negatively affect performance. For optimum performance during a bulk copy operation, setting the database to bulk-logged recovery is recommended. When you use bulk-logged recovery, the transaction log captures the results of bulk operations, but does not capture row-by-row insertions.
In addition, if you are loading a large amount of data from a single client into an empty table, you should specify the TABLOCK hint (rather than having SQL Server 2000 start with row locks and escalate them to table locks) and use a large batch size using the ROWS_PER_BATCH hint (large batch sizes are generally more efficient than small batch sizes). If the table into which you are importing the data has indexes, it is generally faster to drop all nonclustered indexes and re-create them after the data insertion. If the table has clustered indexes, it is generally faster to order the data in the text file to match the order in the clustered index and specify the ORDER hint.
If the table into which you are importing data is not empty and contains indexes, the decision on whether to drop indexes depends upon the amount of data being inserted compared to the amount of data existing in the table. The greater the percentage of new data, the faster it is to drop all indexes and re-create them after the data is loaded.
Manipulate heterogeneous data. Methods include linked servers, OPENQUERY, OPENROWSET, and OPENXML.
Enclose table and column names in brackets if they have a space or use a reserved word.
^ is XAND
~ is XOR
Two single quotes in a string result in one single quote in the output.
BETWEEN
LIKE - can use % for any characterS or _ for any one character. [abcd] matches with any of the characters in the range and [^abcd] matches with any character NOT in the range.
You can capture @@ERROR and @@ROWCOUNT if you do it IMMEDIATELY after an UPDATE statement - otherwise ANY other SQL statement executed will reset them.
YOU CAN ONLY ASSIGN ONE VARIABLE IN A SET STATEMENT!!!
When WITH TIES is specified, you have to have an ORDER BY clause.
IMPORTANT: “WHERE EXISTS” - there should be NOTHING in front of the EXISTS word!!
Specifies a subquery to test for the existence of rows.
The select list of a subquery introduced by EXISTS almost always consists of an asterisk (*). There is no reason to list column names because you are simply testing for the existence of rows that meet the conditions specified in the subquery.
Must always be defined in the FROM clause.
Rowversion is a synonym for the timestamp datatype. Use if for forward compatibility. You need to supply a column name for rowversion, unlike timestamp, which automatically calls itself “timestamp” if you don’t supply a column name.
ISNULL(‘this if I’m not null’, ‘this if I’m null’) - both arguments must be the same or a compatible data type.
If MyAttrib IS NULL
If MyAttrib IS NOT NULL
WHERE item1 + item2 IS NOT NULL ….. if EITHER item1 OR item2 are NULL, the sum will be NULL (amazing, huh?!)
<> NULL will only work properly if the ANSI nulls option is disabled, so prefer not to use this. Admittedly, the ANSI nulls option for a database is disabled by default, BUT BY DEFAULT it is ENABLED at the connection level for ODBC and OLE DB clients, and this will OVERRIDE the database option!!
SELECT ProductID, ProductName,
CASE Discontinued
WHEN 0 THEN ‘Discontinued’
WHEN 1 THEN ‘Active’
END AS Status
FROM Products WHERE ProductID IN (4,5)
Can use COLLATE in SQL to override the default collation.
A UNION query is like merging two tables. If you say UNION ALL, this stops duplicates getting eliminated.
Join conditions can be specified in either the FROM or WHERE clauses; specifying them in the FROM clause is recommended. Using the WHERE clause is a bad idea and will be dropped in a future version of SQL Server.
Null Values and Joins: When there are null values in the columns of the tables being joined, the null values do not match each other.
CROSS PRODUCT - combines all rows with all rows.
INNER JOIN - includes only matching rows.
LEFT OUTER JOIN - includes all left table rows and matches.
RIGHT OUTER JOIN - includes all right table rows and matches.
FULL OUTER JOIN - includes all table rows and matches.
All columns in the SELECT statement must be either in the GROUP BY or in an aggregate function!!!
In GROUP BY queries, the WHERE condition is applied before the grouping is done. The HAVING clause is applied after the grouping occurs, and, unlike WHERE in GROUP BY queries, can contain aggregate functions.
Let you query a server that’s not linked.
EG 1 - Needs OLE-DB access.
SELECT * FROM OPENROWSET (connection string with query)
EG 2 - SELECT * FROM
OPENDATASOURCE (‘Microsoft.Jet.OLEDB.4.0’, ‘Data Source = C:\nwind.mdb’)…Products
WHERE ProductId=1
Use CONTAINS(column,value)
| 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 |
| Lock mode | Description |
| Shared (S) | Used for operations that do not change or update data (read-only operations), such as a SELECT statement. Allows concurrent SELECTs. |
| Update (U) | Used on resources that can be updated. You can only have one U lock at a time. This prevents a deadlock with two transactions trying to get an X lock at the same time. |
| Exclusive (X) | Used for data-modification operations, such as INSERT, UPDATE, or DELETE. Ensures that multiple updates cannot be made to the same resource at the same time. No other transactions can read or update data with an X lock. |
| Intent | Used to establish a lock hierarchy. The types of intent locks are: intent shared (IS), intent exclusive (IX), and shared with intent exclusive (SIX). Used when a transaction wants to get an S or an X lock on a resource lower down. |
| Schema | Used when an operation dependent on the schema of a table is executing. The types of schema locks are: schema modification (Sch-M - used when a DDL statement will be executed, like adding a column or deleting a table) and schema stability (Sch-S - used when compiling queries). |
| Bulk Update (BU) | Used when bulk-copying data into a table and the TABLOCK hint is specified. |
Used for transactions at the serializable isolation level. Serializability requires that if a query is run twice in the same transaction, it needs to get the same rows back. Key-Range locking is used in this scenario to prevent phantom reads.
Lock escalation getting a coarse lock to replace many fine-grain locks. This is done automatically by SQL Server (“Dynamic Locking”) and you don’t configure it.
Deadlocking - apps should handle error 1205, where the transaction has been chosen as the deadlock victim. Code different transactions to use the resources in THE SAME ORDER to minimize the chance of deadlocking!!!!!!
Bound Connections can use the same transaction and locks. Don’t do this if they can make conflicting updates!
How do you request a cursor?
1. T-SQL i.e. DECLARE CURSOR
2. DB API cursor functions (ADO, OLE DB, ODBC & DB-Library)
An app should never mix these two ways. If you use an API cursor you would need to reset all the cursor attributes back to the defaults before doing a DECLARE CURSOR.
If neither way is used, SQL Server returns a “default result set”, i.e. the whole lot.
How does SQL Server implement a cursor?
1. T-SQL Server Cursors: Mainly used in scripts, sprocs and triggers. Implemented on the SERVER.
2. API Server Cursors: Implemented on the SERVER.
3. Client Cursors: Implemented by caching all the result set rows on the client.
Server Cursors VS Default Result Set
Don’t support multiple result sets.
Each FETCH must be sent from the client to the server.
Less efficient than default result set. Choose Server Cursors only where you need their functionality or only part of the result set is likely to be retrieved.
Server Cursors VS Client Cursors
Static cursors - complete result set built in tempdb when cursor opened - so significant delay when cursor first opened!!
Keyset-driven cursors - keyset built in tempdb when cursor opened. Can see most updates but not new rows. Only the data for the key set is built in tempdb so the initial delay is smaller.
Dynamic cursors - reflect all changes made while they are scrolling, normally when the changes have been committed, but you can set the isolation level to read uncommitted. Nothing is built in tempdb so cursor opens quicker than static or keyset cursors.
Forward-only cursors - does not support scrolling
Cursors use the same rules as normal SELECTs for locking types, but the locks are acquired at different times.
OPTIMISTIC CONCURRENCY GIVES BEST CONCURRENCY.
Extract data in XML format. Considerations include output format and XML schema structure.
FIRST:
DECLARE @idoc int
DECLARE @doc nvarchar(1000)
SET @doc = ‘
EXEC sp_xml_preparedocument @idoc OUTPUT, @doc
@doc is used to pass the XML document to the procedure. The above code gives you a relational rowset.
SECOND:
SELECT * FROM OPENXML (@idoc, ‘Root/Customers/Orders’)
WITH (CompanyName nvarchar(25) ‘../@CompanyName’)
OrderID int ‘@OrderID’,
OrderDate datetime ‘@OrderDate’,
Total money ‘@Total’)
In the above eg, @idoc is the handle for the XML document representation. The row pattern ‘Root/Customers/Orders’ identifies the lowest level in the XML document from which you need to retrive data. This is why @CompanyName is preceded by ../ because you need to go up one to the Customers level to retrieve that item.
The column patterns ‘@OrderID’, ‘@OrderDate’ and ‘@Total’ are optional in this example as the names are the same.
Column names are CASE SENSITIVE! Watch out for this on the exam!!!
XML documents can be attribute-centric, as in this eg, or element-centric, and in the case of the latter you need to set the FLAGS parameter to 2 rather than 1 (the default).
THIRD:
Do a SELECT on the rowset.
FOURTH:
Call the sp_xml_removedocument stored procedure with the @idoc parameter to delete the internal representation of the XML document and free the memory.
Syntax - FOR XML mode [, XMLDATA] [, ELEMENTS][, BINARY BASE64]
mode can be RAW, AUTO or EXPLICIT.
RAW mode - Puts each row retrieved into a generic XML element
AUTO mode - Automatically puts the retrieved data into a nested XML tree. Each table in the query becomes an XML element.
EXPLICIT - You explicity define the XML tree.
XMLDATA - Returns an XML-Data schema, which it sticks in front of the document.
ELEMENTS - Returns the columns as sub-elements. Otherwise they come back as XML attributes.
You should implement business logic at the lowest possible level (i.e. in the db where possible!)
In drag and drop examples, TRIPLE CHECK - an item you’ve already used might need to be used again later on so check each item against each possible place to insert it.
Users have an app to enter information. x, y and z columns must have values. DON’T use NOT NULL constraints on the columns, as SQL Server will only report an error on the first column. Instead, code the requirement in an AFTER INSERT trigger so you can check all of the columns that are required (in the special inserted table), and raise a custom error to the application.
SQL errors that would normally cause the statement to be cancelled and continue with the next statement in a trigger or stored procedure would cause the function to stop.
UDFs return either a scalar value or a table. Table-valued functions can be a powerful alternative to views, and can also replace stored procedures that return a single result set.
Scalar functions return a single data value of the type defined in a RETURNS clause.
The function body is in a BEGIN … END block.
INLINE table-valued functions have no function body - the table is the result set of a single SELECT statement.
Views don’t support parameters in the WHERE clause, but you can work around this with an inline table-valued function eg
CREATE FUNCTION fn_CustomersInCountry (@CountryParameter nvarchar(30))
RETURNS table
AS
RETURN (SELECT * FROM mydb.dbo.Customers
WHERE Country = @CountryParameter)
GO
MULTISTATEMENT table-valued functions have a BEGIN … END block and contain SQL statements that build and insert rows in the table to be returned.
Will look something like this:
create function MyFunction (@MyParameter1 int, @MyParameter2 int)
RETURNS @my_table TABLE
(Columns & data types for new table.)
AS
BEGIN
INSERT @my_table
SELECT statement include WHERE clause using MyParameter1 & 2.
RETURN
END
You’ll usually want to SET NOCOUNT off - stops the message indicating the number of rows affected by a statement from being returned as part of the results.
Transactional Savepoints let you roll back a portion of a transaction.
Create a savepoint: SAVE TRANSACTION mysavepoint
Roll back to the savepoint: ROLLBACK TRANSACTION mysavepoint
Savepoints are useful in situations where errors are unlikely to occur. The use of a savepoint to roll back part of a transaction in the case of an infrequent error can be more efficient than having each transaction test to see if an update is valid before making the update, for instance for an application accessed via a modem.
Troubleshoot and optimize programming objects. Objects include stored procedures, transactions, triggers, user-defined functions, and views.
How would you find out how deep a chain of stored procedures is?
Analyze the query execution plan. Considerations include query processor operations and steps.
Capture, analyze, and replay SQL Profiler traces. Considerations include lock detection, performance tuning, and trace flags.
Create and implement indexing strategies. Considerations include clustered index, covering index, indexed views, nonclustered index, placement, and statistics.
To get the number of ad-hoc queries submitted by multiple applications:
SQL:StmtCompleted:ApplicationName
To get which stored procedures are taking a long time:
SP:Completed:Duration
To get what order SQL batches are running in:
SQL:BatchStarting:StartTime
Anything that starts with SQL: is for SQL statements, so use them to monitor ad hoc queries or to DEBUG sps, but don’t use them if you’re looking at sps as whole units.
Scan events are useful if you want to see where to create indexes to avoid full table scans!
You want to determine the performance of your stored procedures.
Use:
SP:Completed
SP:Recompile
SP:CacheHit (existing execution plan found)
If auto create statistics and auto update statistics are degrading db performance, you can switch them off using db_option or the EM.
sp_configure can be used to configure server-wide options.
If db performance is degraded because statistics are out of date …
Is it one table? UPDATE STATISTICS MyTable WITH FULLSCAN / % ROWS
… Or is it the whole db? Execute sp_updatestats in the current db - it will update the stats for all the user-defined tables in the db.
Generally, for OLTP, disk I/O is always the bottleneck.
Put non-clustered indexes for frequently joined tables on separate filegroups. This gives you better performance than putting the frequently joined tables themselves on different disks. If you can do both, even better. But don’t have TOO many disks and filegroups contending for I/O, or this may degrade performance.
To grant access to a Windows NT 4.0 or Windows 2000 built-in, local group, specify BUILTIN instead of the domain or computer name.
dbo - Any member of sysadmin who uses a database is mapped to dbo in that db. Also, any object created by any member of the sysadmin fixed server role belongs to dbo automatically. Objects created by users who are not members of dbo belong to that user.
Guest - can be deleted and added to all dbs except master and tempdb, where it must always exist. By default, a guest user account does not exist in newly created databases.
Roles - are not objects so you can’t have multiple ones with the same name under different users. Roles are owned by the user specified when the role was created, or if not specified, then by the user who created the role.
Don’t nest roles too deeply as performance may be affected.
When you add a user to a role, they must already have access to the db.
Fixed server roles are external to any dbs.
Users in NT / 2000 who are members of BUILTIN\Administrators are automatically members of the sysadmin fixed server role.
Removing a SQL Server user or Windows user or group from a SQL Server database automatically removes the permissions defined for the user or group and prevents that user from using the database under the old security account. The permissions do not have to be removed separately. However, it is not possible to remove a user from SQL Server if that user currently owns objects (tables, procedures, or views) within a database. If the user owns objects, then either drop those objects before removing the user or transfer ownership to another existing user by using the sp_changeobjectowner system stored procedure.
When you grant an object permission to a user account in a database, you can optionally specify the WITH GRANT OPTION clause, which allows the user account to grant that object permission to others. When you revoke a permission granted using the WITH GRANT OPTION clause, specify the CASCADE clause to have the permissions revoked from the user account as well as any other accounts that received the permission from the initial account.
Views can depend on other views and tables. Procedures can depend on other procedures, views and tables. If all the objects that the initial one depends on have the same owner, then SQL Server only checks the permissions on the first one. If the ownership chain is broken, then SQL Server checks the permissions on each object after each break.
When should you grant REFERENCES permissions?
If a user has column-level permissions assigned and then you GRANT or DENY permissions for the whole table, then the column-level permissions disappear!
If you do it the other way round, it works OK.
|
Cassie Simons (MCSE, MCSD, MCDBA) is the author of "How to Pass Your MCSE/MCSD Quickly and Easily". Visit www.ExamsAreEasy.com today and subscribe to a unique free "Bootcamp" ezine giving you important tips and secrets to help you to certification success! |