Skip to main content

Posts

Grant Revoke privilege in SQL

Grant/Revoke are the keywords which uses to grant/revoke privileges on an object to/from a user/group. Grant is a keyword you use when you want to authorize/authenticate. a principal and Revoke is the Keyword used when you want to take back the Authorization/authentication from the Principal. Principal refers to Lo-gin at instance level.Database user at Database level. Grant,revoke,deny,with grant are object level security.Grant means giving permission on a particular object to user.Command: grant privilege on object name to username. Revoke means taking back the permission. Command: revoke privilege on object name from username. Deny means restrict user not to do something command: deny privilege on object name to username. With grant means by using grant user can give grant permission to other user command: grant privilege on object name to user with grant.

Clustered index and non clustered index in SQL

Clustered index which reordered the table and saved separately as index table(data pages stored as B-tree) there can be one clustered index per db & Non clustered index is typically a index(data pages stored as heap) A table can have only one clustered index with many non clustered indexes. The leaf nodes of the cluster index stores data pages and non clustered index stores key values(where as table includes clustered index) or rid's(table contains non cluster index only).

Advantage and disadvantage of Team Viewer , Ammy Admin and show my PC etc

Advantage of Team Viewer , Ammy Admin and show my PC etc You can share your desktop to other user or pc to take live support help from your friend or colleague. You can also help to other friends also. If you want to use limited hours in a month use of   Team Viewer , Ammy Admin and show my PC then you can use trial version but if you want to use lot of time in a single month then you need to buy license. You can buy license online. Disadvantage of Team Viewer , Ammy Admin and show my PC Mostly these type of connections registers all clicks and keypress, that means they can know about all your logins and passwords and you PC could be hacked or misused(Its possibility but mostly they do not do it).

How will you upgrade for Active and Active cluster server

To upgrade for Active & Active cluster server Active to active means you will install SQL server on each node so they will be active to active. First transfer both sql server service on node A and upgrade Node B then transfer both services on node B and upgrade node A and then split the service on separate nodes(Node A & B)

Datebase-Unavailable database:testdb is unavailable(suspect,offline,recovering etc) in SQL

SQL Error:  Database-Unavailable database  :testdb is unavailable(suspect,offline,recovering etc) 1.If the database is offline, the approach should be to bring the database online by either from GUI or from query : ALTER DATABASE <database_name> SET ONLINE and if it still doesn't come up..we should check the logs for the reasons..and based on the outcome we have to proceed accordingly. 2.If the Database is offline, then try to make the database online using the script ALTER DATABASE <database name> SET ONLINE. If it is not recover the database to online, then check in event viewer for possible error, if error is 17204--then data file has damaged,if error is 17207--- then log file has damaged,If you see this error then the database is there in suspect state. To Recover the suspect state database u need to follow steps as below: If data file damaged: 1. Take the tile log back up. 2.Restore Full back up with no recovery. 3.Restore Differential & Log Backups

How to Shrink TempDB in Sql Server?

To Shrink TempDB in Sql Server below ideas can help you to handle the issue. If it is data file then Instead of shrinking you should check the open transactions causing tempdb to get full..if it is not critical you can kill the process or else should add secondary data files. Shrinking log whereas is safe witch you can do by dbcc shrinkfile command. 1)If mdf file is full we can shrink that but sometimes we can’t shrink mdf file the reasons are: (i)check if any locks are there through (sys.dm_tran_locks)or sp_locks (ii)check for sys.dm_db_session_space_usage which will allows to check for page allocation and deallocation for each session on that instance (iii)check for temporary objects in tempdb using (select * from sys.all_objects where is_ms_shipped=0)-ser created temp objects. These are called as cached objects which are residing in procedure cache which helps stored procedure to make use of the query plan. But now it is not getting shrinked we have to perform DBCC FREEPROCCACHE wh