How to give sysadmin permission to user in Azure SQL Database

Question

How to grant sysadmin permissions to user in Azure SQL Database or how to give same level of access to a user in Azure SQL Database?

Solution

Use the azure portal. server-level principal login should be used to grant access to individual databases.

One can create new user & give rights to one or more databases:

    -- in master
create login [XXXX] with password = 'YYYYY'
create user [XXXX] from login [XXXX];

-- if you want the user to be able to create databases and logins
exec sp_addRoleMember 'dbmanager', 'XXXX';
exec sp_addRoleMember 'loginmanager', 'XXXX'

-- in each individual database, to grant dbo
create user [XXXX] from login [XXXX];
exec sp_addRoleMember 'db_owner', 'XXXX';

If you just created a login and a corresponding user, you have to add the appropriate role memberships...

e.g.,

exec sp_addrolemember 'dbmanager', 'login1User';
exec sp_addrolemember 'loginmanager', 'login1User';

Comments

[Champ]

Hi Nishant, I want to enable full rights for a user to one particular database. create user [XXXX]<I hope its new user> from login [XXXX]<wt is login here???>; exec sp_addRoleMember 'db_owner', 'XXXX';

[Nishant Singh]

Does this help you out ? stackoverflow.com/questions/6058058/…

[Champ]

Not able add users to individual database. Can you please provide some sample parameters that should be passed in the place of XXXXX... my actual error is "Error #: 2778 -- Only System Administrator can specify WITH LOG option for RAISERROR command"

[Nishant Singh]

I edited the answer ,, could you try that. Moreover dbmanager lets you create databases, loginmanager lets you create logins, but the server-level principal login must be used to grant access to individual databases

[Champ]

Hi Nishnat, Thanks for your help, but that doesn't solve the purpose.. i need to replicate System Administrator in sql azure.... as we give SysAdmin permissions to user in on-premise sql server.

[Champ]

As far as my analysis, there is no physical system exist for SQL azure. The database saves in different file storages. so there is not point to have in particular to system to give admin access/ role.