SQL Server, Connection String creation

Whenever we need to create a connection string for a new database or in the same database then we need to follow simple few steps listed below:

1. Create the login in the master database using the query given in the above mentioned reference file name or see as given
   CREATE LOGIN [XYZ] WITH PASSWORD=N'XYZ',
DEFAULT_DATABASE=[Master], DEFAULT_LANGUAGE=[us_english],
CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
   NOTE-if you are using the sql server GUI to create a new login then please make sure to un check the enforce password policy otherwise there would be problem with the connection string in the near future
2. Create the User in the concerned Database and attach the user with the particular login any failure in doing so will result an error while using the connection string. Set the default schema name with it too (default schema name is useful as all the procedures under that default schema can be accessed by the application using the procedure name only if any other schema procedure need to be executed which has the permission then application need to specify the schema name along with the procedure name otherwise the procedure will not be found.
   CREATE USER [XYZ] FOR LOGIN [XYZ] WITH DEFAULT_SCHEMA=[schimabnameSP]
GO
3. Create the Role using the query given in the above mentioned file. The role plays the most important part in the assignment of the permission to different schemas or database objects for the newly created login or connection string.
   CREATE ROLE [DBR_rolename] AUTHORIZATION [dbo]
GO
4. Go to the concerned database security folder and then into the schema folder select the particular schema for which we need to assign the permission.
5. Click the property after the right click on the schema
6. In the property go to the permission tab and search for the "Role Name" and give the execute permission and then save it and close the pop up
7. Then go to the "User" folder and right click on the concerned user and select the role from the list shown below and close the pop up after clicking "OK" button.

If all the steps are followed then the connection string will work properly

Common Loop Hole –

1. In order to check the connection string when we login with the user name and password set in step 1 then we are unable to see the procedure list (solution user is not associated with the role)
2. "Execute permission is denied" this is the common issue reported by the application developers (Solution check if the application developer is using correct connection string or not and if the connection string is right then we need to check if the concerned schema is associated with the correct role with the execution permission) (Solution if the above solution is not solving the problem then check if the User is associated with the schema or not if so then remove the user and use role )

NOTE- We do not give any permission to the schema under which we make tables and views, only schemas which has the procedure need to be given EXECUTE PERMISSION. Schema should not have the direct permission for the user in any circumstances.

Need to give access to different database in the same server

Go to the login name right click on it and go to the “User Mapping” tab and select the concerned database. on selection of the database all the roles for that database will be listed below select the desired role and save the setting.