1

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?

  • is this what you are looking for? – SqlWorldWide Jun 15 '17 at 15:15
  • Not really, users can still see the other databases. But its close – Squazz Jun 15 '17 at 18:38
1

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.