June 16, 2009 at 2:38 pm
We had all SQL 2000 servers. I needed to get some information including the roles from each server. My select was something like this:
SELECT :ls_db_name, name
FROM dbo.sysusers
WHERE issqlrole = 1
and gid > 0
and name not like 'db_%';
We are now using some SQL 2005 servers and it is my understanding that for SQL 2005, I can no longer select from sysusers with any consistency.
Does anyone know how I can get the roles off of a DB regardless if it is 2000 or 2005?
June 16, 2009 at 3:03 pm
The recommended way is to query the catalog view sys.database_principals because sysusers may go away in future releases. I get the same results querying the view or sysusers.
Greg
June 17, 2009 at 1:44 pm
On a 2005 DB I get rows when I perform:
SELECT * FROM sys.database_principals
On a 2000 DB I get:
Invalid object name 'sys.database_principals'.
Under the scenario I am using, I cannot log in as 'sa'.
June 17, 2009 at 2:22 pm
sys.database_principals is a system view that was introduced in SQL Server 2005, it does not exist in 2000. dbo.sysusers view is still available in 2005 (and 2008 for that matter) but it may not be in future versions.
June 17, 2009 at 2:25 pm
That's my problem - from where I'm running my query, I don't know if it is a 2000 or 2005 version. The select @@version gives too much information to accurately determine if it is 2000 or 2005.
June 17, 2009 at 2:52 pm
You could just use sysusers view in both 2000 and 2005 but if you did want to use sys.database_principals when querying 2005 you can use something like this:
declare @version varchar(20)
select @version = convert(varchar(20),SERVERPROPERTY('ProductVersion'))
if @version like '9%'
begin
select * from sys.database_principals
end
else
begin
select * from dbo.sysusers
end
June 17, 2009 at 4:01 pm
I'll give that a try, So far looks good. Thanks for helping me out.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply