Create user on Azure elastic database and deny view to other databases i same setting

azureazure-sql-database

I have just shifted to Azure and I'm trying to set up some databases.

Previously I have been able to create users that can only see their own database with the following script. But on Azure this is giving me quite the headache.

declare @login_name sysname
declare @db_name sysname
declare @password sysname
declare @sql nvarchar(max)

SET @login_name = 'Username'
SET @password ='Password'
SET @db_name = 'Database'


USE MASTER

set @sql = 'CREATE LOGIN ' + QUOTENAME(@login_name)  + 'WITH PASSWORD = ''' + REPLACE(@password, '''', '''''')
execute( @sql )

set @sql = 'DENY VIEW ANY DATABASE TO ' + QUOTENAME(@login_name)
execute( @sql )

-- Set the user to use the database created
set @sql = 'ALTER LOGIN ' + QUOTENAME(@login_name)  + 
    ' WITH DEFAULT_DATABASE = ' + QUOTENAME(@db_name)
execute( @sql )


set @sql = 'USE ' + QUOTENAME(@db_name) + char(10) + 
    'EXEC sp_changedbowner ' + QUOTENAME(@login_name)
execute( @sql )

Everything I find online tells me just to

  • Create the login first
  • Then connect to the database I want to assign
    the login to
  • Then create a user and assign roles to this user

One thing is that this is quite a lot of connecting and disconnecting when I'm creating 10-20 databases at a time assigning users to everyone of them. Another thing is that I want make sure that when the users login to their database, they can only see their own database and not all other databases on that server.

What can I do, is there no way to create login & user, assign roles and limit view at the same time?

Best Answer

Reading you comment I see now what you are looking for.

Here is how you can do this.

Create 2 empty database:

USE [master]
GO
CREATE DATABASE [TestPerm1]
GO
ALTER DATABASE [TestPerm1] SET COMPATIBILITY_LEVEL = 130
GO


USE [master]
GO    
CREATE DATABASE [TestPerm2]
GO    
ALTER DATABASE [TestPerm2] SET COMPATIBILITY_LEVEL = 130
GO

Create 2 login 

USE [master]
GO    
CREATE LOGIN [testPerm1] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

USE [master]
GO    
CREATE LOGIN [testPerm2] WITH PASSWORD=N'strongPw', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

Create a user in master using one of the login

USE [master]
GO
CREATE USER [testPerm2] FOR LOGIN [testPerm2] WITH DEFAULT_SCHEMA=[dbo]
GO

Using SSMS connect with user testPerm2 using default setting. You will be able to see all database ( I know you do not want this, but hang on)

enter image description here

Create a user with testPerm1 in testPerm1 database that we created earlier.

USE [TestPerm1]
GO
CREATE USER [testPerm1] FOR LOGIN [testPerm1] WITH DEFAULT_SCHEMA=[dbo]
GO

Now using SSMS connect to the server by changing default database, this user will only see testPerm1 but not testPerm2.

enter image description here

enter image description here

Related Question