dbcc checktable repair_rebuild

Syntax:
[sql]
DBCC CHECKTABLE(‘TableName’,REPAIR_REBUILD)
[/sql]

This command checks the integrity of the table with respect to pages and structures and repairs it with no possible loss.

Example:
[sql]
ALTER DATABASE AdventureWorksDW2008R2 SET SINGLE_USER
DBCC CHECKTABLE(‘DatabaseLog’,REPAIR_REBUILD)
[/sql]

Msg 7919, Level 16, State 3, Line 1 Repair statement not processed. Database needs to be in single user mode.

Error Message:

Msg 7919, Level 16, State 3, Line 1
Repair statement not processed. Database needs to be in single user mode.

Example:
[sql]
DBCC CHECKDB(‘AdventureWorks’, REPAIR_REBUILD)
[/sql]

This error occurs when DBCC CHECKDB command is issued when the database is not in Single user mode.

Fix/Resolution:
Change the database to Sinle user mode and run the query.
[sql]
ALTER DATABASE AdventureWorks SET SINGLE_USER
GO
DBCC CHECKDB(‘AdventureWorks’, REPAIR_REBUILD)
[/sql]

Dont forget to bring the database to the user mode in which it is present before after running the query.

list of dbcc commands in sql server

Following are the list of dbcc commands in sql server 2012
DBCC CHECKALLOC
DBCC CHECKDB
DBCC CHECKCATALOG
DBCC CHECKFILEGROUP
DBCC CHECKTABLE
DBCC TABLE CHECK
DBCC TABLE REPAIR
DBCC ALLOC CHECK
DBCC ALLOC REPAIR
DBCC SYS CHECK
DBCC SYS REPAIR
DBCC SSB CHECK
DBCC CHECKCATALOG
DBCC IVIEW CHECK
DBCC INPUTBUFFER
DBCC SHOWCONTIG
DBCC OPENTRAN
DBCC SQLPERF
DBCC OUTPUTBUFFER
DBCC TRACESTATUS
DBCC PROCCACHE
DBCC USEROPTIONS
DBCC SHOW_STATISTICS
DBCC CHECKALLOC
DBCC CHECKFILEGROUP
DBCC CHECKCATALOG
DBCC CHECKIDENT
DBCC CHECKCONSTRAINTS
DBCC CHECKTABLE
DBCC CHECKDB
DBCC CLEANTABLE
DBCC INDEXDEFRAG
DBCC DBREINDEX
DBCC SHRINKDATABASE
DBCC DROPCLEANBUFFERS
DBCC SHRINKFILE
DBCC FREEPROCCACHE
DBCC UPDATEUSAGE
DBCC dllname
DBCC HELP
DBCC FREESESSIONCACHE
DBCC TRACEOFF
DBCC FREESYSTEMCACHE
DBCC TRACEON

VIEW CHANGE TRACKING Permission

VIEW CHANGE TRACKING is a permission provided by sql server.

Grant VIEW CHANGE TRACKING permission on a table to a user:
[sql]
GRANT VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]

Revoke VIEW CHANGE TRACKING permission on a table to a user:
[sql]
Revoke VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to username
[/sql]

DENY VIEVIEW CHANGE TRACKING permission on a table to a user:
[sql]
DENY VIEW CHANGE TRACKING ON OBJECT::dbo.databaselog to aaaaaa
[/sql]

Msg 4606 Level 16 State 1 Line 1 Granted or revoked privilege SELECT is not compatible with object.

Error Message:

Msg 4606, Level 16, State 1, Line 1
Granted or revoked privilege SELECT is not compatible with object.

This error occurs when you try to grant or revoke select permissions on stored procedure.
You cannot revoke/grant Select permission on the procedure.

Example:
[sql]
GRANT SELECT ON OBJECT::dbo.test TO AAAAAA
[/sql]
where dbo.test is a stored procedure
Fix:
– Remove SELECT in the query and give any of the below permissions as required.

