MSSQL: mudanças entre as edições
Ir para navegação
Ir para pesquisar
(→Docs) |
Sem resumo de edição |
||
| Linha 4: | Linha 4: | ||
-- Create LOGIN | -- Create LOGIN | ||
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a'; | CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a'; | ||
-- Create USER | -- Create USER | ||
CREATE USER readonlyuser FROM LOGIN readonlylogin; | CREATE USER readonlyuser FROM LOGIN readonlylogin; | ||
-- ADD ROLE MEMBER | -- ADD ROLE MEMBER | ||
EXEC sp_addrolemember 'db_datareader', 'readonlyuser'; | EXEC sp_addrolemember 'db_datareader', 'readonlyuser'; | ||
-- OR | -- OR | ||
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]; | ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser]; | ||
-- CREATE USER WITHOUT LOGIN | -- CREATE USER WITHOUT LOGIN | ||
CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a'; | CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a'; | ||
-- GET ALL PERMISSIONS EXPLICITLY GRANTED OR DENIED | -- GET ALL PERMISSIONS EXPLICITLY GRANTED OR DENIED | ||
SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, | SELECT DISTINCT pr.principal_id, pr.name, pr.type_desc, | ||
| Linha 23: | Linha 23: | ||
JOIN sys.database_permissions AS pe | JOIN sys.database_permissions AS pe | ||
ON pe.grantee_principal_id = pr.principal_id; | ON pe.grantee_principal_id = pr.principal_id; | ||
-- DELETE/REMOVE USER | |||
DROP USER readonlyuser; | |||
</syntaxhighlight> | </syntaxhighlight> | ||
Edição das 12h59min de 18 de outubro de 2021
Basic Commands
Azure SQL
-- Create LOGIN
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
-- Create USER
CREATE USER readonlyuser FROM LOGIN readonlylogin;
-- ADD ROLE MEMBER
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
-- OR
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser];
-- CREATE USER WITHOUT LOGIN
CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a';
-- 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;
-- DELETE/REMOVE USER
DROP USER readonlyuser;