How can I get Role for Current User from SQL Server using VB6 ADO connection 
Author Message
 How can I get Role for Current User from SQL Server using VB6 ADO connection

Hello.

I would like to know if there is a way I can get the role(s) for the user
logging onto SQL Server 2000 from an ADO 2.5 connection within Visual Basic
6 Service Pack 5.  The user is logging in using Windows security (ie, not
SQL security).

Can someone please let me know the best way of doing this ?  I'm a complete
newbie when it comes to SQL and security.

Your help is greatly appreciated.

Thanks.



Sun, 13 Feb 2005 14:27:40 GMT  
 How can I get Role for Current User from SQL Server using VB6 ADO connection
There are system stored procedures to get Role information.  One is called
sp_HelpUser which we use quite successfully.   Others are sp_HelpRole and
sp_HelpRoleMember.

Bruce



Quote:
> Hello.

> I would like to know if there is a way I can get the role(s) for the user
> logging onto SQL Server 2000 from an ADO 2.5 connection within Visual
Basic
> 6 Service Pack 5.  The user is logging in using Windows security (ie, not
> SQL security).

> Can someone please let me know the best way of doing this ?  I'm a
complete
> newbie when it comes to SQL and security.

> Your help is greatly appreciated.

> Thanks.



Sun, 13 Feb 2005 14:53:28 GMT  
 How can I get Role for Current User from SQL Server using VB6 ADO connection
Thank you very much Bruce.

Looking at these, I also found 'IS_MEMBER' which would return a 1 or 0 if
the current user is a member of a group
eg,

rs.Source = "SELECT IS_MEMBER ('PlantOpsAdmin')"

This means that I don't actually need to get the list of roles from SQL
Server, this command does what I was planning to do in code :)

I really had a hard time testing this using Enterprise Manager.  I have a
local copy installed on my computer, and I added my login name to the role.
However, IS_MEMBER kept on returning 0.  Eventually, I found that it was
checking for dba, not for my login name.  Each time I logged in with my
login name, it would log me in as dba.  Eventaully, after a bit of clicking,
breaking and looking, I got it to log me in as the correct login name.

Anyway, thanks for your help.

Andrew.


Quote:
> There are system stored procedures to get Role information.  One is called
> sp_HelpUser which we use quite successfully.   Others are sp_HelpRole and
> sp_HelpRoleMember.

> Bruce



> > Hello.

> > I would like to know if there is a way I can get the role(s) for the
user
> > logging onto SQL Server 2000 from an ADO 2.5 connection within Visual
> Basic
> > 6 Service Pack 5.  The user is logging in using Windows security (ie,
not
> > SQL security).

> > Can someone please let me know the best way of doing this ?  I'm a
> complete
> > newbie when it comes to SQL and security.

> > Your help is greatly appreciated.

> > Thanks.



Sun, 13 Feb 2005 15:47:40 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Getting SQL Sever User Role Information

2. SQL Server Application roles and connections

3. SQL server application role and VB connection

4. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL

5. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

6. VB6: Oracle 8/SQL Server 2000 compatibility problems using ADO 2.5 and SQL Syntax

7. Getting SQL Server 7.0 users with ADOX in VB6

8. Getting the User Group name using Current User Function

9. ADO & Application Roles on SQL Server 7

10. adding a user DSN connection to MS SQL server 2000 using AD GPO logon scripts

11. SQL Server Windows Authentication : How to connect a different user then the current Windows login user

12. SQL Server Windows Authentication : How to connect a different user then the current Windows login user

 

 
Powered by phpBB® Forum Software