Managing SQL Server Users

Like logins, user accounts can be managed through Enterprise Manager or via T-SQL stored procedures. The method you choose will depend on personal preference and the number of user accounts with which you are working. If you are working with multiple users, it is often more efficient to use T-SQL.

Using Enterprise Manager

In Enterprise Manager, users are managed through the Users folder in each database. Right-clicking Users and selecting New Database User brings up the New User dialog box, as shown in Figure 15.7.

Figure 15.7. The New User dialog box.

From here, you can select which login to associate with the user, the name of the user, and any database roles of which they should be a member. Note that the Permissions button is grayed out; permissions to access database objects can't be added to the user until the user account is created. You must click OK to create the user and then reaccess the user's Properties page to add permissions. If you do need to add permissions or make changes to role membership, double-clicking the user in the Details pane will open the Properties dialog box.

Using T-SQL

To add a user to a database with T-SQL, execute the sp_grantdbaccess stored procedure specifying the login name and the username:

 EXEC sp_grantdbaccess 'domain1\billw', 'billw' 

The command for removing the user is as follows :

 EXEC sp_revokedbaccess 'billw' 

If you want to change the relationship between a login and user, use sp_change_users_login:

 EXEC sp_change_users_login 'update_one', 'billw', 'domain1\bwhite', 

The ' update_one' argument links the specified user to a new login. This stored procedure also accepts the arguments ' report ' to list the users and their respective logins, and ' auto_fix ', which attempts to link the users with a login of the same name. This can be useful when transferring a database to a different server. The utility does a best match, so check the results to ensure you achieved the desired results.