0

I need to check whether a user is db owner.

I've found two methods:

  • look at "exec sp_helprolemember" output table
  • select count(*) from (select DbRole = g.name, MemberName = u.name, MemberSID = u.sid from sys.database_principals u, sys.database_principals g, sys.database_role_members m where g.principal_id = m.role_principal_id and u.principal_id = m.member_principal_id and g.name='db_owner') output where MemberName='user_to_be_checked'

Which is, in your opinion, the most long term supported method? I mean: will it be more likely for Microsoft to commit design changes to system tables structures or stored procedures syntax/output? And which one is the most portable across SQL Server versions?

Thnx

Raf

user2965433
  • 23
  • 6

3 Answers3

2

Both methods you list are incorrect.

To start with, is always incorrect to check for db_owner membership. The correct check is for CONTROL permission on the database, and the appropriate function to use is HAS_PERM_BY_NAME(). This will save you from the embarrassment of your application refusing to work with an user that has sufficient privileges (CONTROL) because the application uses an incorrect check (role/group membership). This is actually explicitly called out on MSDN:

If the user has the CONTROL DATABASE permission but is not a member of db_owner role, ... will correctly report that the user is not a member of the db_owner role, even though the user has the same permissions.

Finally, if you really need to know role/group membership, the appropriate function is IS_MEMBER()

Remus Rusanu
  • 288,378
  • 40
  • 442
  • 569
  • IS_MEMBER() checks for group, not user. You gave no further info about its long term support/presence. – user2965433 Nov 08 '13 at 18:02
  • For the record: 'long term support' means reading the list of [Deprecated Database Engine Features](http://technet.microsoft.com/en-us/library/ms143729.aspx) and looking if the chosen solution features anything announced for deprecation. – Remus Rusanu Nov 08 '13 at 19:47
0

I would think that the system stored procedure - exec sp_helprolemember - would be the long term supported method.

I looked at the source code for exec sp_helprolemember and internally it has the check your are doing in your 2nd method (not verbatim, but it has other conditions also).

Moreover, using Microsoft provided system stored procedures is the recommended way of getting information unless their existing procedures do not give you all the information you want. In that case, you dig deeper and write your own queries against system tables and functions.

Shiva
  • 20,575
  • 14
  • 82
  • 112
  • Yep, same checks in both solutions, just because I was wondering if it was more safe to rely on tables than procedure. I think Brian got the point so I shouldn't relay on both :-) Thnx! – user2965433 Nov 08 '13 at 18:07
0

I would recommend neither actually.

For the catalog view option, Microsoft says:

In future releases of SQL Server, Microsoft may augment the definition of any system catalog view by adding columns to the end of the column list. We recommend against using the syntax SELECT * FROM sys.catalog_view_name in production code because the number of columns returned might change and break your application.

I believe the same may hold for the system stored procedure.

Instead, I'd suggest using the IS_ROLEMEMBER function. For example:

SELECT IS_ROLEMEMBER('db_owner', 'user1');

It returns 1 if true and 0 if false.

http://technet.microsoft.com/en-us/library/ee677633.aspx

Brian Knight
  • 4,970
  • 28
  • 34
  • IS_ROLEMEMBER is exactly what I was looking for! But you gave no further info about its long term support/presence. – user2965433 Nov 08 '13 at 18:04
  • Unlike the system views and procs, the IS_ROLEMEMBER() function page in Books Online does not offer any caveats as to its durability in the product. I take that as a sign they are planning to maintain it for the unforeseeable future. – Brian Knight Nov 08 '13 at 18:43