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?