Toni on Tech

Manage Azure database security with T-SQL

—

by

Toni Frankola
in

This is slightly off-topic but we recently deployed some SQL databases to Windows Azure. I wanted to tighten security and allow users to connect to a particular views only. To my amazement, even though you can connect to Azure databases with SQL Management Studio, you cannot manage any of the advanced properties including security.

The following code will:

  • Create a custom role
  • Grant it privileges to a single view
  • Create a custom user and add it to the role above

Here is the code:


USE MyDatabase
GO

CREATE LOGIN [MyLoginName] WITH PASSWORD=N'MyPassword'
GO

CREATE ROLE [MyRole]
GO

GRANT SELECT ON [All] TO [MyRole]
GO

CREATE USER [MyLoginName] for LOGIN [MyLoginName];
GO

EXEC sp_addrolemember 'MyRole', 'MyLoginName'
GO