May 28, 2012 at 5:41 am
Dear Sir,
I have MS SQL 2008 R2 installed on Windows 2008 R2 EE.
Issue is our old application runs on SQL 2005 with out sysadmin rights which we select on SERVER ROLES.
Now when have converted our DB on SQL 2008 r2 with all same settings we need to enable SYSADMIN rights in server roles but our client has policy that they will not enable SYSADMIN rigths.
What are the difference between SYSADMIN rights in SQL 2005 and SQL 2008.
Thanks
Adeel Imtiaz
May 28, 2012 at 6:31 am
As per BOL: "Members of the sysadmin fixed server role can perform any activity in the server."
In either 2005 or 2008, sysadmin is still God rights where members can do absolutely anything. And by default, they are mapped to the dbo database user for all databases.
May 28, 2012 at 6:35 am
One other thing is that local administrators of the server are in the sysadmin role by default in SQL 2005 they are not in SQL 2008
May 28, 2012 at 6:55 am
SQLDBA360 (5/28/2012)
One other thing is that local administrators of the server are in the sysadmin role by default in SQL 2005 they are not in SQL 2008
Good point. Also, Domain Admins as they are typically part of the local admin group.
May 28, 2012 at 8:33 am
It could likely be that your 'user/login' just requires dbo rights to a database. You will probably find that as a domain admin or local admin under to ss2005, you did have sysadmin (as has been suggested).
First start by giving you user read/write access to the database, if that doesnt work scale to dbo. You may find that read/write + execute does the trick.
Do you carry out any server/maint/disk operations? As these would require different types ofr access.
HTH
Adam Zacks-------------------------------------------Be Nice, Or Leave
May 28, 2012 at 8:42 am
What operation do u plan to perform using sysadmin rights?
Do you have a NT Login or SQL account.
What is the reason the client said sysadmin is not allowed.
Admins are the same regardless of the version of SQL ( expection being they are not added by default in SQL 2008)
Please do not confuse the windows admin with the sql admin they are not the same ( but can be the same account).
Look up principle of least privileges.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply