Azure SQL - Create User - Revision history

Ittichai at 22:57, 8 September 2018

2018-09-08T22:57:26Z

Ittichai at 19:59, 7 September 2018

2018-09-07T19:59:32Z

Ittichai at 19:57, 7 September 2018

2018-09-07T19:57:42Z

Ittichai at 19:57, 7 September 2018

2018-09-07T19:57:27Z

Ittichai at 19:56, 7 September 2018

2018-09-07T19:56:08Z

Ittichai at 19:54, 7 September 2018

2018-09-07T19:54:35Z

Ittichai: Created page with "

 CREATE LOGIN user1 WITH PASSWORD = 'Password1'; CREATE USER user1 from LOGIN user1; exec sp_addRoleMember 'dbmanager', user1; exec sp_addRoleMember 'loginmanager', user1..."

2018-09-07T19:52:03Z

Created page with "<pre> CREATE LOGIN user1 WITH PASSWORD = 'Password1'; CREATE USER user1 from LOGIN user1; exec sp_addRoleMember 'dbmanager', user1; exec sp_addRoleMember 'loginmanager', user1..."

New page

<pre>
CREATE LOGIN user1 WITH PASSWORD = 'Password1';
CREATE USER user1 from LOGIN user1;
exec sp_addRoleMember 'dbmanager', user1;
exec sp_addRoleMember 'loginmanager', user1;

</pre>
* Check current role
Check current roles
<pre>
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
</pre>

* Set Firewall for Azure DB server
<pre>

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'
</pre>
[[Category|Azure SQL]]
[[Category|Azure SQL DW]]

@@ Line 28: / Line 28: @@
 GRANT CREATE PROCEDURE TO user1;
 GRANT SELECT, INSERT, UPDATE, DELETE, ALTER, EXECUTE, VIEW DEFINITION ON SCHEMA::schema1 TO user1;
 </pre>
 * Set Firewall for Azure DB server

@@ Line 40: / Line 40: @@
 EXEC sp_delete_database_firewall_rule @name = N'InternaloFirewallRule'
 </pre>
-[[Category|Azure SQL]]
+[[Category:Azure SQL]]
-[[Category|Azure SQL DW]]
+[[Category:Azure SQL DW]]

@@ Line 13: / Line 13: @@
 </pre>
 * Check current role
 <pre>
 SELECT

@@ Line 25: / Line 25: @@
 ON rm.member_principal_id = m.principal_id
 </pre>
+* Grants
 * Set Firewall for Azure DB server
 <pre>

@@ Line 1: / Line 1: @@
 <pre>
 CREATE LOGIN user1 WITH PASSWORD = 'Password1';
@@ Line 4: / Line 5: @@
 exec sp_addRoleMember 'dbmanager', user1;
 exec sp_addRoleMember 'loginmanager', user1;
+</pre>
 </pre>
 * Check current role
@@ Line 10: / Line 15: @@
 <pre>
 SELECT
-    p.NAME rolename
+    p.NAME rolename,
-  ,m.NAME username
+   m.NAME username
 FROM
     sys.database_role_members rm
@@ Line 22: / Line 27: @@
 * Set Firewall for Azure DB server
 <pre>
 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',