0

what would be the SQL SERVER 2012 equivalent for Oracle all_users and user_roles_privs tables please ?

John Y
  • 14,123
  • 2
  • 48
  • 72
user3844877
  • 493
  • 4
  • 9
  • 18

1 Answers1

1

First of all: There are two 'leves' of 'users' in SQL Server. The first one is the Server Logins. These are the users who can connect to the server. The second 'level' are the Database Users (these users are mapped to Server Logins).

Also there are two types of privileges: Server level privileges and Database (object) level privileges.

These are the relevant catalog views:

  • Server logins: sys.server_principals and sys.sql_logins and sys.login_token
  • Database users: sys.database_principals
  • Database permissions: sys.database_permissions
  • Database role memberships: sys.database_role_members

You can use these functions too to check if a user has privilege: fn_my_permissions and HAS_PERMS_BY_NAME

You can read more about catalog views on MSDN: http://msdn.microsoft.com/en-us/library/ms174365.aspx

Also, please check this question: SQL Server query to find all permissions/access for all users in a database

Community
  • 1
  • 1
Pred
  • 8,789
  • 3
  • 26
  • 46