microsoft sql server: check users own permissions

Question

I have a Microsoft SQL server database and a set of users.

Now in my app I want to make some functionality only visible, if the user has entered username and password with certain rights (admin).

Since the databases and the usernames and their rights can change, how do i check what permissions/rights an Microsoft SQL server user has?

Answer 1

You can check current user's right on certain securables using [sys.fn_mypermissions][1] which returns all permission on the securable. You can also check a specific permission with HAS_PERMS_BY_NAME. Eg. you can check for CONTROL SERVER permission which implies an administrator is logged in:

SELECT HAS_PERMS_BY_NAME(null, null, 'CONTROL SERVER');

Answer 2

The simplest way to do this is using the IS_MEMBER('rolename') function, that checks whether the user is in the role/group 'db_owner'. The function will perform a check at database level, and returns 1 (Int32), if the user has the specified role.

If you need to check at server level, you can use the IS_SRVROLEMEMBER function. Both are available since SQL Server 2005.

Answer 3

I'm not entirely certain that I understand your problem definition however assuming I do.....

I would suggest that you create a SQL Server Database Role that you can add the relevant application users to, perhaps via some group membership maintained within the App (or a Windows Domain Group). You can use the group to Role mapping to independently manage user membership, from managing the relevant permissions to securables within the database via the Role.

This way, you just need to check that an application User is a member of the relevant application or windows group, without the need to query the security configuration of SQL Server.