Solved

check database role

Posted on 2010-09-17
4
1,131 Views
Last Modified: 2012-05-10
like IS_SRVROLEMEMBER helped to verify a server role, is there an equivalent for database role?

thanks
0
Comment
Question by:anushahanna
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:BrandonGalderisi
ID: 33702034

SELECT is_rolemember('ROLENAME','USERNAME')


ex.
SELECT is_rolemember('db_owner','USERNAME')
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33702110
Brandon

is my format any issue..

all of these are returning NULLs

use tracker
SELECT is_rolemember('db_owner','USERNAME') -- your example
SELECT is_rolemember('db_owner','SRBSV\jimw') -- Domain User Group
SELECT is_rolemember('db_owner','tracker_Dev_Users') --SQL Login
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 33702217
Is USERNAME a user in the SQL database?
Is SRBSV\jimw a user in the SQL database?
Is tracker_DEV_users a user in the database?

This script will show you the full mapping in each DB.

SELECT su.name,sr.name, is_rolemember(sr.name,su.name) 'is_RoleMember'
FROM sysusers su
  CROSS JOIN sysusers sr
WHERE su.islogin=1
  AND sr.issqlrole=1
0
 
LVL 6

Author Comment

by:anushahanna
ID: 33702443
got it.

thanks Brandon.
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query execution hang 5 32
VBScript Write Column Headers 3 38
Update a text value in another table 10 42
SQL Server Error 21 8 26
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how the fundamental information of how to create a table.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question