Wednesday, June 11, 2014

Recovery database from suspect mode

The database was in suspect mode, inaccessible Ran the following query Then restored from the latest full back up

EXEC sp_resetstatus client_367
ALTER DATABASE client_367 SET EMERGENCY
use client_367
DBCC checkdb
ALTER DATABASE client_367 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('client_367', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE client_367 SET MULTI_USER

Friday, June 6, 2014

Permission: To list the permissions on a schema

--the schema name is testschema
select pr.permission_name as permissionName,s.[name] as schemaname,s.[schema_id], p.[name] as principal_name, p.principal_id as pricipalID
from sys.schemas s
inner join sys.database_permissions pr
on s.schema_id = pr.major_Id
inner join sys.database_principals p
on pr.grantee_principal_id = p.principal_id
where s.[name] = 'testschema'