Creating a SQL Azure user that cannot create tables

Question

I just have to create a new user on an SQL Azure database, that can SELECT/INSERT/UPDATE/DELETE from every table, and execute every proc, but CANNOT CREATE/ALTER/DROP anything.

In other words, I need a new user that cannot run DDL commands against the database.

I've already managed to create the login and the user (on master schema). Any help?

-- Edit 1 As soon as I created the new user on the SQL Azure, it already can create tables and procs (so, it's fundamental to REVOKE that power). Here arte the commands issued from the Master database:

CREATE LOGIN Sistema WITH PASSWORD = 'XXX';

CREATE USER Sistema 
FOR LOGIN Sistema 
WITH DEFAULT_SCHEMA = dbo; --This command was run on Master database, AND on my MainDatabase.

--Solution (based on Jisaak's answer):

--After creating login and user, I issued those commands from MainDatabase:

EXEC sp_addrolemember db_datareader, Sistema
EXEC sp_addrolemember db_datawriter, Sistema
EXEC sp_droprolemember db_owner, Sistema    --As it appears, SQL Azure defaults new users as "db_owners", which must be revoked

Community · Accepted Answer · 2017-05-23 11:51:11Z

5

Just assign the "db_datareader" and "db_datawriter" database role membership to the user. You can use the sp_addrolemember stored procedure for that which works fine on Azure:

EXEC sp_addrolemember 'db_datareader',  'USERNAME'
EXEC sp_addrolemember 'db_datawriter',  'USERNAME'

Edit:

You can check the rolememberships using this sql from gplwhite:

select m.name as Member, r.name as Role
from sys.database_role_members
inner join sys.database_principals m on sys.database_role_members.member_principal_id = m.principal_id
inner join sys.database_principals r on sys.database_role_members.role_principal_id = r.principal_id

edited May 23, 2017 at 11:51

CommunityBot

11 silver badge

answered Jul 16, 2015 at 6:54

Martin Brandl

59.4k16 gold badges151 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Marcelo Myara Over a year ago

thanks for the help. Problem is: as soon as the new user is created, he already can create tables and procs (and alter/drop too, I suppose). Another problem is that I'm receiving

Msg 15151, Level 16, State 1, Line 1 Cannot alter the role 'db_datareader', because it does not exist or you do not have permission.

when trying to assign a role using the sp_addtolemember from the master database.

Martin Brandl Over a year ago

I edited my answer and also the code sample, please try again.

Vladimir Glushkov Over a year ago

I'm having the exact same error as @MarceloMyara when trying to alter db_datareader in my Azure SQL master DB. In the same time I see it exists and I can alter roles like dbmanager.

Kevin Cloet Over a year ago

For people how have the 'Cannot alter the role...' error. Create Login must be executed on the master database. Create User query and alter role query must be executed on your target database.

Collectives™ on Stack Overflow

Creating a SQL Azure user that cannot create tables

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related