Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


i want to find the group members.


i want to find the group members.

Author
Message
m.rajesh.uk
m.rajesh.uk
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 589
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]
Ian Scarlett
Ian Scarlett
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1421 Visits: 6278
You've got an erroneous line with '177' half way down the code you posted.



m.rajesh.uk
m.rajesh.uk
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 589
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 .........
m.rajesh.uk
m.rajesh.uk
SSC Veteran
SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)SSC Veteran (213 reputation)

Group: General Forum Members
Points: 213 Visits: 589
plz help...
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10285 Visits: 9584
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
Ed Wagner
Ed Wagner
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10285 Visits: 9584
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
Perry Whittle
Perry Whittle
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search