MSSQL
Ir para navegação
Ir para pesquisar
Basic Commands
Azure SQL Database
Create LOGIN/USER
-- Create LOGIN
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
-- OR
USE [master]
GO
CREATE LOGIN [readonlylogin] WITH PASSWORD=N'1231!#ASDF!a', DEFAULT_DATABASE=[TESTDATABASE], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
-- Create USER
USE TESTDATABASE
GO
CREATE USER readonlyuser FROM LOGIN readonlylogin;
GO
-- CREATE USER WITHOUT LOGIN
CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a';
-- CREATE USER ON THE DATABASE
CREATE USER username WITH PASSWORD = 'MySuperS3cretP@ssw0rd';
ALTER ROLE [db_datareader] ADD MEMBER [username];
ALTER ROLE [db_datawriter] ADD MEMBER [username];
ALTER ROLE [db_owner] ADD MEMBER [username];
Drop User
DROP USER username;
Set Permission
-- ADD ROLE MEMBER
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
-- OR
USE TESTDATABASE
GO
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser];
GO
Get Permissions
-- GET ALL PERMISSIONS EXPLICITLY GRANTED OR DENIED
SELECT DISTINCT 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;
Get Detailed Permissions
-- GET DETAILED PERMISSIONS
;WITH
[explicit] AS (
SELECT [p].[principal_id], [p].[name], [p].[type_desc], [p].[create_date],
[dbp].[permission_name] COLLATE SQL_Latin1_General_CP1_CI_AS [permission],
CAST('' AS SYSNAME) [grant_through]
FROM [sys].[database_permissions] [dbp]
INNER JOIN [sys].[database_principals] [p] ON [dbp].[grantee_principal_id] = [p].[principal_id]
WHERE ([dbp].[type] IN ('IN','UP','DL','CL','DABO','IM','SL','TO') OR [dbp].[type] LIKE 'AL%' OR [dbp].[type] LIKE 'CR%')
AND [dbp].[state] IN ('G','W')
UNION ALL
SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
FROM [sys].[database_principals] [dp]
INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
INNER JOIN [explicit] [p] ON [p].[principal_id] = [rm].[role_principal_id]
),
[fixed] AS (
SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[name] [permission], CAST('' AS SYSNAME) [grant_through]
FROM [sys].[database_principals] [dp]
INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
INNER JOIN [sys].[database_principals] [p] ON [p].[principal_id] = [rm].[role_principal_id]
WHERE [p].[name] IN ('db_owner','db_datareader','db_datawriter','db_ddladmin','db_securityadmin','db_accessadmin','dbmanager','loginmanager')
UNION ALL
SELECT [dp].[principal_id], [dp].[name], [dp].[type_desc], [dp].[create_date], [p].[permission], [p].[name] [grant_through]
FROM [sys].[database_principals] [dp]
INNER JOIN [sys].[database_role_members] [rm] ON [rm].[member_principal_id] = [dp].[principal_id]
INNER JOIN [fixed] [p] ON [p].[principal_id] = [rm].[role_principal_id]
)
SELECT DISTINCT DB_NAME() [database], [name] [username], [type_desc], [create_date], [permission], [grant_through]
FROM [explicit]
WHERE [type_desc] NOT IN ('DATABASE_ROLE')
UNION ALL
SELECT DISTINCT DB_NAME(), [name], [type_desc], [create_date], [permission], [grant_through]
FROM [fixed]
WHERE [type_desc] NOT IN ('DATABASE_ROLE')
ORDER BY 1, 2
OPTION(MAXRECURSION 10);
Delete User
-- DELETE/REMOVE USER
DROP USER readonlyuser;
Get All databases
-- LIST ALL DATABASES
select [name] as database_name,
database_id,
create_date
from sys.databases
order by name
Get all tables
-- LIST ALL TABLES
select schema_name(t.schema_id) as schema_name,
t.name as table_name,
t.create_date,
t.modify_date
from sys.tables t
order by schema_name,
table_name;
Describe Table columns
exec sp_columns Employee;
Delete All data on a table
truncate table Employee;
Get Sessions
exec sp_who2;