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

ANSI And TSQL way of incrementing row no.!!

Hi someone today asked me to add column with autoincrementing values each time row is inserted. So i gave them this solution.
In TSQL we can achive it by :

Create customers_TSQL ( cust_id int IDENTITY (1,1) Primary Key, cust_name varchar(20))

Insert into customers_TSQL values('John')

This will insert a customer named John with cust_id set to 1.Each time a new customer is added cust_id is incremented by 1.
As we can see in create statement we have used IDENTITY (1,1) which says the IDENTITY property is what makes the column increment by 1 when a new row is added. The (1,1) after IDENTITY indicates the seed and increment value can be set when the table is created. If you wanted to have an initial value of 10 and increment it by 5 each time a record is added, you would use (10,5). You can also omit the (seed, increment value) part of the IDENTITY property, in which case the values default to one and one.

While in ANSI what we can do is:

Create customers_ANSI ( cust_id int Primary Key, cust_name varchar(20))

Insert into Customers_ANSI
Select COALESCE(MAX(cust_ID)+1,1),'John' FROM Customers_ANSI

Every time the above statement is executed it will insert value 1 greater than the max value of cust_id.

See COALESCE() in online help for further info.

UseFull Tips for Stored Procedure !!

• SET NOCOUNT ON should be the first statement in every stored procedure you create because it minimizes network traffic between SQL Server and client applications.
  
  Setting NOCOUNT on disables DONE_IN_PROC messages—the messages SQL Server normally sends to the client indicating the number of rows affected by a T-SQL statement.
  Because these messages are very rarely used, eliminating them conserves network bandwidth without really giving up any functionality and can speed up applications considerably. Note that you can disable DONE_IN_PROC messages for the entire server via a trace flag (3640) and for a particular user session via the sp_configure 'user options' command. (In rare circumstances, disabling DONE_IN_PROC messages can cause problems with some applications—for example, some older versions of Microsoft Access and certain ill-behaved OLEDB providers).
• A procedure can contain any valid Transact-SQL command except these: CREATE DEFAULT, CREATE FUNCTION, CREATE PROC, CREATE RULE, CREATE SCHEMA, CREATE TRIGGER, CREATE VIEW, SET SHOWPLAN_TEXT, and SET SHOWPLAN_ALL. These commands must reside in their own command batches, and, therefore, can't be part of a stored procedure. Procedures can create databases, tables, and indexes, but not other procedures, defaults, functions, rules, schemas, triggers, or views.You can work around this limitation—the inability to construct most other kinds of objects from within a stored procedure—by constructing a T-SQL string and executing it via sp_executesql or the EXEC() function like :

SET @sql=N'create proc dbo.test2 as select ''1'''EXEC dbo.sp_executesql @sql

• Although executing a stored procedure can be as easy as listing it on a line by itself in a T-SQL command batch, you should make a habit of prefixing all stored procedure calls with the EXEC keyword, like this: EXEC dbo.sp_who
• You should also be sure to owner-qualify procedure calls ("dbo" in the previous example). Omitting the owner from a procedure call causes SQL Server to momentarily place a compile lock on the procedure because it cannot locate it immediately in the procedure cache. This lock is released once the procedure-sans-owner is located in the cache, but can still cause problems in high-throughput environments. Owner-qualifying objects is simply a good habit to get into. It's one of those things you can do to save yourself problems down the road.

Some of the Stored Procedure Advantages !!

Stored Procedures have a number of advantages over ad hoc queries, including :-

• Execution plan retention and reuse
• Query autoparameterization
• Encapsulation of business rules and policies
• Application modularization
• Sharing of application logic between applications
• Access to database objects that is both secure and uniform
• Consistent, safe data modification
• Network bandwidth conservation
• Support for automatic execution at system start-up