administrators are often faced with the need to learn where
features of his/her system live or reside on a less familiar
system. Steve Callan approaches this need by mapping SQL Server
features back into Oracle Database.
This category includes three child
items: Logins, Server Roles, and Credentials.
Logins in SQL Server can be one of
two types with respect to authentication: managed by Windows
(typically within Active Directory, but also local to the server)
and managed by SQL Server (mixed mode). The examples shown in the
screenshot above show both types. As a general rule, login names
with one word or without slashes are indicative of being MSSQL
accounts. The “sa” account (analogous to SYS and SYSTEM) is an
example of this type of account.
Administrators get a free account;
one of the several built-in groups in Windows is the Administrators
group. Other built-in and local, but Windows authenticated, accounts
include the NT AUTHORITY accounts. The NT AUTHORITY\SYSTEM account
is granted a login along with the built-in Administrator account,
and a common practice is to remove both of these accounts and rely
upon AD authentication. If you look at the properties (right-click
the account, then go to the Server Roles page), you can see how the
SYSTEM account has been granted the sysadmin fixed server role. In
Oracle terms, this account has SYS/SYSTEM level privileges).
A common question in Oracle, with
respect to licensing, has to do with how many users have access to
the database. Letting Oracle manage user security makes the
DBA_USERS data dictionary view a fairly valid source of what the
count is. With practically all SQL Server logins (from human users)
being managed via Active Directory, the Logins folder is not going
to be quite as useful, and if a user doesn’t appear under Logins, he
won’t appear in a table either. This problem is compounded when
DOMAIN\Authenticated Users is created as a login. Any and all domain
authenticated users can logon with this login account.
When you logon in Oracle, you have
whatever roles, grants, and privileges your account/schema has been
authorized. It is quite possible you cannot see objects in another
schema. The SQL Server counterpart to this uses a building and
office analogy. Your basic logon gets you into the building. To get
into a specific office, you need another key. So, within a database,
security is (further) managed by granting whatever roles to a user.
Speaking of roles, SQL Server comes
with eight pre-defined server roles (shown in the Server Roles
folder). These roles are actually fixed server roles, and their
server-level purpose, function or permission is shown below.
Administer bulk operations (run bulk
Alter any database (create, and
alter/restore their own)
Alter resources (manage disk files)
Alter any connection, alter server state
Alter any login (grant, deny, revoke
server/database permissions, reset passwords)
Alter any endpoint, alter resources, alter
server state, alter settings, shutdown, view server state
(change server-wide configuration options, shutdown)
Alter any linked server (add/remove linked
Control server (do anything)
The fixed roles are server level,
and a user who has one of these roles can grant that role to other
logins (Oracle comparison - a role granted with admin included).
Several “sp_whatever” queries are available for server roles (as
with many other features, such as sp_helpdb), but they may not be
very informative. The results from sp_helpsrvrole are shown below.
As to which logins have which server
roles assigned, the sp_help results (sp_helpsrvrolemember) are much
more informative. You can see the value of running this periodically
as an audit policy (using the principle of least permissive access).
To illustrate, I created a new sample user and assigned a fixed
The last folder item under Security
is Credentials, and the name is suggestive of what Oracle can use.
Database Control, as an example, allows you to enter credential
information. When the database needs to go out to the operating
system, a login with the appropriate credential may do so. The
credential needs a Windows username and password, and the SQL Server
login is granted the credential. You’ll see this more often than not
in .NET applications (where the database can make calls out to
procedures or assemblies).
This category contains some very
useful functionality or features, some of which will be more near
and dear to the Oracle DBA.
It goes without saying that backups
are critical to all database systems. A Backup Device simplifies
some scripting (or entry in a GUI) with respect to where a backup
will be created. Instead of scripting out a path, you can create a
location and basically alias it within MSSQL. The Oracle analogy
(for the most part) is a combination of the archived redo log
location(s) and the Flash Recovery Area. As you may recall, the
files contained in the FRA include the control file (and an auto
backed up version), RMAN files, flashback logs, data file copies,
and archived redo logs. Oracle has a lot more going on in the FRA
than SQL Server has in a Backup Device aliased location, but you can
at least see the similarity.
Endpoints are gateways into MSSQL
and are used without an active (human) user session (e.g., one
database talking to another database via a process). What are some
ways connections into a database can be made? Think of Internet
connections in particular, but also connections over a network in
general. Database Mirroring is a prime example of this. Mirroring is
much like using a standby database in Oracle. The connection between
the partners (which database is the primary, and which one is the
mirror) is facilitated via endpoints on each server. Multiple
databases can be mirrored, and they will all use the same endpoint.
Included in the setup of an endpoint are port information and the
authentication method. An advantage SQL Server has here is your
ability to use the same AD account at both ends (or even a third
“end” if using a witness server) for the authentication. If using a
local account, you’ll run into server certificate issues.
SQL Server provides several catalog
views of endpoint-related information. These will include
information about other types of endpoints (Service Broker, SOAP,
A linked server in SQL Server is
completely analogous to a database link in Oracle. The list of
providers is fairly extensive.
Two of the providers are directly
related to Oracle: MSDAORA and OraOLEDB.Oracle. From Oracle’s
perspective, connecting to SQL Server may involve ODAC or MDAC.
Information about Oracle Data Access Components can be found at OTN
in the Windows technology area. If you are using much older versions
of either product, Microsoft Data Access Components info can also be
Setting up a linked server can be
frustrating, and the truth of the matter is that there is more than
one way to create a linked server to Oracle. The two create
statements (sp_addlinkedserver stored procedure) accomplish the same
EXEC master.dbo.sp_addlinkedserver @server = N'TO_ORCL',
EXEC master.dbo.sp_addlinkedserver @server = N'ALSO_ORCL',
Using a simple queries such as...
Select * from TO_ORCL..SCOTT.EMP
Select * from ALSO_ORCL..SCOTT.EMP
...can return different results (one
will work; the other may not, with a complaint about a numeric error
conversion). Check your client versions of software (one is to be
above 10.2.0.1, 10.2.0.2 and higher seems to work well).
Finally, the last folder item
covered in this part of the series is Triggers. How are these
triggers different from the triggers found under Programmability in
a Database? Well, the first is that the names are different. Within
a database, the formal label is Database Triggers. In this area, the
name is simply Triggers. SQL Server supports server-level triggers,
which are more along the lines of DDL triggers as opposed to DML
triggers. DDL triggers can be created at the database level and also
at the server level. If you wanted to monitor who created a
database, then a server-level DDL trigger would apply, and the
trigger would be found under Server Objects > Triggers. In Oracle,
all triggers are found under the respective owner. In investigating
what happens with data and objects within SQL Server, you have (at
least) two places to inspect: database-level and server-level
In the next part of this series,
we’ll continue on with more of the mappings from SQL Server to