SQL Server Security
Note that this tutorial uses the 2000 edition of SQL Server (SQL Server 2000).
For the latest version, see SQL Server tutorial.
SQL Server has a number of security features that assist database administrators in maintaining their database in a secure way. You can see the areas of security by expanding the "Security" folder within Enterprise Manager.
Logins
SQL Server allows you to configure users and roles for anyone who needs to access SQL Server or any of its databases and their objects. Typically, you will have a database administrator who has access to everything. Then you will have users with varying levels of access, depending on the tasks they're allowed to perform.
Doing this can prevent inexperienced users from wreaking havoc on your database environment. Imagine if one morning you came to work, only to find that someone had accidentally deleted your main database!
In Enterprise Manager, you can use SQL Server Security section to restrict the tasks that each user can perform. If you have many users, you can assign them a role. Roles enable you to assign the same access rights across many users. Instead of assigning permissions against an individual user (or 'login'), you assign them against a role. You can then add users/logins into that role.
More about logins in later.
Server Roles
Server roles are available for various database administration tasks. Not everyone should be assigned to a server role. In fact, only advanced users such as database administrators should be assigned a server role.
Here are the server roles defined in SQL Server during setup:
| Server Role | Description |
|---|---|
| sysadmin | Can perform any task in SQL Server. |
| serveradmin | Can set server-wide configuration options, can shut down the server. |
| setupadmin | Can manage linked servers and startup procedures. |
| securityadmin | Can manage logins and database permissions, read logs, change passwords. |
| processadmin | Can manage processes running in SQL Server. |
| dbcreator | Can create, alter, and drop databases. |
| diskadmin | Can manage disk files. |
| bulkadmin | Can execute BULK INSERT statements. |
As you can see, some of these roles allow very specific tasks to be performed. If you don't have many technical users, it's likely that you'll only use one or two of these roles (including sysadmin).
Linked Servers
The Linked Servers option allows you to connect to another instance of SQL Server running on a different machine, perhaps remotely in a different city/country. This can be useful if you need to perform distributed queries (query a remote database). Setting up a linked server is quite straight forward in Enterprise Manager, all you need is details of the remote server, and the database that you need to query.
In the above example, "Data Source" is the name of the SQL Server machine, "Catalogue" is the name of the database. You can also configure options in the other two tabs depending on your requirements.
Remote Servers
The Remote Servers option allows you to execute a stored procedure on another instance of SQL Server without establishing another connection. The Remote Servers option is only provided for backwards compatibility. If you need to execute stored procedures against a remote server, use a linked server.