T-SQL Example code: User Management

Some useful commands for dealing with users (in a non violent way), and a very useful couple of lines for finding and then fixing orphaned users which often happens after moving a database to a differnet SQL server.

Change user password

USE master 

GO

sp_password @old=NULL, @new='password', @loginame='<login_name>';

GO

Detect orphaned users

run the following T-SQL script

USE <database_name>

GO 

sp_change_users_login @Action='Report'

GO

Resolve an orphaned user

The following command relinks the server login account specified by <login_name> with the database user specified by <database_user>.

USE <database_name>

GO

sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>', 

   @LoginName='<login_name>';

GO

List all users of a database

SELECT name from DatabaseName.dbo.sysusers where uid <16000

Posted on July 12, 2012 at 23:38 by simon · Permalink
In: SQL · Tagged with: orphan, orphan user, sp_password

Leave a Reply