|update userinfo set enable = 1
where id in (select User_ from SECURITYUSERROLE
where SECURITYROLE in (select recid from SECURITYROLE
where SECURITYROLE.aotname = ‘-SYSADMIN-‘ ))
I always login on the BI server because Sql Studio is installed on that server. The connection to the database is also a little different. See the screen dump below. One has to add ‘. database.windows.net’ at the end of the SQL server name. The SQL server name and login user are shown in LCS
So login this way.
|Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : The server principal “axdeployuser” is not able to access the database “master” under the current security context..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user ‘axdeployuser’..
Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Cannot open user default database. Login failed..
This error is related to the orphan users – usernames being correct but the users’ SPID are different. These have to be removed from the database and hooked in again.
|CREATE USER axdeployuser FROM LOGIN axdeployuser EXEC sp_addrolemember ‘db_owner’, ‘axdeployuser’|
In the remote case that this does not work, make sure to remove the users everywhere – on security and on system databases. In this unfortunate event, the users have to be recreated from scratch.
The password is shown on the environment’s LCS.
|CREATE LOGIN axdeployuser WITH PASSWORD = IkIi*2jG!MpQE’;
CREATE USER axdeployuser FROM LOGIN axdeployuser;