Apply a new sql server collation

 Sometimes we will get a requirement to change server collation post installations.

In such cases please follow these steps.

Execute the command below, a lot of information will appears and no user action is required, just close the prompt window after the execution ends. The parameter "-s" is only necessary if more than one sql instance exists on the target machine.

Sql server -m -T4022 -T3659 -s"SQLEXP2014" -Q"SQL_Latin1_General_CP1_CI_AI"

After restore a database with override option, while trying to map a user to that database it will throw error as : User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

TITLE: Microsoft SQL Server Management Studio
------------------------------

Create failed for User 'hello'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1539+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+User&LinkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

User, group, or role 'hello' already exists in the current database. (Microsoft SQL Server, Error: 15023)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=15023&LinkId=20476

------------------------------
BUTTONS:

OK
------------------------------

In this case we have to delete/drop that user from that particular database, again we have to create a user under that database.

How to enable SQL Server Agent?

How to enable SQL Server Agent

Procedure:

Execute the following Queries and refresh the server

Step1:

exec sp_configure 'agent XPs'
if it display an error that advanced options are disabled then by running the following query enable the show advanced options and reconfigure

Step2:  Run the Query to enable advanced options

exec sp_configure 'show advanced options' , 1
go
reconfigure
go

step 3:
exec sp_configure 'show advanced options'
go

Step4:
Enable agent XPs by using the query

exec sp_configure 'agent XPs'
go
exec sp_configure 'agent XPs',1
go
reconfigure
go
exec sp_configure 'agent XPs'

Step5:
now restart the SQL Server Agent / Server. so that SQL Server Services should be enabled

still if it doesn't start then

Step 6:
goto start-->run type services.msc and press OK

Step 7:
Services window will be opened then goto SQL Server Agent, Right click on it and go for start

Now we can see the SQL Server Agent started in our Object Explorer

When we are trying to attach the database it will throw an error as CREATE FILE encountered operating system error 5(Access is denied.) While attempting to open or create the physical file.... (Microsoft SQL Server, Error 5123).

For this error the solution is that there are two different logins in the server, here detaching is happened with one login and while attaching with other login. So there is no access to attach the database, while detaching the database whoever detaching the database that user is the owner of the mdf file so we need to change the permissions.

right click on that mdf file --> select properties--> check permissions of the mdf file--> there we can see only one account has permission on that mdf file, because that account is detached the database so that the user have full rights.

To solve this issue click on 'Add..' add that other login and give full rights to that user, do this process for the ldf file as well. Once you have completed this task click on 'OK' button.

Now try the attach process, it will attach the database successfully. 

How to GRANT ALTER TRACE permission in SQL Server 2005/2008?

If user don't have profiler run permission, though he has dbowner access because profiler is a server level so to give profiler access permission follow below any one procedure.When the user is not having 'sa' permission and don't have Alter trace permission, he can't run profiler.

Method 1: By using GUI.

1) Connect the server in SSMS(SQL Server Management Studio)

2) Go to Object Explorer
3) expand the Security folder
4) expand the Logins folder
5) Right click Login name and select properties
6) Select securables tab
7) Click search
8) Select The server 'server name'
9) Click OK
10) Select permission Alter trace
11)Select Grant
12)Click OK.

Method 2: By using SQL script.

use master
GRANT ALTER TRACE TO [Username]

How to remove ALTER TRACE permission.

To revoke the ALTER TRACE permission from the user follow below any one of the methods.

Method 1:By using GUI.

1) Connect the server in SSMS(SQL Server Management Studio).

2)  Go to Object Explorer
3) Select and expand the Security folder
4) Select and expand the Logins folder
5) Right click Login name and select properties
6) Select securables tab
7) Click search
8) Select The server 'servername'
9) Click OK
10) Select permission Alter trace
11)unselect Grant permission
12)Click OK.

Method 2:  By using SQL script.

use master

REVOKE ALTER TRACE FROM [username]

If login is unable to delete, what are the reasons to get the errors? how to solve the issue?

                  When we are deleting the login, by normally it will delete. In some cases it will throws an error.First read the error why it is unable to delete, based on those errors we will solve the issue

• unable to delete the user because schema is owned by the user.

In this situation, it clearly says that the user is the owner of a schema so that the login is unable to delete. To find out which schema is owned by that user, go to the database which is shown in the error dialogue box expand the database -> expand security -> select schemas -> press F7   then in right side pane it will show the name and owner of the schema then right click on properties -> then change  schema owner as dbo -> click OK. It will delete now.

• in another case it throws an error that could not drop login 'some user' as the user is currently logged in.

 In this situation to find the open session by that user run the stored procedure sp_who2 the output will show with spid, now check that spid what it is running by using this command                        dbcc inputbuffer(spid) then kill that spid if it is running less priority query, mostly that spid is in sleeping state so we can kill that then delete the login.

How to add a Linked Server?

To add a linked server using SQL Server Management Studio(SSMS), open the server where you want to create a link from in object explorer.

Step 1: In SSMS -> object explorer -> server -> server objects ->  Linked servers(right click on the linked server folder and select "New Linked Server")

then click on that New Linked Server option, it will display as follows.

Step 2: The New Linked Server dialog will appears as follows

Step 3: For "Server Type" make sure that "Other Data Source" is selected.

Step 4: Provider-- Select "Microsoft OLE DB Provider for SQL Server".

Step 5: Product Name--SQLSERVER

Step 6: Data source-- Type the actual server name and instance name (SERVERNAME\INSTANCENAME).

Step 7: Provider String-- Blank.

Step 8: catalog--Optional.

Step 9: Click the security and click on "Be made using the login’s current security context", so that it provides more secure.

Step 10: Click on "Server Options" -> in right pane change RPC & RPC Out to "True" as follows.

Step 11: Click OK, and the New Linked Server is created.