/**********************************************/
sp_tables
/**********************************************/
2. Database level
permissions except fixed database role
Note: The permissions
of fixed database roles do not appear in sys.database_permissions
/**********************************************/
SELECT pr.principal_id, pr.name,pr.type_desc,
pr.authentication_type_desc, pe.state_desc,pe.permission_name
FROM sys.database_principals AS pr
JOIN sys.database_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id;
/**********************************************/
3. Users and database
roles (Fixed database roles)
/**********************************************/
SELECT DPUsers.name, 'is a member of
',DPRoles.name
FROM sys.database_role_members AS DRM
JOIN sys.database_principals AS DPRoles
ON DRM.role_principal_id = DPRoles.principal_id
JOIN sys.database_principals AS DPUsers
ON DRM.member_principal_id = DPUsers.principal_id;
/**********************************************/
4. Server level
permissions except fixed database role
Note: The permissions
of fixed server roles do not appear in sys.server_permissions
/**********************************************/
SELECT pr.principal_id, pr.name,pr.type_desc,
pe.state_desc, pe.permission_name
FROM sys.server_principals AS pr
JOIN sys.server_permissions AS pe
ON pe.grantee_principal_id = pr.principal_id
/**********************************************/
5. Users and Server
roles (Fixed Server roles)
/**********************************************/
SELECT SPUsers.name, 'is a member of
',SPRoles.name
FROM sys.server_role_members AS SRM
JOIN sys.server_principals AS SPRoles
ON SRM.role_principal_id=SPRoles.principal_id
JOIN sys.server_principals AS SPUsers
ON SRM.member_principal_id = SPUsers.principal_id;
/**********************************************/
6. To give access on
shared path
/**********************************************/
EXEC sp_pdw_add_network_credentials '100.100.100.100', 'domain\account', 'password_here';
/**********************************************/
7. To find session
status
/**********************************************/
select * from sys.dm_pdw_waits whereobject_name = 'AdventureWorksPDW2012' and
Session_id = 'SID424642'
/**********************************************/
select * from sys.dm_pdw_waits where state =
'Queued' and type = 'Exclusive' andobject_name = 'DBname'
and Session_id ='SID424642'
/**********************************************/
8. To find when lock
is requested and when its given
/**********************************************/
select * from sys.dm_pdw_lock_waits where
Session_id = = 'SID424687'
/**********************************************/
9. Types of waits in
PDW server
/**********************************************/
select distinct type from sys.dm_pdw_waits
/**********************************************/
10. Details of Active
sessions
/**********************************************/
Select session_id ,* fromsys.dm_pdw_exec_sessions where login_name ='login_name' and status = 'Active' and Session_id = 'SID424642'
/**********************************************/
11. Creating
Table using CTAS
/**********************************************/
use CTAS
CREATE TABLE myTable_Test
WITH
(
CLUSTERED COLUMNSTORE INDEX,
DISTRIBUTION = HASH (AccountKey)
)
AS select * from DimAccount;
/**********************************************/
12. Insert from
table
/**********************************************/
INSERT INTO myTable_Test
select * from DimAccount
GO
/**********************************************/
13. Insert recursive
data
/**********************************************/
INSERT INTO myTable_Test
SELECT *
FROM
AdventureWorksPDW2012.dbo.myTable_Test
GO
/**********************************************/
Check this out
ReplyDeletewebsite development
Web development Company