[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1525
  • Last Modified:

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

1 Solution
you have to put UserName and new Password in Database/Server instaed of 'sa' to protect you db arch.
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
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 ?
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

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
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.'
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.


Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now