2

I executed the following script to create mydb_user and mydb_admin with limited permissions.

STEP1: Ran these scripts on azure masterdb

CREATE LOGIN mydb_admin 
WITH PASSWORD = 'nnn' 
GO
CREATE LOGIN mydb_user 
WITH PASSWORD = 'nnnnnnnnn' 
GO

Step2: Ran the below scripts on the actual azure db  eg., mydb

CREATE USER mydb_admin  FROM LOGIN mydb_admin ;
EXEC sp_addrolemember 'db_datareader', 'mydb_admin';
EXEC sp_addrolemember 'db_datawriter', 'mydb_admin';
GRANT CONNECT TO mydb_admin;
GRANT SELECT TO mydb_admin;
GRANT EXECUTE ON [dbo].[procDumpRowsInto_De_Common] TO [mydb_admin] AS [dbo]

CREATE USER mydb_user FROM LOGIN mydb_user;
EXEC sp_addrolemember 'db_datareader', 'mydb_user';
EXEC sp_addrolemember 'db_executor', 'mydb_user';
GRANT CONNECT TO mydb_user;
GRANT SELECT TO mydb_user;

Now I want to see if the roles and permissions are set correctly. Is there a query that I can run to see this? For instance, I want to see the effective roles and permissions for dbusers mydb_user,mydb_admin on my database myDb?

Joseph Idziorek
  • 4,853
  • 6
  • 23
  • 37
roney
  • 964
  • 3
  • 15
  • 37
  • 2
    Same as on SQL Server, you can call sys.fn_my_permissions. see http://stackoverflow.com/a/12789102 – Jack Richins Feb 02 '16 at 05:03
  • Invalid object name 'fn_my_permissions'. I tried that already, but I am not able to find any such object 'fn_my_permissions' in my azure 'master' db – roney Feb 02 '16 at 19:23
  • You are right, fn_my_permissions is not there on Azure SQL DB. You can call select HAS_PERMS_BY_NAME (db_name(), 'DATABASE', 'SELECT') to check whether a specific permission is granted or not. – Jack Richins Feb 03 '16 at 20:59

1 Answers1

0

You are right, fn_my_permissions is not there on Azure SQL DB. You can call select HAS_PERMS_BY_NAME (db_name(), 'DATABASE', 'SELECT'), or other permission you want to check for, to check whether a specific permission is granted or not either directly or via a role.

Jack Richins
  • 548
  • 2
  • 5