Security (Level 2)

Installing SQL Server

Installing SQL Server
Security (Level 2)
SQL Server 6.x SQL Server 2000
The GRANT and REVOKE statements granted and revoked permissions, respectively. The REVOKE statement denied a permission to a single user that was granted to the user's group. The security model uses DENY in addition to GRANT and REVOKE. REVOKE has changed to remove a previously granted or denied permission. DENY creates an entry in the security system that denies a permission from a security account and prevents the user, group, or role from inheriting the permission through its group and role memberships. The REVOKE statement can no longer be used to deny permission to a user whose group has permission. Use the DENY statement to deny permissions explicitly to a specific user or group.

Expect different results as compared to earlier versions of SQL Server. Recognize that scripts using the SQL Server 6.x security model using GRANT and REVOKE behave differently than scripts using the current model of GRANT, REVOKE, and DENY if REVOKE was used to deny permissions to selected members of a group.

When executing an RPC, logins using integrated security mode referred to an internal login name with the backslashes (\) translated to underscores (_). For example, \Domain\Joe was translated to Domain_Joe. Those servers upgraded to SQL Server that execute RPC calls no longer translate backslashes to underscores when using Windows NT Authentication. To use the SQL Server version 6.x naming convention for login names, use sp_addlinkedsrvlogin to map the backslash version of the username to an underscore version.

Expect different results as compared to earlier versions of SQL Server. Add references for sp_addlinkedsrvlogin to translate backslash version login names to underscore versions to maintain version 6.x login translations when the sending server of an RPC uses SQL Server 2000.

Examples
A.    Map specific backslash login to underscore login

This example maps the \LONDON1\nancyd login name to LONDON1_nancyd:

sp_addlinkedsrvlogin 'receiving_server_name', 
   false, 
   'LONDON1\nancyd', 
   'LONDON1_nancyd', NULL
B.    Map specific backslash login to sa login

This example maps Nancy's LONDON1 login to the sa login, because Nancy's domain login is part of the built-in administrators group:

sp_addlinkedsrvlogin 'receiving_server_name', 
   false, 
   'LONDON1\nancyd', 
   'sa', NULL