Another Certificate !!

Hi,
Guys another certificate from BrainBench.
I like this site very much as it tells you about the strengths and weakness of a particular topic.
Here is my transcript if u wanna check...

http://www.brainbench.com/transcript.jsp?pid=2420160

Troubleshooting Orphaned Users

Although the terms login and user are often used interchangeably, they are very different.

• A login is used for user authentication
• A database user account is used for database access and permissions validation.
  

  

Logins are associated to users by the security identifier (SID). A login is required for access to the SQL Server server. The process of verifying that a particular login is valid is called "authentication". This login must be associated to a SQL Server database user. You use the user account to control activities performed in the database. If no user account exists in a database for a specific login, the user that is using that login cannot access the database even though the user may be able to connect to SQL Server. The single exception to this situation is when the database contains the "guest" user account. A login that does not have an associated user account is mapped to the guest user. Conversely, if a database user exists but there is no login associated, the user is not able to log into SQL Server server.
When a database is restored to a different server it contains a set of users and permissions but there may not be any corresponding logins or the logins may not be associated with the same users. This condition is known as having "orphaned users."

When you restore a database backup to another server, you may experience a problem with orphaned users. The following scenario illustrates the problem and shows how to resolve it.

Use mastersp_addlogin 'test', 'password', 'Northwind'

SELECT sid FROM dbo.sysxlogins WHERE name = 'test'

Grant access to the user you just created

Use Northwind

sp_grantdbaccess 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'0xE5EFF2DB1688C246855B013148882E75
As you can see, both SID's are identical.

Backup the database

Use masterBACKUP DATABASE NorthwindTO DISK = 'C:\Northwind.bak'
Copy the Backupfile to another Maschine and SQL Server and restore it as follows:
RESTORE FILELISTONLYFROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'NorthwindNorthwind_log
RESTORE DATABASE TestDBFROM DISK = 'C:\Users\Zahn\Work\Northwind.bak'WITH MOVE 'Northwind' TO 'D:\DataMSSQL\Data\northwnd.mdf', MOVE 'Northwind_log' TO 'D:\DataMSSQL\Data\northwnd.ldf'

The restored database contains a user named "test" without a corresponding login, which results in "test" being orphaned.
Check the SID's
Use masterSELECT sid FROM dbo.sysxlogins WHERE name = 'test'0x39EE98D37EAC2243B7833705EC1C60E3
Use TestDB

SELECT sid FROM dbo.sysusers WHERE name = 'test'0xE5EFF2DB1688C246855B013148882E75

Now, to detect orphaned users, run this code
Use TestDB

sp_change_users_login 'report'

test 0xE5EFF2DB1688C246855B013148882E75

The output lists all the logins, which have a mismatch between the entries in the sysusers system table, of the TestDB database, and the sysxlogins system table in the master database.

Resolve Orphaned Users
Use TestDB

sp_change_users_login 'update_one', 'test', 'test'

SELECT sid FROM dbo.sysusers WHERE name = 'test'0x39EE98D37EAC2243B7833705EC1C60E3

use master

SELECT sid FROM dbo.sysxlogins WHERE name = 'test'0x39EE98D37EAC2243B7833705EC1C60E3

This relinks the server login "test" with the the TestDB database user "test". The sp_change_users_login stored procedure can also perform an update of all orphaned users with the "auto_fix" parameter but this is not recommended because SQL Server attempts to match logins and users by name. For most cases this works; however, if the wrong login is associated with a user, a user may have incorrect permissions.

More Details can be found here

SQL Server2005 Training Free by Microsoft!!

Hi All,

For all those who wants to start in SQL Server 2005 can view microsoft link for SQL Server 2005 Training.

U can find it here:
http://www.microsoft.com/events/series/msdnsqlserver2005.mspx

Lots of webcast and training material is provided.

So visit and have fun .........

Learn and learn u will earn more..

My Interview Experience!!

Hi All ,
On last Saturday I went to take interview for SQL Server Programmers.

I found lots of people were not aware of basic questions of SQL Server.

Some knew SQL as such but not much in SQL Server......Some were mixing there experience of oracle with SQL Server.
Some were answering the questions like an essay etc.
Most of them were not able to clear the basic SQL questions.

Solution:
Try to find a good book and follow it.
Remember all u'r basics clearly.
Try to give short answers unless specified.
Good communication skill is always desired.
Be cool and calm at the place of interview.
Dont take any question granted and if dont know the answer dont try to make on your own.
Improve the quality as such not the quantity.

Some of the links below have questions commonly asked in the interviews:
http://vyaskn.tripod.com/iq.htm
http://www.allapplabs.com/interview_questions/
db_interview_questions.htm
http://www.techinterviews.com/?p=161
http://www.geekinterview.com/Interview-Questions/Oracle/SQL

Hope U will find good questions on SQL Server......U can mail me at .....
shekhar.saxena@programmer.net

Brainbench Certification !!!

Hey Guys ....
I cleared BB Certification for RDBMS just like that....

View my transcript here:
http://www.brainbench.com/transcript.jsp?pid=2420160