CODEWITHSUNDEEP
sqlsql-server
scigor
scigor

Reputation: 1618

microsoft sql server: check users own permissions

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?

Upvotes: 7

Views: 9067

Answers (3)

jb_
jb_

Reputation: 958

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.

Upvotes: 2

Remus Rusanu
Remus Rusanu

Reputation: 294177

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');

Upvotes: 7

John Sansom
John Sansom

Reputation: 41819

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.

Upvotes: 1

Related Questions