Authenticating Logins - Sample Commands
(Page 20 of 24 )
Let’s take a look at some sample commands. The first example shows how to add the Users local group to the sysxlogins table:
Sp_grantlogin 'builtin\Users'
Here, I grant login permissions to the local account MaggieM:
sp_grantlogin 'SS7_NT_SRV\MaggieM'
Next, I grant login permissions to the domain account NTTEST\FrankB:
sp_grantlogin 'NTTEST\FrankB'
Finally, I grant login permissions to the domain global group Domain Admins:
sp_grantlogin 'NTTEST\Domain Admins'
The only drawback to granting login permission to a Windows NT group as a whole is that there may be a time when a user needs to be a member of the group but does not need to have access to the server. It is for just this situation that SQL Server allows you to deny access to individual accounts or groups using sp_revokelogin:
sp_revokelogin [@loginame =] 'login'
The problem with this approach is that it does not revoke the login privileges for any other groups of which the user is a member. To keep the user out completely, you must use the following command:
sp_denylogin [@loginame =] 'login'
Table 2-4 shows the bit mappings for the xstatus column.
| Purpose | Bit | Description |
|---|
| denylogin | 1 | Indicates whether the login account is permitted access to the server. |
| hasaccess | 2 | Indicates whether the login account is permitted access to the server. |
| isntname | 3 | Is the name a Windows NT account name? |
| isntgroup | 3 | Is the name a Windows NT group name? |
| isntuser | 4 | Is the account a Windows NT user account? |
| | If bit 4 is 0 and bit 3 is 1, the account is a Windows NT group. If bit 4 is 1 and bit 3 is 1, the account is a Windows NT user. |
| sysadmin | 5 | The rest of the bits indicate membership in system roles:
0 = not a member
1 = is a member |
| securityadmin | 6 | |
| serveradmin | 7 | |
| setupadmin | 8 | |
| processadmin | 9 | |
| diskadmin | 10 | |
| dbcreator | 11 | |
| bulkadmin | 12 | |
Table 2-4. Xstatus Column
The sp_denylogin stored procedure will add an entry in sysxlogins that specifically prohibits logins by the specified account. Denied login accounts have bit 1 set in the xstatus column of sysxlogins. The decision whether to use sp_revokelogin or sp_denylogin depends on whether you want to remove an entry from sysxlogins or specifically prohibit an existing user from logging in. Removing the entry for a user account or a group from the table denies access to the server, because SQL Server will not be able to find the account’s SID. If a user has access through any other SID, however, she can still log in. Denying access to a SID is like the “No Access” permission on NTFS files in Windows NT, in that it overrides all other permissions granted to the other groups in the access token. This restriction applies even to system administrators, because you cannot be a system administrator until you log in successfully.
To reverse the effects of either sp_revokelogin or sp_denylogin, you can simply call sp_grantlogin again. If you have denied access to a user’s individual account SID, but you still want to allow that user to log in through membership in a group, call sp_grantlogin with the user’s account, and then call sp_revokelogin with the same account. The user will not be able to log in using his individual account, thanks to sp_revokelogin, but he will be able to log in using the group. That might sound a bit confusing at first, so let’s look at another example.
Execution of the following command grants login access to all members of the Domain Users global group. When it finishes, you can query either syslogins or sysxlogins and find a new row with the Domain Users name and its SID:
Exec sp_grantlogin 'NTTEST\Domain Users'
Because FrankB’s domain account is a member of Domain Users by default, he is automatically permitted to log into SQL Server. Let’s suppose, however, that FrankB is untrustworthy and should not have access to the server. To take away his right to log in—which was granted to the Domain Users group—you execute the following command:
Exec sp_denylogin 'NTTEST\FrankB'
This is preferable to revoking login permissions from the Domain Users group, because now you are telling SQL Server that FrankB is explicitly denied access. To understand why that is significantly different from just revoking his permissions, you can create another domain global group named NTTEST\SQL Server Users, make FrankB a member, and grant login access to the group:
Exec sp_grantlogin 'NTTEST\SQL Server Users'
Everyone in this group, except FrankB, will now be granted access. If you had simply used sp_revokelogin, and the deny login entry were not in sysxlogins, FrankB would now be able to log in because he is a member of both the Domain Users and the SQL Server Users groups. Membership in either one would be sufficient to grant him the right to log in. However, the explicit deny login permission overrides those permissions. Without it, you would have to revoke login permissions from both groups to prevent FrankB from logging in.
What this example shows is the potential pitfall of granting permissions to Windows groups. Because SQL Server permits users to log in if the permission has been granted to any one of the groups listed in the access token, it becomes necessary either to plan both group membership and permission assignment carefully or to deny permissions to individual members. The choice between the two approaches depends mostly on whether you want to manage permissions positively, by saying who can access the server, or negatively, by saying who cannot access the server.
 This is from SQL Server Security Distilled, second edition, by Morris Lewis (Apress, ISBN 1590592190). Check it out at your favorite bookstore today. Buy this book now. |
Next: Server Roles >>
More MS SQL Server Articles
More By Apress Publishing
/ 17 
