How to create sql Login by script.


To create sql login user follow the scripts below :

First have to login to sql management studio with other user who have the permission to create user or by Windows authentication

and then run the following scripts :

CREATE LOGIN DesiredUserName WITH PASSWORD = 'DesiredUserPassword';

Now to map this login user to a database use the following script:

USE  dbNewDB
 GO
 CREATE USER DesiredUserName FOR LOGIN DesiredUserName WITH DEFAULT_SCHEMA=[dbo]
 GO       
 GRANT CONNECT,CONTROL to DesiredUserName
Advertisements

How to map a user to a sql server database


Suppose a new database ‘dbNewDB’ is created on sql server database. Now to add an existing  user(existing sql server user) to this database with control,  following script can be use :

Let the existing user is ‘existingUser’

USE  dbNewDB
GO
CREATE USER existingUser FOR LOGIN existingUser WITH DEFAULT_SCHEMA=[dbo]
GO       
GRANT CONNECT,CONTROL to existingUser

Can’t connect to remote sql server


Sometime we fail to connect  SQL Server(both by application and by local sql server) installed on remote machine . We do try by chnaging connection string and enbling and disabling a lot of option in the server but can’t fix.

I my self face that problem and after trying a lot of things solve that. I found there are two settings related to that by enbling these we can access the server remotely.

1. Go to the server machine open SQL server management studio and log in to the server and open the server properties window and check the ‘Allow remote connections to this server’ option as by the figure below :

AllowRemoteConnection

2. Second one is to enable TCP/IP protocol for the SQL Sever, we can do this from the computer management studio. Go to controlpanel enter into Administrative Tools click and open the computer management studio and click on the ‘Protocols for MSSQLSERVER’ node and right click on the TCP/IP from the right side studio and make TCP/IP enable as shown by the picture below :

Can't connect remote sql server

Now try to connect the SQL server remotely, Hope now it will be available to remote clients.

How to clear SQL server cache / Database Query optimization task consideration


Sometime we developers assigned for query optimization tasks or performance improvment of system, and we start with it, We found some query takes very low time in the  Sql server query management studio but taking more time when calling from application, even some time we see that a query execution time varies time by time. And it seems to us like a puzzle and we go around but could not solve that problem. And it hampers our optimization task.

This happens because of some cache of SQL server, Each time a query or procedure runs, SQL server caches the query with the result, so if second time this query comes again to SQL server it returs the cache data so its take less time than before.

So to get over from this problem and to get actual execution time run the following  commands before running a query,

DBCC   FREEPROCCACHE  /* Remove all elements from the procedure cache.*/

DBCC    FREESESSIONCACHE  /*Flush the distributed query connection cache.*/

DBCC   FREESYSTEMCACHE(‘ALL’)  /* Release all unused cache entries from all caches.*/

DBCC   DROPCLEANBUFFERS  /*Remove all clean buffers from the buffer pool.*/

DBCC   PROCCACHE /*Display information about the procedure cache*/