I've got a microsoft sql server database and some customers. Now during my application i wish to have functionality only visible when the user has joined account information with certain privileges (admin). Because the databases and also the usernames as well as their privileges can alter, how do you check what permissions/privileges an microsoft sql server user has?
You should check current user's directly on certain securables using
[sys.fn_mypermissions] which returns all permission around the securable. You may also check a particular permission with [cde]. Eg. you should check for
HAS_PERMS_BY_NAME permission which suggests webmaster is drenched in:
I am not entirely sure that I realize your condition definition however presuming I actually do.....
I recommend that you simply produce a SQL Server Database Role that you could add the appropriate application customers to, possibly via some group membership maintained inside the Application (or perhaps a Home windows Domain Group). You should use the audience to Role mapping to individually manage user membership, from controlling the appropriate permissions to securables inside the database through the Role.
By doing this, you need to simply make sure that a credit card applicatoin User is part of the appropriate application or home windows group, with no need to query the safety configuration of SQL Server.
The easiest method of doing this really is while using IS_MEMBER function, that inspections wether the consumer is incorporated in the role/group db_owner. The function will work a cheque at database level. If you want to check at server leve, you should use the IS_SRVROLEMEMBER function. Both of them are available since sql server 2005.