Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

i want to find the group members. Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 1:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 12, 2014 7:14 AM
Points: 189, Visits: 466
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
Posted Wednesday, February 20, 2013 1:42 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, June 16, 2014 7:32 AM
Points: 1,327, Visits: 4,504
You've got an erroneous line with '177' half way down the code you posted.


Post #1421979
Posted Wednesday, February 20, 2013 2:04 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 12, 2014 7:14 AM
Points: 189, Visits: 466
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
Posted Thursday, February 21, 2013 6:39 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Saturday, April 12, 2014 7:14 AM
Points: 189, Visits: 466
plz help...
Post #1422543
Posted Thursday, February 21, 2013 7:19 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 3,971, Visits: 2,979
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.


Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1422574
Posted Thursday, February 21, 2013 7:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 6,182, Visits: 13,329
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"
Post #1422577
Posted Thursday, February 21, 2013 7:29 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 10:39 AM
Points: 3,971, Visits: 2,979
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.



Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions
Post #1422582
Posted Thursday, February 21, 2013 7:33 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 10:15 AM
Points: 6,182, Visits: 13,329
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"
Post #1422585
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse