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.

How to rebuild the server when the master database is crashed?

If master is crashed the server will not restart then follow these steps to bring back server online.

Step 1:
             First we have to check server error-log there it will mention restore the master database or re-install in event viewer-->windows logs-->application it will show that the master database is crashed, have to restore or re-install.

Step 2:
             Rebuild the system databases from command prompt. Go to the following path and run setup as follows
C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Step 3:

             Once the above command runs successfully, check the summary.txt file, if the final result is passed and check the requested action as Rebuilddatabase.

Step 4:
             Restart the SQL server services.

Step 5:
             Connect to the instance. But you cannot see any user defined databases. The rebuilding process has created fresh\brand new master  database so there are no other databases and all previous configurations are lost.

Step 6:
             Now we have to restore the master database to get all the previous configurations. Go to command prompt and run the instance in single user mode
net stop instancename
net start instancename /m

Step 7:
            Connect to the instance and take new query to restore master database. Once we restore master database, then we can get all the previous configuration values including user defined databases.

Step 8:
           Click on Connect button and connect to the instance.

Step 9:

           Restore master database as follows
            restore database master from disk='path.bak' with replace

Step 10:
            Restart the SQL server instance, it will start in multi user mode, here we can see all the user defined databases.

sp_who & sp_who2

There is a very useful system stored procedure called sp_who on SQL Server that lists the users and processes running on the system. It comes handy when you want to know the loading on the SQL Server or see if any process is blocked.It will give result as running spids, who login to the system with host name and what he is running on that machine.

sp_who
 

sp_who2 

It will give the more information that CPU time, disk io with last batch time and that login where (ie., on which database) he is executing the query.

select * from sys.dm_db_file_space_usage

We can use the sys.dm_db_file_space_usage dynamic management view to monitor the spaced used by tempdb for storing user/internal objects and version store.

sys.dm_db_file_space_usage has following columns:

1. database_id – will always be 2, database id of tempdb.

2. unallocated_extent_page_count – total number of free extents in the database file

3. version_store_reserved_page_count – pages allocated for version store

4. user_object_reserved_page_count – pages allocated for user objects, these are the permanent object you create explicitly in tempdb, system tables, temporary tables, table variables…

5. internal_object_reserved_page_count – pages allocated for internal objects

for details about other columns returned refer BOL.

Server roles

Role is a collection of privileges.  

There are 8 server roles in sql server they are as follows.

1. Sysadmin
2. Server admin
3. Setup admin
4. DB creator
5. Disk admin
6. Bulk admin
7. Process admin
8. Security admin

Sysadmin:  The members of this role can perform any operation on the server. This is the root level entry on the server.