I use Microsoft SQL Server Management Studio quite frequently in the course of my work. Most of the time, however, I’m logged in as a user who has broad privileges on the instance I’m managing.
Recently, however, I’ve been managing an internal production application that is hosted by IT. Since this application’s database resides on a SQL Server instance that contains multiple production databases, the SQL Server user that I log in as has pretty limited permissions, relating only to the one database that I need to manage.
My assumption is that if my user does not have permission to perform a function in the SQL Server Management application, the user’s access to that function will be limited–UI components that give the user access to said function will be absent or disabled.
The case in point today was creating other SQL Server users. I assumed that my user would not be able to create other users, but I was surprised to find that I could navigate to the Security/Logins node in the Object Explorer and that the ‘New Login…’ command was presented on the context menu.
Being a tester, I just had to try to create a new user–if for no other reason, to ensure that IT gave this user only the necessary permissions. So, I selected the ‘New Login…’ menu item and lo and behold, the ‘New Login’ dialog appeared. I filled out all the necessary information and submitted the form.
Only when I submitted the New Login form did I get an error message informing me that my user does not have permissions to create another user.
Since most actions in the application result in SQL queries to the database(s), I can imagine Microsoft’s reasoning is as follows: permissions are built into the database; let the database do its work; why duplicate database functionality in the UI? From a user experience standpoint, however, this does not make for the most user-friendly application.