Difference between revisions of "Azure SQL - Create User"
Jump to navigation
Jump to search
(4 intermediate revisions by the same user not shown) | |||
Line 2: | Line 2: | ||
<pre> | <pre> | ||
CREATE LOGIN user1 WITH PASSWORD = 'Password1'; | CREATE LOGIN user1 WITH PASSWORD = 'Password1'; | ||
+ | CREATE LOGIN user1 WITH PASSWORD = 'Password1' -- MUST_CHANGE; (Not supported on Azure SQL DW); | ||
CREATE USER user1 from LOGIN user1; | CREATE USER user1 from LOGIN user1; | ||
exec sp_addRoleMember 'dbmanager', user1; | exec sp_addRoleMember 'dbmanager', user1; | ||
Line 12: | Line 13: | ||
</pre> | </pre> | ||
* Check current role | * Check current role | ||
− | |||
<pre> | <pre> | ||
SELECT | SELECT | ||
Line 24: | Line 24: | ||
ON rm.member_principal_id = m.principal_id | ON rm.member_principal_id = m.principal_id | ||
</pre> | </pre> | ||
− | + | * Grants | |
+ | <pre> | ||
+ | GRANT CREATE PROCEDURE TO user1; | ||
+ | GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schema1 TO user1; | ||
+ | </pre> | ||
+ | * Change Resource Group (for Azure SQL DW) | ||
+ | https://docs.microsoft.com/en-us/azure/sql-data-warehouse/resource-classes-for-workload-management | ||
+ | <pre> | ||
+ | EXEC sp_addrolemember 'largerc', zbradfint; | ||
+ | </pre> | ||
* Set Firewall for Azure DB server | * Set Firewall for Azure DB server | ||
<pre> | <pre> | ||
Line 36: | Line 45: | ||
EXEC sp_delete_database_firewall_rule @name = N'InternaloFirewallRule' | EXEC sp_delete_database_firewall_rule @name = N'InternaloFirewallRule' | ||
</pre> | </pre> | ||
− | [[Category | + | [[Category:Azure SQL]] |
− | [[Category | + | [[Category:Azure SQL DW]] |
Latest revision as of 17:57, 8 September 2018
- Create a local SQL Server authenticated user
CREATE LOGIN user1 WITH PASSWORD = 'Password1'; CREATE LOGIN user1 WITH PASSWORD = 'Password1' -- MUST_CHANGE; (Not supported on Azure SQL DW); CREATE USER user1 from LOGIN user1; exec sp_addRoleMember 'dbmanager', user1; exec sp_addRoleMember 'loginmanager', user1;
- Create a domain authenticated user
create user [user2@Domain.com] from external provider; exec sp_addRoleMember 'db_ddladmin', 'user2@domain.com';
- Check current role
SELECT p.NAME rolename, m.NAME username FROM sys.database_role_members rm JOIN sys.database_principals p ON rm.role_principal_id = p.principal_id JOIN sys.database_principals m ON rm.member_principal_id = m.principal_id
- Grants
GRANT CREATE PROCEDURE TO user1; GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schema1 TO user1;
- Change Resource Group (for Azure SQL DW)
https://docs.microsoft.com/en-us/azure/sql-data-warehouse/resource-classes-for-workload-management
EXEC sp_addrolemember 'largerc', zbradfint;
- Set Firewall for Azure DB server
SELECT * FROM sys.firewall_rules ORDER BY name; SELECT * FROM sys.database_firewall_rules ORDER BY name; EXEC sp_set_database_firewall_rule @name = N'InternaloFirewallRule', @start_ip_address = '0.0.0.0', @end_ip_address = '255.255.255.255'; EXEC sp_delete_database_firewall_rule @name = N'InternaloFirewallRule'