MSSQL: mudanças entre as edições
Ir para navegação
Ir para pesquisar
(→Docs) |
|||
| Linha 29: | Linha 29: | ||
*[https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15 Database-Level Roles] | *[https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/database-level-roles?view=sql-server-ver15 Database-Level Roles] | ||
*[https://azure.microsoft.com/pt-br/blog/adding-users-to-your-sql-azure-database/ Adding Users to Your SQL Azure Database] | *[https://azure.microsoft.com/pt-br/blog/adding-users-to-your-sql-azure-database/ Adding Users to Your SQL Azure Database] | ||
*[https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-database-permissions-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 sys.database_permissions (Transact-SQL)] | |||
*[https://docs.microsoft.com/en-us/azure/azure-sql/database/logins-create-manage Authorize database access to SQL Database, SQL Managed Instance, and Azure Synapse Analytics] | |||
Edição das 12h55min de 18 de outubro de 2021
Basic Commands
Azure SQL
-- Create LOGIN
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
GO
-- Create USER
CREATE USER readonlyuser FROM LOGIN readonlylogin;
GO
-- ADD ROLE MEMBER
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
GO
-- OR
ALTER ROLE [db_datareader] ADD MEMBER [readonlyuser];
GO
-- CREATE USER WITHOUT LOGIN
CREATE USER readonlyuser WITH PASSWORD = '1231!#ASDF!a';
GO
-- 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;
GO