Web Design & Development

Syntax

sp_change_users_login [@Action =] ‘action’ [,[@UserNamePattern =] ‘user’]
[,[@LoginName =] ‘login’]

Arguments

[@Action =] ‘action’
Describes the action to be performed by the procedure. action is varchar(10), and can be one of these values.

Auto_Fix

Links user entries in the sysusers table in the current database to logins of the same name in syslogins. It is recommended that the result from the Auto_Fix statement be checked to confirm that the links made are the intended outcome. Avoid using Auto_Fix in security-sensitive situations. Auto_Fix makes best estimates on links, possibly allowing a user more access permissions than intended.

user must be a valid user in the current database, and login must be NULL, a zero-length string (‘‘), or not specified.

Report

Lists the users, and their corresponding security identifiers (SID), that are in the current database, not linked to any login.

user and login must be NULL, a zero-length string (‘‘), or not specified.

Update_One

Links the specified user in the current database to login. login must already exist. user and login must be specified.

[@UserNamePattern =] ‘user’
Is the name of a SQL Server user in the current database. user is sysname, with a default of NULL. sp_change_users_login can only be used with the security accounts of SQL Server logins and users; it cannot be used with Microsoft Windows NT® users.
[@LoginName =] ‘login’
Is the name of a SQL Server login. login is sysname, with a default of NULL.

Return Code Values

0 (success) or 1 (failure)

Permissions

Any member of the public role can execute sp_change_users_login with the Report option. Only members of the sysadmin fixed server role can specify the Auto_Fix option. Only members of the sysadmin or db_owner roles can specify the Update_One option.

Examples

A. Show a report of the current user to login mappings

This example produces a report of the users in the current database and their security identifiers.

EXEC sp_change_users_login ‘Report’

B. Change the login for a user

This example changes the link between user Mary in the pubs database and the existing login, to the new login NewMary (added with sp_addlogin).

–Add the new login.

USE master

go

EXEC sp_addlogin ‘NewMary’

go

–Change the user account to link with the ‘NewMary’ login.

USE pubs

go

EXEC sp_change_users_login ‘Update_One’, ‘Mary’, ‘NewMary’

Related Article: http://msdn2.microsoft.com/en-us/library/ms174378.aspx

SELECT t1.<field list>, t2.<field list>
FROM <database name>.<user>.<table> AS t1 INNER JOIN
<database name>.<user>.<table2> AS t2 ON t1.id = t2.id

INSERT INTO MyTable (FirstCol, SecondCol)
SELECT ‘First’ ,1
UNION ALL
SELECT ‘Second’ ,2
UNION ALL
SELECT ‘Third’ ,3

Here’s a handy query for finding duplicates in a table. Suppose you want to find all email addresses in a table that exist more than once:

SELECT email,
COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

You could also use this technique to find rows that occur exactly once:

SELECT email
FROM users
GROUP BY email
HAVING ( COUNT(email) = 1 )

SELECT CONVERT(VARCHAR(8), GETDATE()-365, 112)

This is a way to query and get the date in the following format:

YYYYMMDD

I came across some dates saved in the database in this format as a varchar and had to use this to initially get the date and then convert it from and integer to varchar. An additional problem I had to conquer was to make the year minus one year, but I haven’t found that answer yet.

View Standard Date Formats

(Source)

Error: Back up the transaction log for the database to free up some log space.

To recover from a full transaction log

If the log is 100% full you cannot truncate it by backing it up, since the backup has to be recorded in the transaction log. For version 6.5, use:

DUMP TRANSACTION WITH NO_LOG

And for version 7 or later:

BACKUP LOG WITH NO_LOG

Since you cannot continue to perform transaction log backup after this command has been executed, you should perform a database backup.

Displays the number of rows, disk space reserved, and disk space used by a table, indexed view, or SQL Server 2005 Service Broker queue in the current database, or displays the disk space reserved and used by the whole database.

RIGHT
Returns the part of a character string starting a specified number of integer_expression characters from the right.

Examples
SELECT RIGHT(au_fname, 5) FROM authors

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = ANY
(SELECT pro_no
FROM vtm_data_combination
WHERE (total_weight = 0) AND (total_charges = 0) AND (total_pieces = 0)))

This query is essentially, imagining PRO_NUMBER is a 3 digit number:

SELECT *
FROM VTM_DATA_DETAILS
WHERE (PRO_NUMBER = 097) OR (PRO_NUMBER = 647) OR (PRO_NUMBER = 324)…..etc.

Until all PRO_NUMBER are selected from vtm_data_combination.