Restrict sa (System administrator) user to view database structure in sql server

I want to restrict database structure from my software customers.
Is there any way to restrict SA user to view database structure, as sysadmin user can do anything in SQL server

Thanks,
MSBCKumarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rajvihariCommented:
you have to put UserName and new Password in Database/Server instaed of 'sa' to protect you db arch.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Just Remember sa is the system administrator at SQL Server level and can do / view anything within that instance..
If you don't want sa user to be active, then either create one another user with sysadmin privileges granted and disable sa account..
No other go..

PS: Do remember that the new sysadmin account created would be able to view your database structure.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MSBCKumarAuthor Commented:
as per my knowledge - any user member of sysadmin role have all access rights for all database. Is there any way i can revoke rights to access/view database from sysadmin ?
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

RiteshShahCommented:
sysadmin can do anything. all you need to do is, create other login with specific permission and don't share SA password with anybody. no other way to do so
0
lofCommented:
You are right saying that even if you create a new login and add it to sysadmin server role he will effectively have the same permissions as sa user but if you then add restrictions for that new login they will have higher priority then permissions from the role.

Anyway it would be probably good idea to revise your requirement. You are talking about restricting access from your customers. Does it mean you host a database and you give them sysadmin permissions or is it the structure on their servers you don't want them to see?

'If your case is the second option you cannot hide table definitions but you can encrypt views and stored procedures WITH ENCRYPTION but if someone really wants to see it, he will. Here is Microsoft's disclaimer about encrypting stored procedures
Indicates that SQL Server will convert the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. Users that have no access to system tables or database files cannot retrieve the obfuscated text. However, the text will be available to privileged users that can either access system tables over the DAC port or directly access database files. Also, users that can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime.'
0
felipe_schauenburgCommented:
I agree with RiteshShah.
SysAdmin are absolute in a database. If you don't want your client to have full acces, just create another role with specified permissions and allow it to the apllication.
For me makes no sense to create a retricted permission sysadmin role. Apply the a security model for the least privilege policy.

Regards.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.