2

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
Marcelo Myara
  • 2,841
  • 2
  • 27
  • 36

1 Answers1

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
Community
  • 1
  • 1
Martin Brandl
  • 56,134
  • 13
  • 133
  • 172
  • 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. – Marcelo Myara Jul 16 '15 at 11:35
  • I edited my answer and also the code sample, please try again. – Martin Brandl Jul 16 '15 at 12:10
  • 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. – Vladimir Glushkov Nov 20 '17 at 15:36
  • 22
    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. – Kevin Cloet Jan 31 '18 at 14:19