February 20, 2013 at 1:33 am
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]
February 20, 2013 at 1:42 am
You've got an erroneous line with '177' half way down the code you posted.
February 20, 2013 at 2:04 am
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 .........
February 21, 2013 at 6:39 am
plz help...
February 21, 2013 at 7:19 am
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.
February 21, 2013 at 7:25 am
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" 😉
February 21, 2013 at 7:29 am
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.
February 21, 2013 at 7:33 am
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" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply