Read only access to SQL Azure DB

Just recently, I was testing out some stuff with SQL Azure. Then I got this bright idea of giving out the connection string to some friends, also for testing. As it turned out.. they all tried and succeeded in inserting data, a lot of data. So I got to thinking, is it possible to give out sql azure as read only?

Well there’s no better way than to try.

So I logged in to my SQL Azure DB and clicked “Manage” on the Master DB. This should restrict writing to ALL my databases as SQL Azure still does not support the USE keyword.

Then I create a readonly login

Then I create a user as guestreadonly

OK. So I have a type and a user. What I need to do next is to assign the role to the user and that’s it!

EXEC sp_addrolemember ‘db_datareader’, ‘guestuser’;

Advertisements
This entry was posted in Computers and Internet. Bookmark the permalink.

2 Responses to Read only access to SQL Azure DB

  1. Alexandria says:

    Hi Edu,
    I’d like to touch base with you. Please let me know how. Thanks!

  2. When I try to use EXEC sp_addrolemember ‘db_datareader’, ‘guestuser’;
    I get a “Message 15247” saying I don’t have permission. I am in the master. I have created the user and login. Any idea?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s