September 5, 2003 at 11:48 am
Does serveradmin role has permissions/rights sa doesn't have?
Here is the story.
We were struggling to make SAP CRM to work in clustered environment with SQL Server named instance running SQL Server 2000 SP3a. Each time we tried to bring application resource online, we received error message 'SQL Serevr doesn't exist or Access denied'. We checked the SQL server login and grant it with 'sa' privilege and got same error message still. Finally, SAP support called back to tell us we have to grant the login with 'server administrators' role and it really does work! It seems to me 'sa' doesn't have some kind of permissions/rights the 'serveradmin' has.
From BOL.
"Fixed server role Description
sysadmin Performs any activity in SQL Server. The permissions of this role span all of the other fixed server roles.
serveradmin Configures server-wide settings."
Anyone has experienced this before or has better explanations?
Thanks.
September 5, 2003 at 12:48 pm
From SQL BOL
quote:
sysadmin - Performs any activity in SQL Server. The permissions of this role span all of the other fixed server roles.
SA is a memeber of sysadmin fixed role and should not run into any issue doing anything the other fixed roles can do. Not sure why you are seeing differently.
September 5, 2003 at 12:56 pm
Not seen it myself. Even if you do a check on the server role within T-SQL, a sysadmin role user will return as a serveradmin role user. Some undocumented bug?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
September 5, 2003 at 12:57 pm
That is exactly the question I have. Can't explain.
September 5, 2003 at 1:17 pm
quote:
Even if you do a check on the server role within T-SQL, a sysadmin role user will return as a serveradmin role user.
How do you check? I have run EXEC sp_helpsrvrolemember 'serveradmin' and it doesn't return 'sa' as serveradmin member.
September 5, 2003 at 1:18 pm
Could have something to do with clustering that I have not seen. But alas no cluster to test on.
September 5, 2003 at 1:35 pm
Try:
SELECT is_srvrolemember('sysadmin')
A sysadmin account should return with a 1.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply