i want to find the group members.

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » i want to find the group members.

i want to find the group members.

Rate Topic

Display Mode

Topic Options

Author

Message

m.rajesh.uk

Posted Wednesday, February 20, 2013 1:33 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:39 PM
Points: 146, Visits: 362

i am having the code for finding group members when i am executing in ssms 2008 r2 i am getting the errors

Msg 102, Level 15, State 1, Line 23
Incorrect syntax near '177'.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'tempdb.dbo.RESULT_STRING'.

original code from SQL server tackle book.

SET NoCount ON
SET quoted_identifier OFF
DECLARE @groupname VARCHAR(100)
IF EXISTS (SELECT *
FROM tempdb.dbo.sysobjects
WHERE id =
OBJECT_ID(N'[tempdb].[dbo].[RESULT_STRING]'))
DROP TABLE [tempdb].[dbo].[RESULT_STRING];
CREATE TABLE [tempdb].[dbo].[RESULT_STRING]
( Account_Name VARCHAR(2500),
type varchar(10),
Privilege varchar(10),
Mapped_Login_Name varchar(60),
Group_Name varchar(100) )
DECLARE Get_Groups CURSOR
FOR Select
name from master..syslogins
where
isntgroup = 1 and status >= 9 or Name= 'BUILTIN\ADMINISTRATORS'
-- Open cursor and loop through group names
OPEN Get_Groups
FETCH NEXT FROM Get_Groups INTO @groupname
177
WHILE ( @@fetch_status <> -1 )
BEGIN
IF ( @@fetch_status = -2 )
BEGIN
FETCH NEXT FROM Get_Groups INTO @groupname
CONTINUE
END
--Insert SQL Commands Here:
Insert into [tempdb].[dbo].[RESULT_STRING]
Exec master..xp_logininfo @Groupname, 'members'
FETCH NEXT FROM Get_groups INTO @groupname
END
DEALLOCATE Get_Groups
Alter TABLE [tempdb].[dbo].[RESULT_STRING] Add Server
varchar(100) NULL;
GO
Update [tempdb].[dbo].[RESULT_STRING] Set Server =
CONVERT(varchar(100), SERVERPROPERTY('Servername'))
-- Now Query the temp table for users.
SET NoCount OFF
SELECT [Account_Name]
,[type]
,[Privilege]
,[Mapped_Login_Name]
,[Group_Name]
,[Server]
FROM [tempdb].[dbo].[RESULT_STRING]

Post #1421970

Ian Scarlett

Posted Wednesday, February 20, 2013 1:42 AM

Ten Centuries

Group: General Forum Members
Last Login: 2 days ago @ 1:02 AM
Points: 1,322, Visits: 4,404

You've got an erroneous line with '177' half way down the code you posted.

Post #1421979

m.rajesh.uk

Posted Wednesday, February 20, 2013 2:04 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:39 PM
Points: 146, Visits: 362

Thanks i got it

but now i am getting new error..

Msg 15404, Level 16, State 5, Procedure xp_logininfo, Line 42
Could not obtain information about Windows NT group/user 'NT SERVICE\MSSQLSERVER', error code 0x8ac.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Server'.

i know this is due to cross domain ownership. how to resolve this .........

Post #1421986

m.rajesh.uk

Posted Thursday, February 21, 2013 6:39 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 11:39 PM
Points: 146, Visits: 362

plz help...

Post #1422543

Ed Wagner

Posted Thursday, February 21, 2013 7:19 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 2,004, Visits: 720

Do you have permission on the server itself? I know you need the sysadmin server role to run xp_logininfo, but I'm not sure what you need on the server to be able to query active directory.

Post #1422574

Perry Whittle

Posted Thursday, February 21, 2013 7:25 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 5,242, Visits: 11,262

m.rajesh.uk (2/20/2013)

Windows NT group/user 'NT SERVICE\MSSQLSERVER',

It's not a group, its a virtual alias used by the service account in SID based security when using SQL Server 2008 under Windows 2008 and electing to use SID based security during the install. Exclude it from the cursor

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1422577

Ed Wagner

Posted Thursday, February 21, 2013 7:29 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 1:20 PM
Points: 2,004, Visits: 720

Thank you, Perry. I was just getting ready to post that I couldn't do it myself with my permissions.
I just love the wide variety of stuff available for learning on these forums. Thanks again.

Post #1422582

Perry Whittle

Posted Thursday, February 21, 2013 7:33 AM

SSCertifiable

Group: General Forum Members
Last Login: Today @ 10:11 AM
Points: 5,242, Visits: 11,262

Ed Wagner (2/21/2013)

Thank you, Perry. I was just getting ready to post that I couldn't do it myself with my permissions.
I just love the wide variety of stuff available for learning on these forums. Thanks again.

Change the cursor definition to

Select
name from master.sys.server_principals
where name NOT LIKE 'NT SERVICE\%' and
type = 'G' or Name = 'BUILTIN\ADMINISTRATORS'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" Wink

Post #1422585

« Prev Topic | Next Topic »

Permissions