| Author |
Topic |
|
chip
Starting Member
14 Posts |
 Posted - 2009-04-16 : 20:18:21
|
| I have several roles like Supervisor, Clerk, Manager setup under my database. I have created a login and put them in the Supervisor role at the Security level. However, if I login as that user and run a program that checks IS_MEMBER('Supervisor') I get a zero returned (therefore the code does not recognize that the user is a member of that role). If I run sp_helprolemember 'Supervisor' the user shows up in the list. Any idea what I have done wrong?Chip DicksonTexas |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chip
Starting Member
14 Posts |
Posted - 2009-04-17 : 08:27:43
|
| This SELECT returns a zero indicating that the logged on user is not in that Role? SELECT is_member('Supervisor')The following stored procedure list the objects(?) in the role which shows all objects in the role? EXEC sp_helprolemember 'Supervisor'Chip DicksonTexas |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
chip
Starting Member
14 Posts |
Posted - 2009-04-17 : 15:03:40
|
| Returned from select @@version:Microsoft SQL Server 2005 - 9.00.3282.00 (X64) Aug 5 2008 00:48:00 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2) I found several forums concerned/having problems with this issue also. Seems to be some issue with the user being a member of dbo or not a member of dbo, didn't quite understand which. We have a temporary work around using this:select Count(*) from sysusers where (issqlrole = 1) and (name = 'Supervisor')which of course returns us a count of 1 or more if the present user is in that particular role, but is not quite as clean as the IS_MEMBER.Chip DicksonTexas |
|
|
|
chip
Starting Member
14 Posts |
Posted - 2009-04-20 : 14:15:17
|
| Well, the workaround that I was given does not give me what I need. That select brings back all the possible roles, not the possible roles for the logged on user. So, I still have a delima.Chip DicksonTexas |
|
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-04-20 : 14:43:15
|
| I think members are users and every user has to be part of a role. Begin CREATE USER ? FOR LOGIN ? WITH DEFAULT_SCHEMA=[dbo]EndBegin exec sp_addrolemember @rolename = 'db_owner',@membername = '?'End |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
barretld
Starting Member
22 Posts |
Posted - 2009-04-20 : 15:12:18
|
| difficult to say without seeing it....but every role you create, like Supervisor has to be a part of an already built in role. ONce you create the role, go to properties, then securables, and add some stuff....maybe that will help. db_accessadmindb_backupoperatordb_datareaderdb_datawriterdb_ddladmindb_denydatareaderdb_denydatawriterdb_ownerdb_securityadminpublic |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
|
Role membership issue