[sql]
GRANT ALTER ON OBJECT::dbo.test TO AAAAAA
[/sql]
[sql]
GRANT CONTROL ON OBJECT::dbo.test TO AAAAAA
[/sql]
[sql]
GRANT EXECUTE ON OBJECT::dbo.test TO AAAAAA
[/sql]
[sql]
GRANT TAKE OWNERSHIP ON OBJECT::dbo.test TO AAAAAA
[/sql]
[sql]
GRANT VIEW DEFINITION ON OBJECT::dbo.test TO AAAAAA
[/sql]

Refer to the below link to learn more about permissions on Stored Procedures
Permissions On Stored Procedures in SQL Server

Msg 15151 Level 16 State 1 Line 1 Cannot find the user ‘username’ because it does not exist or you do not have permission.


Msg 15151, Level 16, State 1, Line 1
Cannot find the user ‘username’, because it does not exist or you do not have permission.

Example:
[sql]
GRANT EXECUTE ON OBJECT::dbo.test to username
[/sql]

Fix/resolution:

– Check if the user exists
[sql]
SELECT * FROM sys.server_principals WHERE name = N’username’
[/sql]
– Check if the stored procedure exists
[sql]
SELECT * FROM sys.procedures WHERE name = N’test’
[/sql]
– Make sure that the query is being executed in the database in which the stored procedure exists

Msg 297, Level 16, State 1, Line 1 The user does not have permission to perform this action.

Error Message:

Msg 297, Level 16, State 1, Line 1
The user does not have permission to perform this action.

Reason:
This error occurs when the user does not have permission to execute a specific query.

Example:
Revoke view state permission to user
[sql]
REVOKE VIEW SERVER STATE TO user_name
[/sql]
You will get the error message 297 when you execute this query to fetch the index usage stats
[sql]
select * from sys.dm_db_index_usage_stats
[/sql]

Fix/Resolution:
Provide the required permissions to the user.
[sql]
GRANT VIEW SERVER STATE TO user_name
[/sql]
Note: replace user_name with the name of the user.

Msg 229 Level 14 State 5 Procedure XXXXXX Line 1 The EXECUTE permission was denied on the object ‘XXXXX’, database ‘xxxxxx’, schema ‘dbo’.

Error Message:

Msg 229, Level 14, State 5, Procedure test, Line 1
The EXECUTE permission was denied on the object ‘test’, database ‘AdventureWorksDW2008R2’, schema ‘dbo’.

This error occurs when the user does not have execute permissions for the procedure on the database.

Fix/Resolution:
– Ask your DBA To grant you execute permissions on the procedure.

Query:
[sql]
GRANT EXECUTE ON OBJECT::dbo.test TO username
[/sql]
Note:
dbo.test – is the procedure name
username – is the name of the user

Grant Revoke Permissions On Stored Procedures in SQL Server

SQL Server Provides the following permission on the stored procedures:

– ALTER
– CONTROL
– EXECUTE
– REFERENCES
– TAKE OWNERSHIP
– VIEW DEFINITION

To Grant revoke permissions Expand Object Explorer and right click on the required stored procedure and select properties as shown below:

Expand Object Explorer

In the properties popup select Permissions, here you can grant or revoke the respective permissions.

Stored Procedure Permissions

T SQL Script to grant permissions on stored procedure:

[sql]
GRANT ALTER ON OBJECT::dbo.test TO username
GRANT CONTROL ON OBJECT::dbo.test TO username
GRANT EXECUTE ON OBJECT::dbo.test TO username
GRANT REFERENCES ON OBJECT::dbo.test TO username
GRANT TAKE OWNERSHIP ON OBJECT::dbo.test TO username
GRANT VIEW DEFINITION ON OBJECT::dbo.test TO username
[/sql]
Note: dbo.test is the procedure name.

T SQL Script to revoke permissions on stored procedure:
[sql]
REVOKE ALTER ON OBJECT::dbo.test TO username
REVOKE CONTROL ON OBJECT::dbo.test TO username
REVOKE EXECUTE ON OBJECT::dbo.test TO username
REVOKE REFERENCES ON OBJECT::dbo.test TO username
REVOKE TAKE OWNERSHIP ON OBJECT::dbo.test TO username
REVOKE VIEW DEFINITION ON OBJECT::dbo.test TO username
[/sql]