2

How to using SQL statements to do below config for case 1 and 2?

Case 1: There are multiple databases DB1, DB2, DB3, ... in the Azure SQL managed instance How to create a new database user User1 that can only access DB1 and hide the other databases from this user (i.e., when User1 open the database server in the SQL Server Management Studio only show DB1 to him) At the same time, User1 needs to have privilege to perform below actions against DB1:

  1. Create tables
  2. Add columns
  3. CRUD access to the tables

Case 2: If the multiple databases DB1, DB2, DB3, ... are single databases (not in Managed instance) How to do the similar config as in case 1, i.e., User1 only can see DB1 with the above privileges?

I tried the solution from below question: How do I create a new user in a SQL Azure database?

CREATE LOGIN User1 WITH password='Test1234';
CREATE USER User1 FROM LOGIN User1;

But then the User1 can see all databases.

+++++++++++++++++++++++++++++++

1st Update:

Based on @Nick.McDermaid references (big thank you), I tried following SQL statements and they are working:

--the below statement grant user1 access to the database, it's working:
CREATE USER user1 WITH PASSWORD = 'Test1234';

--the below 4 statements grant user1 CRUD privilege on all tables, it's working:
GRANT SELECT TO user1;
GRANT INSERT TO user1;
GRANT UPDATE TO user1;
GRANT DELETE TO user1;

But having issue to grant create table and add column:

--intend to grant user1 creating table privilege, commands completed successfully, but when trying to create a table return below error:
--Microsoft SQL: The specified schema name "dbo" either does not exist or you do not have permission to use it.
GRANT CREATE TABLE TO user1;

--intend to grant user1 adding columns privilege, but Azure SQL return below error:
--Incorrect syntax near 'ALTER'.
GRANT ALTER TABLE TO user1;

+++++++++++++++++++++++++++++++

2nd Update:

My final SQL statements are:

CREATE USER [user1] WITH PASSWORD=N'Test1234'
GO

CREATE ROLE [test_role]
GO

ALTER ROLE [test_role] ADD MEMBER [user1];
GO

ALTER ROLE [db_ddladmin] ADD MEMBER [test_role];
GO
ALTER ROLE [db_datawriter] ADD MEMBER [test_role];
GO
ALTER ROLE [db_datareader] ADD MEMBER [test_role];
GO
J.W
  • 671
  • 7
  • 23
  • 1
    As mentioned in the comments to the accepted answer, the accepted answer is misleading. You don't want to create a Login, just a user in the database. https://learn.microsoft.com/en-us/sql/relational-databases/security/contained-database-users-making-your-database-portable?view=sql-server-2017#syntax-differences – Nick.Mc May 20 '19 at 05:43
  • 1
    This is the syntax you want, but you _first_ need to connect directly to the database under a login that has enough privileges https://learn.microsoft.com/en-us/sql/t-sql/statements/create-user-transact-sql?view=sql-server-2017#e-creating-a-contained-database-user-with-password – Nick.Mc May 20 '19 at 05:45
  • There's a lot of confusing answers in that linked question - ignore any that have `CREATE LOGIN` in them – Nick.Mc May 20 '19 at 05:47
  • @Nick.McDermaid thanks for the two helpful links. After creating the user in the database without creating a Login, how to use SQL statements to set up the privilege to limit the new user's rights? Any links/articles for reference please? – J.W May 21 '19 at 04:16
  • I don't know off the top of my head. I just google it. This was my first google result: https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-database-permissions-transact-sql?view=sql-server-2017#a-granting-permission-to-create-tables – Nick.Mc May 21 '19 at 04:21
  • Based on your requirements - you want people to be able to create and alter tables but you don't want them to create any other objects (views, procs etc.)? – Nick.Mc May 21 '19 at 04:22
  • Hi @Nick.McDermaid, please check out my update on the question. The "GRANT CREATE TABLE TO user1;" statement mentioned in the third link seems not working? here is my test database info if you would like to try: server: wjtest.database.windows.net, server admin login: test, admin password: 6efA5VOs – J.W May 23 '19 at 00:38
  • Looking at the docs it says `ALTER` is implied by `CREATE` so you don't need the `GRANT ALTER` script (it's not valid anyway). – Nick.Mc May 23 '19 at 03:52
  • 1
    Not too sure about the first error. Are you currently logged in as an admin? The bottom of this page: https://www.sqlservercentral.com/forums/topic/grant-permission-help-need-error-msg-2760 says you need to grant alter to the schema first. `GRANT ALTER ON SCHEMA::dbo TO User1;` – Nick.Mc May 23 '19 at 03:53
  • @Nick.McDermaid, I'm a bit confused but I see the docs says in an opposite way, `CREATE TABLE` is implied by `ALTER`, `ALTER` is implied by `CONTROL` (not `CREATE`), does it mean after granting `CREATE TABLE` still need to grant ALTER somehow? In SSMS I opened the server using the admin login `test`, and trying to create user1 with the limited privileges. – J.W May 23 '19 at 10:45
  • @Nick.McDermaid, after `GRANT ALTER ON SCHEMA::dbo TO user1`, the user1 got `DROP TABLE` privilege too (seems the grant scope is too large) But it is fine if we have to in order to grant the `CREATE TABLE` privilege. Thanks – J.W May 23 '19 at 12:02
  • Glad you worked it out. Keep in mind that all of these grants would normally be applied to a role, then a user is added to a role. Then you don't need to remember all the special grants every time you get a new user – Nick.Mc May 23 '19 at 12:57

0 Answers0