Adding Logins with Stored Procedures
From the Query Analyzer, you can use various stored procedures to add logins. If you're adding an existing NT user or group to the SQL Server logins, you use the syntax
sp_grantlogin 'login_name'
which gives permission to that NT login to access the SQL Server. Alternatively, you can use
sp_defaultdb 'login_name', 'default_database'
which not only assigns a default database or changes it if one is already assigned, but also adds the login if the login hasn't yet been granted permission to access the SQL Server.
If you're setting up a SQL Server login for mixed-mode security, you need to use
sp_addlogin 'login_name', 'password', 'default_database'
to set up the name for the new account and the default database.
Server Roles
SQL Server 7.0 introduced the concept of roles, which are similar to NT groups, for the server and database. A server role is similar to the special operator groups in NT, such as Account Operator or Backup Operator. Roles come with predefined privileges and rights, and you can use them to delegate some administrative tasks. You can place a new login in the server roles while you're adding the login, or go back later and edit the login. Select the Server Roles tab of the Login Properties dialog box to see the various server roles, as Screen 2 shows. The sidebar "Server Roles" lists these roles with the internal names in parentheses.
You can't modify the server roles or add new server roles. A login can belong to more than one role and acquires a combination of the privileges of the roles it belongs to. One major difference between server roles and NT groups is that any member of any server role can add any other login to that role. You can see the Server Roles menu item under Security in the Enterprise Manager. Right-click a role, and select Properties. From the Properties dialog box, you can add or remove users. The Enterprise Manager interface lets you look at roles and see who's in that role, or you can look at logins and see what role each login belongs to.
To use stored procedures to add a login to a server role, use the command
sp_addsrvrolemember 'login_name', 'role'
where role is the internal name—for example, sysadmins.
Removing Logins
You'll eventually need to remove logins from the SQL Server. SQL Server prevents you from removing a login if the login is associated with the user name of a user who owns objects in any database. This rule prevents objects from being orphaned, which would be a violation of referential integrity in the database. If you follow good programming practices, only the database owner (dbo) can own objects, so deleting a user and login account won't cause any conflicts. Also, you can't remove a login that's in use.
To prevent users from accessing the server, you can use NT permissions to deny them access, or even delete their NT login. From Enterprise Manager, you can delete a login by selecting it and pressing the delete key. Or you can right-click the login name and select Delete. The SQL syntax to remove a login is
sp_droplogin 'login_name'
Cleaning Up Logins
If the NT administrator removes an NT user login, that login isn't automatically removed from the list of valid SQL Server logins. If you're using integrated security (i.e., only NT logins), this inactive account is harmless because nobody can use it. But you probably want to clean up these loose ends and remove them from the SQL Server. The stored procedure sp_validatelogins shows you these inactive logins so you can delete them.
Next month, I'll look at giving database access to users who connect to the SQL Server. Meanwhile, try the practice questions in the sidebar "Practice Questions: Login Security," page 66. (Answers to last month's practice questions are in the sidebar "Answers to December Practice Questions: Constraints and Integrity.") For more examples on how to manage logins to SQL Server, see the lab files that accompany this article. Subscribers can download these files at the link to this article at http://www.sqlmag.com.
End of Article
Prev. page
1
[2]
next page -->
