strange issue with is_member
-
Hi all,
I'm running into a bit of an issue with the is_member function in SQL Server 2000.
I've created a user account, and a database role ('fcc_admin'). I've added the user to the role, but when I log in to Query Analyzer as the user, and run 'print is_member('fcc_admin')', it returns 0.
Has anyone else encountered a similar issue? If so how did you resolve it?
thanks,
Marcus
-
keep in mind you need to be connected with that user and to the correct database.
Overhere it works fine (sql2000 sp4 + CU(2187))
Did you also try sp_helprolemember @rolename = 'testrole'
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Hi,
I tried using sp_helprolemember, and it shows my user id as being in the role, however is_member is still not working.
-
looks like the image isn't showing so here is what I see in query analyzer (I am logged in as user malinowm2)
query pane :
select is_member('fcc_admin') as [is_member]
go
sp_helprolemember @rolename = 'fcc_admin'
results pane:
is_member
-----------
0
(1 row(s) affected)
DbRole MemberName MemberSID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fcc_admin batch 0x0713C0CF09356442A11F4CF936847CDC
fcc_admin dtjiook 0xB068EC1003FE8F409C6CA11C9BE505B3
fcc_admin hgodfrey 0xA8FD904BECE16A46BBD7EEB4E8EA9491
fcc_admin hthandi 0x08E17F9952D7F94DA3877E662ACC9CE2
fcc_admin malinowm1 0x63D5B843BB48E243A8B43C19FE6F9987
fcc_admin malinowm2 0x5CE33E5968DCA740A07FCCE7B5655E93
(6 row(s) affected)
-
can you execute the same script preceded by :
select user_id() as UID
, user_name() as Uname
select SUSER_SID ( ) SUSID
, suser_sname() as SUname
go
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ok, so i ran these commands:
select user_id() as UID, user_name() as Uname
select suser_sname() as SUname
go
select is_member('fcc_admin') as [is_member]
go
sp_helprolemember @rolename = 'fcc_admin'
and this is what i got in my results pane:
UID Uname
------ --------------------------------------------------------------------------------------------------------------------------------
1 dbo
(1 row(s) affected)
SUname
--------------------------------------------------------------------------------------------------------------------------------
malinowm2
(1 row(s) affected)
is_member
-----------
0
(1 row(s) affected)
DbRole MemberName MemberSID
-------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
fcc_admin batch 0x0713C0CF09356442A11F4CF936847CDC
fcc_admin dtjiook 0xB068EC1003FE8F409C6CA11C9BE505B3
fcc_admin hgodfrey 0xA8FD904BECE16A46BBD7EEB4E8EA9491
fcc_admin hthandi 0x08E17F9952D7F94DA3877E662ACC9CE2
fcc_admin malinowm1 0x63D5B843BB48E243A8B43C19FE6F9987
fcc_admin malinowm2 0x5CE33E5968DCA740A07FCCE7B5655E93
(6 row(s) affected)
Should I be seeing 'malinowm2' when querying user_name()?
-
UID Uname
------ ------
1 dbo
means your current user _is_ the database owner !
(if he were member of db_owner, it would still show the actual username)
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
ok, i've tried this out with a couple of other logins, both of whom are members of the fcc_admin group.
I also get the same results for the 'select user_id() as UID, user_name() as Uname' query. Looking in enterprise manager, all the user id's I've tried are mapped to login names which are the same as their name. e.g. malinowm2 in my database is mapped to a sql id called malinowm2.
To be honest my understanding of sql security is limited, but it looks like all these users are the database owner?
-
can you also try :
select user_id('malinowm2')
?
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Yeah that returns 13 as my user_id.
I've tried deleting the user and recreating through enterprise manager, assigning to roles etc., same result though.
Thanks for your help with this by the way!
-
.. Thanks for your help with this by the way! ..
That's the strength of forums like SSC
Like you do, I find your results strange
Can you test this scenario ?
/****** Object: Database test Script Date: 04/09/2007 16:10:46 ******/
CREATE DATABASE [test]
GO
use [test]
GO
/****** Object: Login test Script Date: 04/09/2007 16:10:46 ******/
if not exists (select * from master.dbo.syslogins where loginname = N'test')
BEGIN
declare @logindb nvarchar(132), @loginlang nvarchar(132) select @logindb = N'test', @loginlang = N'us_english'
if @logindb is null or not exists (select * from master.dbo.sysdatabases where name = @logindb)
select @logindb = N'master'
if @loginlang is null or (not exists (select * from master.dbo.syslanguages where name = @loginlang) and @loginlang <> N'us_english')
select @loginlang = @@language
exec sp_addlogin N'test', 'verystrongpassword', @logindb, @loginlang
END
GO
/****** Object: User test Script Date: 04/09/2007 16:10:46 ******/
if not exists (select * from dbo.sysusers where name = N'test')
EXEC sp_grantdbaccess N'test', N'test'
GO
/****** Object: Table [dbo].[t_test] Script Date: 04/09/2007 16:10:47 ******/
CREATE TABLE [dbo].[t_test] (
[test] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
/*
-- to be executed in a new connection using SQLUser test with its password
select user_id() as UID
, user_name() as Uname
select user_id('test')
select user_name(user_id('test'))
This gives me :
UID Uname
------ ---------
5 test
(1 row(s) affected)
------
5
(1 row(s) affected)
----
test
(1 row(s) affected)
*/
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
I tried that out and got slightly different results to you. I used a windows account, and also a sql account (malinowm2). My results were the same for both users and were:
The CREATE DATABASE process is allocating 0.63 MB on disk 'test'.
The CREATE DATABASE process is allocating 0.49 MB on disk 'test_log'.
Granted database access to 'test'.
UID Uname
------ --------------------------------------------------------------------------------------------------------------------------------
1 dbo
(1 row(s) affected)
------
5
(1 row(s) affected)
--------------------------------------------------------------------------------------------------------------------------------
test
(1 row(s) affected)
-
Does the userid 'malinowm2' have database owner permissions on the database? This could be automatically mapping you to 'dbo'.
Steve G.
-
>>Does the userid 'malinowm2' have database owner permissions on the database? This could be automatically mapping you to 'dbo'.
No, the user has membership of several database roles, 'public', 'fcc_admin', and a couple more user defined roles. It also has membership of the 'system administrators' and 'security administrators' server roles.
-
Steve,
thanks for your post. I think it's helped me get a bit closer to finding the source of this problem.
I tried out removing 'malinowm2' from the system administrators group, and all of a sudden is_member('fcc_admin') is working. This is unfortunate as I really wanted the user to be a sysadmin so that I could use the SETUSER command so I could run jobs as another user.
If I recall correctly there's another way of getting the necessary permissions to run SETUSER so I'll try that out.
Marcus
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply