【免费下载】SQLSERVER使用手册
SQL Server 权限管理手册说明书
Top Level Server PermissionsDatabase Level PermissionsALTER ANY APPLICATION ROLE ALTER ANY ASSEMBLY ALTER ANY ASYMMETRIC KEY ALTER ANY CERTIFICATE ALTER ANY CONTRACTALTER ANY DATABASE AUDIT ALTER ANY DATABASE DDL TRIGGERALTER ANY DATABASE EVENT NOTIFICATION ALTER ANY DATASPACEALTER ANY FULLTEXT CATALOGALTER ANY MESSAGE TYPEALTER ANY REMOTE SERVICE BINDING ALTER ANY ROLE ALTER ANY ROUTE ALTER ANY SCHEMA ALTER ANY SERVICE ALTER ANY SYMMETRIC KEYALTER ANY USER –See Connect and Authentication –Database Permissions ChartCREATE AGGREGATE CREATE DEFAULT CREATE FUNCTION CREATE PROCEDURE CREATE QUEUE CREATE RULE CREATE SYNONYM CREATE TABLE CREATE TYPE CREATE VIEWCREATE XML SCHEMA COLLECTIONTop Level Database PermissionsCONTROL ON DATABASE::<name>CREATE ASSEMBLY CREATE ASYMMETRIC KEY CREATE CERTIFICATE CREATE CONTRACTCREATE DATABASE DDL EVENT NOTIFICATIONCREATE FULLTEXT CATALOG CREATE MESSAGE TYPECREATE REMOTE SERVICE BINDING CREATE ROLE CREATE ROUTE CREATE SCHEMA CREATE SERVICE CREATE SYMMETRIC KEYAUTHENTICATE BACKUP DATABASE BACKUP LOG CHECKPOINTCONNECT REPLICATION DELETE EXECUTE INSERT REFERENCES SELECT UPDATEVIEW DEFINITION TAKE OWNERSHIP SHOWPLANSUBSCRIBE QUERY NOTIFICATIONS VIEW DATABASE STATECONTROL SERVERCONNECT DATABASESTATEMENTS:CREATE DATABASE AUDIT SPECIFICATION CREATE/ALTER/DROP database triggersPARTITION & PLAN GUIDE statementsSTATEMENTS:Combined with TRUSTWORTHY allows delegation of authentication BACKUP DATABASE BACKUP LOG CHECKPOINTCREATE ANY DATABASE ALTER ANY DATABASEALTER ANY SERVER AUDIT ALTER ANY EVENT NOTIFICATIONAUTHENTICATE SERVERVIEW ANY DEFINITIONALTER TRACEVIEW SERVER STATE STATEMENTS:Applies to subordinate objects in the database. See Database Permissions –Schema Objects chart.TAKE OWNERSHIP ON OBJECT|TYPE|XML SCHEMA COLLECTION::<name>RECEIVE ON OBJECT::<queue name>SELECT ON OBJECT::<queue name>VIEW CHANGE TRACKING ON OBJECT::<name> SELECT ON OBJECT::<table |view name>INSERT ON OBJECT::< table |view name> UPDATE ON OBJECT::< table |view name> DELETE ON OBJECT::< table |view name>EXECUTE ON OBJECT|TYPE|XML SCHEMA COLLECTION::<name> REFERENCES ON OBJECT|TYPE|XML SCHEMA COLLECTION:<name> VIEW DEFINITION ON OBJECT|TYPE|XML SCHEMA COLLECTION::<name>ALTER ON OBJECT|TYPE|XML SCHEMA COLLECTION::<name>TAKE OWNERSHIP ON SCHEMA::<name>VIEW CHANGE TRACKING ON SCHEMA::<name>SELECT ON SCHEMA::<name>INSERT ON SCHEMA::<name>UPDATE ON SCHEMA::<name>DELETE ON SCHEMA::<name>EXECUTE ON SCHEMA::<name>REFERENCES ON SCHEMA::<name>VIEW DEFINITION ON SCHEMA::<name>ALTER ON SCHEMA::<name>CREATE SEQUENCESELECT ON DATABASE::<name>INSERT ON DATABASE::<name>UPDATE ON DATABASE::<name>DELETE ON DATABASE::<name>EXECUTE ON DATABASE::<name>REFERENCES ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>TAKE OWNERSHIP ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY SCHEMACREATE SCHEMACREATE AGGREGATE CREATE DEFAULT CREATE FUNCTION CREATE PROCEDURE CREATE QUEUE CREATE RULE CREATE SYNONYM CREATE TABLE CREATE TYPE CREATE VIEWCREATE XML SCHEMA COLLECTIONVIEW ANY DEFINITIONVIEW ANY DATABASEALTER ANY DATABASEServer PermissionsDatabase PermissionsSchema PermissionsObject Permissions Type PermissionsXML Schema Collection PermissionsDatabase Permissions –Schema ObjectsNotes:•To create a schema object (such as a table) you must have CREATE permission for that object type plus ALTER ON SCHEMA::<name> for the schema of the object. Might require REFERENCES ON OBJECT::<name> for any referenced CLR type or XML schema collection.•To alter an object (such as a table) you must have ALTER permission on the object (or schema ),or CONTROL permission on the object.CONTROL ON SERVERCONTROL ON DATABASE::<name>CONTROL ON SCHEMA ::<name>CONTROL ON OBJECT|TYPE|XML SCHEMA COLLECTION ::<name>OBJECT permissions apply to the following database objects:AGGREGATE DEFAULT FUNCTION PROCEDURE QUEUE RULE SYNONYM TABLE VIEW(All permissions do not apply to all objects. For example UPDATE only applies to tables and views.)•To drop an object (such as a table) you must have ALTER permission on the schema or CONTROL permission on the object.•To create an index requires ALTER OBJECT::<name> permission on the table or view.•To create or alter a trigger on a table or view requires ALTER OBJECT::<name> on the table or view.•To create statistics requires ALTER OBJECT::<name> on the table or view.CONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASECONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY FULLTEXT CATALOGCREATE FULLTEXT CATALOG Certificate PermissionsFull-text PermissionsAssembly PermissionsQuestions and comments to ************************Server Role PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY SERVER ROLEVIEW DEFINITION ON SERVER ROLE::<name>TAKE OWNERSHIP ON SERVER ROLE::<name>ALTER ON SERVER ROLE::<name>CONTROL ON SERVER ROLE::<name>Most permission statements have the format :AUTHORIZATION PERMISSION ON SECURABLE::NAME TO PRINCIPAL•AUTHORIZATION must be GRANT, REVOKE or DENY.•PERMISSION is listed in the charts below.•ON SECURABLE::NAME is the server, server object, database, or database object and its name. Some permissions do not require ON SECURABLE::NAME.•PRINCIPAL is the login, user, or role which receives or loses the permission. Grant permissions to roles whenever possible.Sample grant statement: GRANT UPDATE ON OBJECT::Production.Parts TO PartsTeam Denying a permission at any level, overrides a related grant.To remove a previously granted permission, use REVOKE, not DENY.NOTES:•The CONTROL SERVER permission has all permissions on the instance of SQL Server.•The CONTROL DATABASE permission has all permissions on the database.•Permissions do not imply role memberships and role memberships do not grant permissions. (E.g. CONTROL SERVER does not imply membership in the sysadmin fixed server role. Membership in the db_owner role does not grant the CONTROL DATABASE permission.) However, it is sometimes possible to impersonate between roles and equivalent permissions.•Granting any permission on a securable allows VIEW DEFINITION on that securable. It is an implied permissions and it cannot be revoked, but it can be explicitly denied by using the DENY VIEW DEFINITION statement.Server Level PermissionsNotes:•Creating a full-text index requires ALTER permission on the table and REFERENCES permission on the full-text catalog.•Dropping a full-text index requires ALTER permission on the table.STATEMENTS:DROP DATABASEMarch 28, 2014How to Read this Chart•Most of the more granular permissions are included in more than one higher level scope permission. So permissions can be inherited from more than one type of higher scope.•Black, green, and blue arrows and boxes point to subordinate permissions that are included in the scope of higher a level permission.•Brown arrows and boxes indicate some of the statements that can use the permission.CREATE SERVER ROLEAvailability Group PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY AVAILABILITY GROUPVIEW DEFINITION ON AVAILABILITY GROUP::<name>TAKE OWNERSHIP ON AVAILABILITY GROUP::<name>ALTER ON AVAILABILITY GROUP::<name>CONTROL ON AVAILABILITY GROUP::<name>CREATE AVAILABILITY GROUPADMINISTER BULK OPERATIONSALTER ANY AVAILABILITY GROUP –See Availability Group PermissionsCREATE AVAILABILTY GROUPALTER ANY CONNECTION ALTER ANY CREDENTIALALTER ANY DATABASE –See Database Permission ChartsCREATE ANY DATABASE –See Top Level Database PermissionsALTER ANY ENDPOINT –See Connect and AuthenticationCREATE ENDPOINT –See Connect and AuthenticationALTER ANY EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATION CREATE TRACE EVENT NOTIFICATIONALTER ANY EVENT SESSION ALTER ANY LINKED SERVERALTER ANY LOGIN –See Connect and Authentication ALTER ANY SERVER AUDITALTER ANY SERVER ROLE –See Server Role PermissionsCREATE SERVER ROLE –See Server Role PermissionsALTER RESOURCES (Not used. Use diskadmin fixed server role instead.)ALTER SERVER STATEVIEW SERVER STATEALTER SETTINGS ALTER TRACEAUTHENTICATE SERVERCONNECT SQL –See Connect and Authentication CONNECT ANY DATABASE IMPERSONATE ANY LOGIN SELECT ALL USER SECURABLES SHUTDOWN UNSAFE ASSEMBLYEXTERNAL ACCESS ASSEMBLYVIEW ANY DEFINITIONVIEW ANY DATABASE –See Database Permissions –Schema* NOTE:The SHUTDOWN statement requires the SQL Server SHUTDOWN permission. Starting, stopping, and pausing the Database Engine from SSCM, SSMS, or Windows requires Windows permissions, not SQL Server permissions.STATEMENTS:CREATE/ALTER/DROP server triggers OPENROWSET(BULK….KILL CREATE/ALTER/DROP CREDENTIAL DBCC FREE…CACHE and SQLPERF SELECT on server-level DMV’s sp_configure, RECONFIGURE sp_create_traceAllows server-level delegationCONTROL SERVERSTATEMENTS:CREATE/ALTER/DROP server triggers OPENROWSET(BULK …KILLServer scoped event notifications Server scoped DDL event notifications Event notifications on trace events Extended event sessions sp_addlinkedserverDBCC FREE…CACHE and SQLPERF SELECT on server-level DMV’s sp_configure, RECONFIGURE sp_trace_create Allows server-level delegation SHUTDOWN*CREATE/ALTER/DROP SERVER AUDIT and SERVER AUDIT SPECIFICATION CONTROL SERVERVIEW ANY DEFINITION ALTER ANY LOGINCONNECT SQLCONTROL ON LOGIN::<name>Connect and Authentication –Server PermissionsVIEW ANY DEFINITIONALTER ANY ENDPOINTCREATE ENDPOINTCONNECT ON ENDPOINT::<name>TAKE OWNERSHIP ON ENDPOINT::<name>VIEW DEFINITION ON ENDPOINT::<name>ALTER ON ENDPOINT::<name>CONTROL ON ENDPOINT::<name>Notes:•The CREATE LOGIN statement creates a login and grants CONNECT SQL to that login.•Enabling a login (ALTER LOGIN <name> ENABLE) is not the same as granting CONNECT SQL permission.•To map a login to a credential, see ALTER ANY CREDENTIAL.•When contained databases are enabled, users can access SQL Server without a login. See database user permissions.•To connect using a login you must have :o An enabled login o CONNECT SQLoCONNECT for the database (if specified)VIEW DEFINITION ON LOGIN::<name>IMPERSONATE ON LOGIN::<name>ALTER ON LOGIN::<name>STATEMENTS:ALTER LOGIN, sp_addlinkedsrvlogin DROP LOGIN CREATE LOGINSTATEMENTS:ALTER ENDPOINT DROP ENDPOINTCREATE ENDPOINTSTATEMENTS:ALTER SERVER ROLE <name> ADD MEMBER DROP SERVER ROLECREATE SERVER ROLESTATEMENTS:ALTER AVAILABILITY GROUP DROP AVAILABILITY GROUPCREATE AVAILABILITY GROUPCONTROL ON FULLTEXT CATALOG::<name>VIEW DEFINITION ON FULLTEXT CATALOG::<name>REFERENCES ON FULLTEXT CATALOG::<name>TAKE OWNERSHIP ON FULLTEXT CATALOG::<name>ALTER ON FULLTEXT CATALOG::<name>STATEMENTS:ALTER FULLTEXT CATALOG CREATE FULLTEXT CATALOGDatabase Role PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY ROLE CREATE ROLE CONTROL ON DATABASE::<name>VIEW DEFINITION ON ROLE::<name>TAKE OWNERSHIP ON ROLE::<name>ALTER ON ROLE::<name>CONTROL ON ROLE::<name>STATEMENTS:ALTER ROLE <name> ADD MEMBER DROP ROLECREATE ROLESymmetric Key PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY SYMMETRIC KEYCREATE SYMMETRIC KEY CONTROL ON DATABASE::<name>VIEW DEFINITION ON SYMMETRIC KEY::<name>REFERENCES ON SYMMETRIC KEY::<name>TAKE OWNERSHIP ON SYMMETRIC KEY::<name>ALTER ON SYMMETRIC KEY::<name>CONTROL ON SYMMETRIC KEY::<name>STATEMENTS:ALTER SYMMETRIC KEY DROP SYMMETRIC KEY CREATE SYMMETRIC KEYNote: OPEN SYMMETRIC KEY requires VIEW DEFINITION permission on the key (implied by any permission on the key), and requires permission on the key encryption hierarchy.Asymmetric Key PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY ASYMMETRIC KEYCREATE ASYMMETRIC KEYCONTROL ON DATABASE::<name>VIEW DEFINITION ON ASYMMETRIC KEY::<name>REFERENCES ON ASYMMETRIC KEY::<name>TAKE OWNERSHIP ON ASYMMETRIC KEY::<name>ALTER ON ASYMMETRIC KEY::<name>CONTROL ON ASYMMETRIC KEY::<name>STATEMENTS:ALTER ASYMMETRIC KEY DROP ASYMMETRIC KEYCREATE ASYMMETRIC KEYNote: ADD SIGNATURE requires CONTROL permission on the key, andrequires ALTER permission on the object.CONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY CERTIFICATE CREATE CERTIFICATE CONTROL ON DATABASE::<name>VIEW DEFINITION ON CERTIFICATE::<name>REFERENCES ON CERTIFICATE::<name>TAKE OWNERSHIP ON CERTIFICATE::<name>ALTER ON CERTIFICATE::<name>CONTROL ON CERTIFICATE::<name>STATEMENTS:ALTER CERTIFICATE DROP CERTIFICATECREATE CERTIFICATENote: ADD SIGNATURE requiresCONTROL permission on the certificate, and requires ALTER permission on the object.CONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY ASSEMBLY CREATE ASSEMBLYCONTROL ON DATABASE::<name>VIEW DEFINITION ON ASSEMBLY::<name>REFERENCES ON ASSEMBLY::<name>TAKE OWNERSHIP ON ASSEMBLY::<name>ALTER ON ASSEMBLY::<name>CONTROL ON ASSEMBLY::<name>STATEMENTS:ALTER ASSEMBLYDROP ASSEMBLYCREATE ASSEMBLYEvent Notification PermissionsCONTROL SERVERALTER ANY EVENT NOTIFICATIONCREATE DDL EVENT NOTIFICATIONCREATE TRACE EVENT NOTIFICATIONALTER ON DATABASE::<name>ALTER ANY DATABASE EVENT NOTIFICATION CREATE DATABASE DDL EVENT NOTIFICATIONCONTROL ON DATABASE::<name>Database scoped event notificationsDatabase scoped DDL event notificationsEvent notifications on trace eventsNote: EVENT NOTIFICATION permissions also affect service broker. See the service broker chart for more into.Connect and Authentication –Database PermissionsCONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASEVIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY USER CONNECT ON DATABASE::<name>CONTROL ON DATABASE::<name>VIEW DEFINITION ON USER::<name>IMPERSONATE ON USER::<name>ALTER ON USER::<name>CONTROL ON USER::<name>STATEMENTS:ALTER USER DROP USER CREATE USERNOTES:•When contained databases are enabled, creating a database user that authenticates at the database, grants CONNECT DATABASE to that user,and it can access SQL Server without a login.•Granting ALTER ANY USER allows a principal to create a user based on a login, but does not grant the server level permission to view information about logins.Replication PermissionsCONTROL SERVERCONTROL ON DATABASE::<name>CONNECT REPLICATION ON DATABASE::<name>CONNECT ON DATABASE::<name>Application Role PermissionsCONTROL SERVERVIEW ANY DEFINITION ALTER ANY DATABASE CONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY APPLICATION ROLECONTROL ON APPLICATION ROLE::<name>VIEW DEFINITION ON APPLICATION ROLE::<name>ALTER ON APPLICATION ROLE::<name>STATEMENTS:ALTER APPLICATION ROLE DROP APPLICATION ROLE CREATE APPLICATION ROLESTATEMENTS:DROP FULLTEXT CATALOG DROP FULLTEXT STOPLISTDROP FULLTEXT SEARCH PROPERTYLISTCONTROL ON FULLTEXT STOPLIST::<name>VIEW DEFINITION ON FULLTEXT STOPLIST::<name>REFERENCES ON FULLTEXT STOPLIST::<name>TAKE OWNERSHIP ON FULLTEXT STOPLIST::<name>ALTER ON FULLTEXT STOPLIST::<name>STATEMENTS:ALTER FULLTEXT STOPLIST CREATE FULLTEXT STOPLISTCONTROL ON SEARCH PROPERTY LIST::<name>VIEW DEFINITION ON SEARCH PROPERTY LIST::<name>REFERENCES ON SEARCH PROPERTY LIST::<name>TAKE OWNERSHIP ON SEARCH PROPERTY LIST::<name>ALTER ON SEARCH PROPERTY LIST::<name>STATEMENTS:ALTER SEARCH PROPERTY LIST CREATE SEARCH PROPERTY LISTService Broker PermissionsNotes:•The user executing the CREATE CONTRACT statement must have REFERENCES permission on all message typesspecified.•The user executing the CREATE SERVICE statement must have REFERENCES permission on the queue and allcontracts specified.•To execute the CREATE or ALTER REMOTE SERVICE BINDING the user must have impersonate permission forthe principal specified in the statement.•When the CREATE or ALTER MESSAGE TYPE statement specifies a schema collection, the user executing thestatement must have REFERENCES permission on the schema collection specified.•See the ALTER ANY EVENT NOTIFICATION chart for more permissions related to Service Broker.•See the SCHEMA OBJECTS chart for QUEUE permissions.•The ALTER CONTRACT permission exists but at this time there is no ALTER CONTRACT statement.CONTROL ON REMOTE SERVICE BINDING::<name>VIEW DEFINITION ON REMOTE SERVICE BINDING::<name>TAKE OWNERSHIP ON REMOTE SERVICE BINDING::<name>ALTER ON REMOTE SERVICE BINDING::<name>STATEMENTS:ALTER REMOTE SERVICE BINDINGDROP REMOTE SERVICE BINDINGCREATE REMOTE SERVICE BINDINGCONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASECONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY REMOTE SERVICE BINDINGCREATE REMOTE SERVICE BINDINGCONTROL ON CONTRACT::<name>VIEW DEFINITION ON CONTRACT::<name>REFERENCES ON CONTRACT::<name>TAKE OWNERSHIP ON CONTRACT::<name>ALTER ON CONTRACT::<name>STATEMENTS:DROP CONTRACTCREATE CONTRACTCONTROL SERVER VIEW ANY DEFINITIONALTER ANY DATABASECONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY CONTRACTCREATE CONTRACTCONTROL ON SERVICE::<name>VIEW DEFINITION ON SERVICE::<name>SEND ON SERVICE::<name>TAKE OWNERSHIP ON SERVICE::<name>ALTER ON SERVICE::<name>STATEMENTS:ALTER SERVICE DROP SERVICECREATE SERVICECONTROL SERVERVIEW ANY DEFINITION ALTER ANY DATABASE CONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY SERVICECREATE SERVICESTATEMENTS:ALTER ROUTE DROP ROUTE CREATE ROUTECONTROL SERVERVIEW ANY DEFINITIONALTER ANY DATABASECONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY ROUTECREATE ROUTE CONTROL ON ROUTE::<name>VIEW DEFINITION ON ROUTE::<name>TAKE OWNERSHIP ON ROUTE::<name>ALTER ON ROUTE::<name>STATEMENTS:ALTER MESSAGE TYPEDROP MESSAGE TYPE CREATE MESSAGE TYPECONTROL SERVER VIEW ANY DEFINITIONALTER ANY DATABASECONTROL ON DATABASE::<name>VIEW DEFINITION ON DATABASE::<name>REFERENCES ON DATABASE::<name>ALTER ON DATABASE::<name>ALTER ANY MESSAGE TYPECREATE MESSAGE TYPECREATE QUEUECONTROL ON MESSAGE TYPE::<name>VIEW DEFINITION ON MESSAGE TYPE::<name>REFERENCES ON MESSAGE TYPE::<name>TAKE OWNERSHIP ON MESSAGE TYPE::<name>ALTER ON MESSAGE TYPE::<name>Permission SyntaxCREATE DATABASE **ALTER ON DATABASE::<name>STATEMENTS: CREATE DATABASE, RESTORE DATABASE** NOTE:CREATE DATABASE is a database level permissionthat can only be granted in the master database.STATEMENTS:EXECUTE ASSTATEMENTS:EXECUTE ASSTATEMENTS:ALTER AUTHORIZATIONNotes:•ALTER AUTHORIZATION for any object might also require IMPERSONATE or membership in a role or ALTER permission on a role.•ALTER AUTHORIZATION exists at many levels in the permission model but is never inherited from ALTER AUTHORIZATION at a higher level.Note: CREATE and ALTER ASSEMBLY statements sometimes require server level EXTERNAL ACCESS ASSEMBLY and UNSAFE ASSEMBLY permissions, and can require membership in the sysadmin fixed server role.NOTES:Only members of the db_owner fixed database role can add or remove members from fixed database roles.NOTES:To add a member to a fixed server role, you must be a member of that fixed server role, or be a member of the sysadmin fixed server role.© 2014 Microsoft Corporation. All rights reserved.Database Engine PermissionsMicrosoft SQL Server 2014。
SQL Server 2019 数据库系统用户手册说明书
5 Make faster, better decisions
Use data virtualization to combine and query a variety of external relational and non-relational data sources without moving or replicating data.
Mission critical security 4
Access rich, interactive reports and enterprise reporting for better analysis and decision-making.
Create, deploy, and manage mobile and paginated reports with rich visualizations in SQL Server Reporting Services. Author beautiful reports with Power BI Desktop. Publish Power BI reports to the cloud or Power BI Report Server in SQL Server 2019, and distribute and consume reports across devices. Consume interactive dashboards and reports, both online and offline, with mobile reporting on iOS, Android, and Windows devices.
SQL Server 使用指南说明书
1. What are the two authentication modes in SQL Server?There are two authentication modes –•Windows Mode•Mixed ModeModes can be changed by selecting the tools menu of SQL Server configuration properties and choose security page.2. What Is SQL Profiler?SQL Profiler is a tool which allows system administrator to monitor events in the SQL server. This is mainly used to capture and save data about each event of a file or a table for analysis.3. What is recursive stored procedure?SQL Server supports recursive stored procedure which calls by itself. Recursive stored procedure can be defined as a method of problem solving wherein the solution is arrived repetitively. It can nest up to 32 levels.CREATE PROCEDURE [dbo].[Fact](@Number Integer,@RetVal Integer OUTPUT)ASDECLARE @In IntegerDECLARE @Out IntegerIF @Number != 1BEGINSELECT @In = @Number – 1EXEC Fact @In, @Out OUTPUT - Same stored procedure has been called again(Recursively) SELECT @RetVal = @Number * @OutENDELSEBEGINSELECT @RetVal = 1ENDRETURNGO4. What are the differences between local and global temporary tables?•Local temporary tables are visible when there is a connection, and are deleted when the connection is closed.CREATE TABLE #<tablename>•Global temporary tables are visible to all users, and are deleted when the connection that created it is closed.CREATE TABLE ##<tablename>5. What is CHECK constraint?A CHECK constraint can be applied to a column in a table to limit the values that can be placed in a column. Check constraint is to enforce integrity.6. Can SQL servers linked to other servers?SQL server can be connected to any database which has OLE-DB provider to give a link. Example: Oracle has OLE-DB provider which has link to connect with the SQL server group.7. What is sub query and its properties?A sub-query is a query which can be nested inside a main query like Select, Update, Insert or Delete statements. This can be used when expression is allowed. Properties of sub query can be defined as• A sub query should not have order by clause• A sub query should be placed in the right hand side of the comparison operator of the main query• A sub query should be enclosed in parenthesis because it needs to be executed first before the main query•More than one sub query can be included8. What are the types of sub query?There are three types of sub query –•Single row sub query which returns only one row•Multiple row sub query which returns multiple rows•Multiple column sub query which returns multiple columns to the main query. With that sub query result, Main query will be executed.9. What is SQL server agent?The SQL Server agent plays a vital role in day to day tasks of SQL server administrator(DBA). Serv er agent’s purpose is to implement the tasks easily with the scheduler engine which allows our jobs to run at scheduled date and time.10. What are scheduled tasks in SQL Server?Scheduled tasks or jobs are used to automate processes that can be run on a scheduled time at a regular interval. This scheduling of tasks helps to reduce human intervention during night time and feed can be done at a particular time. User can also order the tasks in which it has to be generated.11. What is COALESCE in SQL Server?COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.Example –Select COALESCE(empno, empname, salary) from employee;12. How exceptions can be handled in SQL Server Programming?Exceptions are handled using TRY----CATCH constructs and it is handles by writing scripts inside the TRY block and error handling in the CATCH block.13. What is the purpose of FLOOR function?FLOOR function is used to round up a non-integer value to the previous least integer. Example is givenFLOOR(6.7)Returns 6.14. Can we check locks in database? If so, how can we do this lock check?Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.15. What is the use of SIGN function?SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.Example –SIGN(-35) returns -116. What is a Trigger?Triggers are used to execute a batch of SQL code when insert or update or delete commands are executed against a table. Triggers are automatically triggered or executed when the data is modified. It can be executed automatically on insert, delete and update operations.17. What are the types of Triggers?There are four types of triggers and they are:•Insert•Delete•Update•Instead of18. What is an IDENTITY column in insert statements?IDENTITY column is used in table columns to make that column as Auto incremental number or a surrogate key.19. What is Bulkcopy in SQL?Bulkcopy is a tool used to copy large amount of data from Tables. This tool is used to load large amount of data in SQL Server.20. What will be query used to get the list of triggers in a database?Query to get the list of triggers in database-Select * from sys.objects where type=’tr’21. What is the difference between UNION and UNION ALL?• UNION: To select related information from two tables UNION command is used. It is similar to JOIN command.• UNION All: The UNION ALL command is equal to the UNION command, except that UNION ALL selects all values. It will not remove duplicate rows, instead it will retrieve all rows from all tables.22. How Global temporary tables are represented and its scope?Global temporary tables are represented with ## before the table name. Scope will be the outside the session whereas local temporary tables are inside the session. Session ID can be found using @@SPID.23. What are the differences between Stored Procedure and the dynamic SQL?Stored Procedure is a set of statements which is stored in a compiled form. Dynamic SQL is a set of statements that dynamically constructed at runtime and it will not be stored in a Database and it simply execute during run time.24.What is Collation?Collation is defined to specify the sort order in a table. There are three types of sort order –1.Case sensitive2.Case Insensitive3.Binary25. How can we get count of the number of records in a table?Following are the queries can be used to get the count of records in a table -Select * from <tablename> Select count(*) from <tablename> Select rows from sysindexes where id=OBJECT_ID(tablename) and indid<226. What is the command used to get the version of SQL Server?Select SERVERPROPERTY(‘productversion’)is used to get the version of SQL Server.27. What is UPDATE_STATISTICS command?UPDATE_STATISTICS command is used to update the indexes on the tables when there is a large amount of deletions or modifications or bulk copy occurred in indexes.28. What is the use of SET NOCOUNT ON/OFF statement?By default, NOCOUNT is set to OFF and it returns number of records got affected whenever the command is getting executed. If the user doesn’t want to display the number of records affected, it can be explicitly set to ON- (SET NOCOUNT ON).29. Which SQL server table is used to hold the stored procedure scripts?Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.30. What are Magic Tables in SQL Server?During DML operations like Insert, Delete, and Update SQL Server create magic tables to hold the values during the DML operations. These magic tables are used inside the triggers for data transaction.31. What is the difference between SUBSTR and CHARINDEX in the SQL Server?The SUBSTR function is used to return specific portion of string in a given string. But, INSTR function gives character position in a given specified string.SUBSTR(“Smiley”,3)Gives result as SmiCHARINDEX(“Smiley”,’i’,1)Gives 3 as result as I appears in 3rd position of the string32. What is the use of =,==,=== operators?= is used to assign one value or variable to another variable. == is used for comparing two strings or numbers. === is used to compare only string with the string and number with numbers.33. What is ISNULL() operator?ISNULL function is used to check whether value given is NULL or not NULL in sql server. This function also provides to replace a value with the NULL.34. What is the use of FOR Clause?FOR clause is mainly used for XML and browser options. This clause is mainly used to display the query results in XML format or in browser.35. What will be the maximum number of index per table?For SQL Server 2008 100 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.1000 Index can be used as maximum number per table. 1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.1 Clustered Index and 999 Non-clustered indexes per table can be used in SQL Server.36. What is the difference between COMMIT and ROLLBACK?Every statement between BEGIN and COMMIT becomes persistent to database when the COMMIT is executed. Every statement between BEGIN and ROOLBACK are reverted to the state when the ROLLBACK was executed.37. What is the difference between varchar and nvarchar types?Varchar and nvarchar are same but the only difference is that nvarhcar can be used to store Unicode characters for multiple languages and it also takes more space when compared with varchar.38. What is the use of @@SPID?A @@SPID returns the session ID of the current user process.39. What is the command used to Recompile the stored procedure at run time?Stored Procedure can be executed with the help of keyword called RECOMPILE.ExampleExe <SPName> WITH RECOMPILEOr we can include WITHRECOMPILE in the stored procedure itself.40. How to delete duplicate rows in SQL Server?Duplicate rows can be deleted using CTE and ROW NUMER feature of SQL Server.41. Where are SQL Server user names and passwords stored in SQL Server?User Names and Passwords are stored in sys.server_principals and sys.sql_logins. But passwords are not stored in normal text.42. What is the difference between GETDATE and SYSDATETIME?Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.43. How data can be copied from one table to another table?INSERT INTO SELECTThis command is used to insert data into a table which is already created.SELECT INTOThis command is used to create a new table and its structure and data can be copied from existing table.44. What is TABLESAMPLE?TABLESAMPLE is used to extract sample of rows randomly that are all necessary for the application. The sample rows taken are based on the percentage of rows.45. Which command is used for user defined error messages?RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.46. What do mean by XML Datatype?XML data type is used to store XML documents in the SQL Server database. Columns and variables are created and store XML instances in the database.47. What is CDC?CDC is abbreviated as Change Data Capture which is used to capture the data that has been changed recently. This feature is present in SQL Server 2008.48. What is SQL injection?SQL injection is an attack by malicious users in which malicious code can be inserted into strings that can be passed to an instance of SQL server for parsing and execution. All statements have to checked for vulnerabilities as it executes all syntactically valid queries that it receives.Even parameters can be manipulated by the skilled and experienced attackers.49. What are the methods used to protect against SQL injection attack?Following are the methods used to protect against SQL injection attack:•Use Parameters for Stored Procedures•Filtering input parameters•Use Parameter collection with Dynamic SQL•In like clause, user escape characters50. What is Filtered Index?Filtered Index is used to filter some portion of rows in a table to improve query performance, index maintenance and reduces index storage costs. When the index is created with WHERE clause, then it is called Filtered IndexGuru99 Provides FREE ONLINE TUTORIAL on Various courses likeJenkins Agile Testing RPA JUnitSoftware EngineeringSelenium CCNA AngularJS NodeJS PLSQL。
sqlserver基本用法
sqlserver基本用法【原创实用版】目录1.SQL Server 简介2.SQL Server 基本用法3.SQL Server 的优点4.SQL Server 的未来发展正文【1.SQL Server 简介】SQL Server 是由 Microsoft 开发和推广的关系数据库管理系统。
它最初于 1987 年发布，现在已经发展到第十个版本。
SQL Server 是一个功能强大的数据库管理系统，被广泛应用于各种企业和组织的数据管理需求。
【2.SQL Server 基本用法】SQL Server 的基本用法包括以下几个步骤：（1）安装和配置 SQL Server：在安装 SQL Server 之前，需要确保操作系统满足 SQL Server 的系统要求。
安装完成后，需要对 SQL Server 进行配置，包括设置数据库的大小、登录方式等。
（2）连接到 SQL Server：使用 SQL Server Management Studio 或者 SQL Server Express 等工具，连接到 SQL Server。
连接成功后，可以创建和管理数据库和表等数据库对象。
（3）查询和修改数据：使用 SQL 语言，可以对数据库中的数据进行查询和修改。
SQL 语言包括 SELECT、INSERT、UPDATE 和 DELETE 等语句，可以满足各种数据操作需求。
【3.SQL Server 的优点】SQL Server 具有以下几个优点：（1）功能强大：SQL Server 提供了丰富的功能，可以满足各种复杂的数据管理需求。
（2）稳定性高：SQL Server 具有很高的稳定性，可以保证数据的安全性和可靠性。
（3）易于管理：SQL Server 提供了各种管理工具，使得数据库的管理工作变得更加简单和方便。
【4.SQL Server 的未来发展】随着云计算和大数据等技术的发展，SQL Server 也在不断更新和升级，以满足新的数据管理需求。
sqlserver基本用法
sqlserver基本用法摘要：一、SQL Server 简介1.SQL Server 的发展历程2.SQL Server 的特点和优势二、SQL Server 的安装与配置1.安装SQL Server 的硬件和软件要求2.安装SQL Server 的步骤3.配置SQL Server 的方法三、SQL Server 的主要组件1.数据库引擎2.分析服务3.报告服务4.集成服务5.通知服务四、SQL Server 数据库的管理1.数据库的创建2.数据库的备份3.数据库的还原4.数据库的删除五、SQL Server 数据表的操作1.数据表的创建2.数据表结构的修改3.数据表数据的添加、修改和删除4.数据表的查询六、SQL Server 数据查询语言1.SELECT 语句2.INSERT 语句3.UPDATE 语句4.DELETE 语句5.其他数据操作语句七、SQL Server 高级功能1.触发器2.存储过程3.用户自定义函数4.视图八、SQL Server 的安全性和维护1.安全性管理2.性能优化3.故障排除正文：【SQL Server 简介】SQL Server 是微软公司开发的一款关系型数据库管理系统，自1989 年推出以来，已经发展到了多个版本。
它具有强大的数据处理能力、高可用性和易用性，广泛应用于各种企业和组织的信息化建设。
【SQL Server 的安装与配置】安装SQL Server 需要满足一定的硬件和软件要求，例如处理器、内存、硬盘空间等。
安装过程包括SQL Server 安装程序、SQL Server 配置管理器和SQL Server 服务。
配置SQL Server 主要包括设置身份验证模式、服务启动类型、连接设置等。
【SQL Server 的主要组件】SQL Server 主要由以下组件构成：数据库引擎（SQL Server Database Engine），负责处理数据存储和查询；分析服务（Analysis Services），用于数据分析和挖掘；报告服务（Reporting Services），支持生成各种报表；集成服务（Integration Services），负责数据抽取、转换和加载；通知服务（Notification Services），用于实现数据实时通知。
SQL Server 常用操作手册
一、建库操作
1.打开企业管理器；
2.选中数据库节点，进行新建；
3.弹出数据库属性，给数据库命名，使用英文；
4．右键刷新数据库，新建数据库就完成了二、建表操作
方法一、在数据库右键----新建----表
方法二、在数据库---选择表---右键---新建表
三、设主键操作
1.右键单击表—设计表
2.选中列---点击设置主键
四、设外键操作
1.右键单击表—设计表
2.在表空白处右键，单击索引/键
3.弹出属性对对话框---关系选项
4.分别设置主键表、外键表以及主键，在外键表下拉列表—选择对应的主键，即是外键
五、备份操作
1.右键单击数据库—所有任务----备份数据库
2.弹出SQL Server备份，选择存储地址，点击确定
六、还原操作
1.新建一个数据库，右键单击数据库---所有任务---还原数据库，如图：
2.弹出还原数据库对话框，选择“从设备”选项，选好地址；
七、导入/导出操作
1.右键点击数据库，选择所有任务---导入/导出数据
2.弹出导入/导出操作对话框，进行下一步。
八、去掉系统对象
Local---右键编辑。
动软 sqlserver 详细用法
动软 SQLServer 是一款功能强大的数据库管理工具，它支持SQLServer 2000、2005、2008、2012、2014、2016 和 2019 版本，具有方便易用、功能丰富的特点。
本文将介绍动软 SQLServer 的详细用法，包括安装、连接数据库、管理数据等内容。
一、安装动软 SQLServer1. 打开动软 SQLServer 的冠方全球信息站，在下载页面选择适合版本进行下载。
2. 下载完成后，双击安装包进行安装，按照安装向导的步骤进行操作即可完成安装。
二、连接数据库1. 打开动软 SQLServer，在主界面选择“新建连接”。
2. 在弹出的连接属性对话框中填写数据库服务器的信息，包括服务器名称、用户名、密码等，点击“测试连接”按钮测试连接是否成功。
3. 连接成功后，点击“确定”按钮，即可在主界面看到已连接的数据库服务器。
三、管理数据库1. 在主界面左侧的导航栏中选择“数据库”，即可看到已连接的数据库服务器下的所有数据库列表。
2. 右键点击某一个数据库，可以进行数据库的备份、还原、导入导出等操作。
3. 双击某一个数据库，即可进入数据库管理页面，可以查看表、存储过程、视图等数据库对象。
四、查询数据1. 在数据库管理页面中选择“新建查询”，即可编写 SQL 查询语句。
2. 编写完成后，点击“执行”按钮，即可查看查询结果。
五、管理表结构1. 在数据库管理页面中选择某一个数据库，即可查看该数据库中的所有表列表。
2. 双击某一个表，即可查看该表的结构，包括字段、索引、约束等信息。
3. 右键点击某一个表，可以进行表的修改、删除等操作。
六、其他功能除了上述主要功能外，动软 SQLServer 还具有数据导入导出、数据比对、数据同步、数据清理等功能，可以极大地提高数据库管理的效率和便利性。
总结起来，动软SQLServer 是一款功能强大、易用的数据库管理工具，通过本文介绍的详细用法，相信读者已经对其有了更加深入的了解。
sql server 使用方法(一)
SQL Server 使用方法SQL Server是一种关系数据库管理系统，为企业提供了强大的数据管理和分析能力。
在本文中，我们将详细介绍SQL Server的使用方法，包括安装、配置、管理和优化等方面。
安装SQL Server1. 下载SQL Server安装程序首先，您需要从官方网站下载SQL Server的安装程序，选择适合您环境的版本和版本号。
2. 运行安装程序运行下载的安装程序，并按照提示逐步完成安装过程。
在安装过程中，您需要选择安装类型、实例名称、安装路径等信息。
3. 配置SQL Server安装完成后，您需要配置SQL Server，包括设置服务器名称、身份验证模式、端口等信息。
管理SQL Server1. 连接到SQL Server使用SQL Server Management Studio（SSMS）工具，您可以连接到SQL Server实例，进行数据库管理和查询操作。
2. 创建数据库通过SSMS工具，您可以创建新的数据库，设置数据库的名称、文件路径、日志文件等参数。
3. 管理数据库对象在SSMS中，您可以管理数据库对象，包括表、视图、存储过程、触发器等。
优化SQL Server1. 索引优化通过创建合适的索引，可以提高数据库的查询性能。
您可以使用SSMS工具分析查询执行计划，确定需要创建的索引类型和字段。
2. 查询优化通过优化查询语句，可以减少数据库的负载，提高查询性能。
您可以使用SSMS工具分析慢查询，优化查询语句的结构和索引使用。
3. 系统配置优化通过调整SQL Server的系统配置参数，可以提高数据库的整体性能。
您可以通过SSMS工具修改服务器级别的配置参数，如内存分配、并发连接数等。
总结在本文中，我们详细介绍了SQL Server的使用方法，包括安装、配置、管理和优化等方面。
通过掌握这些方法，您可以更好地使用SQL Server，提高数据库的性能和可靠性，满足企业需求。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
Microsoft SQL SERVER 2000使用手册在网络环境下进行数据库系统的应用开发，需要选择具体的DBMS。
本章首先介绍当前流行的关系数据库系统Microsoft SQL Server的基本知识，接着以Microsoft SQL Server 2000为背景，介绍数据库系统设计的方法和SQL Server的高级应用技术。
1 SQL Server简介Microsoft SQL Server是高性能、客户/服务器的关系型数据库管理系统RDBMS，能够支持大吞吐量的事务处理，也能在Microsoft Windows 2000 Server网络环境下管理数据的存取以及开发决策支持应用程序。
由于Micriosoft SQL Server是开放式的系统，其他系统可以与它进行完好的交互操作。
1.1 SQL Server 2000的特点（1）对操作系统的要求低SQL Server 2000除了继续支持Windows 2000 Server外，还新增了SQL Server 2000桌面版（Desktop Edition），此版本可以安装在Windows 95/98/2000专业版上，用来建立数据库，这使得SQL Server 2000变得非常通用，可以在任何大小的计算机上运行，包括膝上机、台式机和高档服务器。
（2）管理自动化许多关键性的工作、数据库调整和最佳化处理等都由系统自动完成，减少了人为手工调整。
例如，系统会动态调整内存需求，自动缩减库和增加数据库所占文件的空间大小，自动更新统计数据等等。
（3）新的Enterprise Manager它以MMC（Microsoft Management Console）为基础加以设计，其界面和其他微软服务器产品一致，看上去很像Windows的资源管理器。
（4）简化了SQL Server 2000数据库与Windows文件系统之间的关系，具有更好的可伸缩性在SQL Server 2000中，数据库驻留在操作系统文件上，而不再位于SQL Server的逻辑设备中，取消了以前必须先建立设备（Device），再在设备上建立数据库的观念，现在仅使用CREATE DATABASE 语句即可创建数据库及其所有文件，从而简化了数据库设计和管理的工作。
SQL Server 2000允许数据库自动扩展，不再需要管理员发出额外的ALTER语句命令。
（6）提供数据库挖掘功能在SQL Server 2000中加入的OLAP（On-Line Analytical Processing）服务，可以帮助公司建立决策分析的应用。
1.2 SQL Server 2000环境介绍1）系统目录用途介绍若使用默认值，安装程序会把大部分SQL Server 2000系统文件存放在启动盘的MSSQL目录下，并且在此目录下将创建多个子目录，用于存放不同的目的文件，列举如下：\BACKUP 存放备份文件\Binn 存放客户端和服务器端可执行文件与DLL文件\Data 存放数据库文件，包括系统数据库、实例数据库和用户数据库\FTDATA 存放全文索引目录文件\Install 存放有关安装方面的信息文件\JOBS 存放工作文件\LOG 存放日志文件\REPLDATA 存放复制数据\Upgrade 存放升级工具及SQL Server 7升级到2000时产生的文件2）自动创建的数据库在安装SQL Server 2000时，安装程序会自动创建4个系统数据库（master、model、msdb、tempdb）和两个范例数据库（Northwind、pubs），系统数据库由系统自动维护。
（1）master数据库其中记录了所有SQL Server 2000的系统信息、登录账号、系统配置设置、系统中所有数据库及其系统信息以及存贮介质信息等。
master数据库的数据文件为master.mdf，日志文件为mastlog.ldf。
（MDF（Mirror Disc File）文件是光盘镜像文件的一种，我们可能经常接触诸如系统文件的ghost镜像文件或者iso镜像文件等。
同样的MDF文件也是一种镜像文件，通常我们用BT或其它P2P软件下载游戏或较大文档的，特别是在网上的流传游戏文件常以mdf文件，其实mdf文件要和mds文件搭配才能使用。
）（2）model数据库此系统数据库是SQL Server 2000为用户创建数据库提供的模板数据库，每个新建的数据库都是在一个model数据库的副本上扩展而生成的，所以对model数据库的修改一定要小心。
model数据库的数据文件为model.mdf，日志文件为modellog.ldf。
（3）msbd数据库msdb数据库主要用于SQL Server 2000存储任务计划信息、事件处理信息、备份恢复信息以及异常报告等。
msdb数据库的数据文件为msdbdata.mdf，日志文件为msdblog.ldf。
（4）tempdb数据库tempdb数据库存放所有临时表和临时的存储程序，并且供SQL Server 2000存放目前使用中的表，它是一个全局的资源，临时表和存储程序可供所有用户使用。
每次SQL Server 2000启动时它会自动重建并且重设为默认大小，使用中它会依需求自动增长。
（5）Northwind数据库Northwind是SQL Serve 2000提供的范例数据库，它包含一个称为Northwind Traders公司的销售数据库。
（6）pubs数据库。
pubs数据库也是SQL Server 2000提供的范例数据库，它包含一个书籍出版公司的数据库范例。
3）SQL Serve 2000提供的主要服务（1）MS SQL Server：运行SQL Server的服务。
它管理着存储数据库的所有文件，处理所有客户应用传送来的T-SQL命令，执行其他服务器上的存储过程，并支持从多个不同数据源获取数据的分布式查询。
（2）SQL Server Agent：管理SQL Server周期性行为的安排，并在发生错误时通知系统管理员。
它包括作业(Jobs)、警告（Alerts）和操作员（Operator）三部分。
（3）Microsoft Server Service：一个全文本索引和搜索引擎。
它可以在所选表列上创建有关词汇的特殊索引，T-SQL语言可以利用这些索引来支持语言搜索和近似搜索。
（4）MS DTC（Microsoft Distributed Transaction Coordinator）：管理包含不同数据源的事务。
它可以正确提交分布式事务，以确保所有服务器上的修改都被保存或回退。
其中，SQL Serve、SQL Server Agent和MS DTC可作为Windows NT/2000 服务来运行，Microsoft Server Service服务只能运行在Window NT/2000 Server上。
1.3 SQL Server 2000的工具介绍1）服务管理器（Service Manager）SQL Server的服务管理器用来启动、停止和暂停SQL Server过程。
必须在进行任何数据库操作前启动SQL Server。
服务管理器可以从SQL Server的程序组中进行启动。
如图1所示。
图1 SQL Server的启动启动后的SQL Server 服务管理器对话框如图2所示。
对服务（MS SQL Server、SQL Server Agent和MS DTC）的选择可用服务（Services）列表。
双击绿、黄、红交通灯，就可以达到启动开始/继续（Start/Continue）、暂停(Pause)和停止(Stop)的目的。
图2 SQL Server 服务管理器2）企业管理器（Enterprise Manager）企业管理器（Enterprise Manager）是用户管理SQL Server 2000的主要工具和图形界面，用户可以在这个全图形界面的环境下建立数据库、表、数据、视图、存储过程、规则、默认值和用户自定义的数据类型等功能。
Enterprise Manager主界面如图3所示。
图3 SQL Server 的企业管理器3）查询分析器（Query Analyzer）查询分析器（Query Analyzer）提供的图形界面可用来分析一个或多个查询执行计划，查看数据结果并且依查询的方式提出最佳化的索引建立方式，以便改进查询效率。
例如，查询pubs库中表jobs的内容，步骤如下：在Windows开始菜单中执行“程序|Microsoft SQL Server|查询分析器”命令，进入查询分析器对话框，如图4所示，在查询分析器窗口的数据库复选框中选择pubs库；在命令窗口中输入SQL 语句：SELECT * FROM JOBS后，点击Execute Query按钮，查询结果便显示在输出窗口中。
图4 SQL Server 查询分析器4）其他工具（1）事件探查器可以即时监督、捕捉、分析 SQL Server 2000的活动，对查询、存储过程、锁定、事务和日志的变化进行监视，以及在另一个服务器上重现所捕获的数据。
如图5所示。
（2）客户端网络实用工具（Client Network Utility ）可以用来配置客户端到服务器的连接。
（3）服务器网络实用工具（Server Network Utility ）是 SQL Server 2000服务器端的网络配置界面。
（4）导入和导出数据（Import and Export Data ）提供了导入、导出以及在 SQL Server 2000和 OLE DB 、ODBC 及文件间转换数据的功能。
（5）联机丛书（Book Online ）提供了联机文档，包括有关操作维护的说明。
（6）OLAP Services 提供了在线分析处理功能（需要另外安装 OLAP 管理工具）。
图5 创建跟踪的多选项卡对话框2 数据库的创建、修改和删除2.1 创建SQL Server数据库通常可以采用以下3种方法来创建数据库。
1）用企业管理器来创建数据库用企业管理器新建数据库的主要步骤如下：（1）在Windows开始菜单中执行“程序|Microsoft SQL Server|企业管理器”命令，进入SQL Server Enterprise Manager界面。
在SQL Server Enterprise Manager展开SQL Server组，再展开数据库项，右击数据库，在弹出的快捷菜单中选择“新建数据库…”命令，如图6所示。
图6 创建新的数据库（2）此时，弹出“数据库属性”对话框，在“常规”选择卡中，输入数据库的名称“studb”，对所建的数据库进行设置，如图7所示。