August 21, 2007 at 7:18 am
I am currently in the process of creating a configuration database and I am having trouble with another area. I am trying to get the server roles from SQL Server 2000/2005 instances and have hit a block. Here is the code I am currently using...
DECLARE @server [nchar] (100)
SET @server = (CONVERT(char(100), (SELECT SERVERPROPERTY('Servername'))))
INSERT INTO [tempdb].[dbo].[Server_Roles] (Server, ServerRole, UserName)
SELECT
role.name,
member.name
FROM
sys.server_role_members rm
inner join
sys.server_principals member
ON
rm.member_principal_id = member.principal_id
inner join
sys.server_principals role
ON
rm.role_principal_id = role.principal_id
However, the sys.* views don't exist in 2000. Does anybody have a way to tweak this query to run on both 2000 and 2005 servers through BI packages? If not, does anybody have some code to get the server roles? Thanks for any help.
August 21, 2007 at 9:01 am
You could try using the results of SP_HELPSRVROLEMEMBER which returns the same thing in both SQL 2000 and SQL 2005.
Greg
Greg
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply