Exploring a SQL Server in SSMS

It is useful to be taught how to fish, in the sense of exploring how a SQL server is structured. This is a guide around giving the tools to discover it for yourself, especially in the situation of being presented a new environment (assuming there is not someone to explain it to you directly).

This assumes the structure is set up as in https://learn.microsoft.com/en-us/ssms/tutorials/ssms-configuration

Access Control

Access to database objects is often granted via Users. Within a database, you can find the list of users in the subfolder Security > Users

Given a user, role, or database object, you can find full access information within the Properties menu by right clicking. To discover this, do the below:

1. Starting from User
   1. Right click > Properties
   2. To see which role(s) have been assigned to this user, navigate to the Membership tab of Properties
      1. The assigned roles will be ticked. Make a note of the role name.
   3. To see any direct access given to the user (not via a role), navigate tot he Securables tab.
      1. Here you will find the Name of the database object and which schema it is from. Further down, you can see what kind of permissions have been granted, e.g. SELECT, INSERT, etc.
   4. To see Owned Schemas, navigate to the Owned Schemas tab
      1. Owned schemas will be ticked
2. Starting from Role
   1. Right click > Properties
   2. To see which user(s) have been assigned this role and schemas owned by this role, navigate tot he General tab of Properties
      1. The assigned users and schemas will be listed
   3. To see which database objects the role has access to along with what kind of permissions it has, navigate to the Securables tab.
      1. Here you will find the Name of the database object and which schema it is from. Further down, you can see what kind of permissions have been granted, e.g. SELECT, INSERT, etc.
3. Starting from Database Object (e.g. table, view)
   1. Right click > Properties
   2. To see which users or roles have access to the database object, and what kind of permissions that user or role has, navigate to the Properties tab

Summary

All permissions information, with how they link together, can be found within the right click properties menu.

Roles can be updated via queries such as

GRANT CREATE TABLE to Joe as dbo

For documentation, see GRANT documentation – there is a table of Arguments.

Object Definitions and Information

To get the definition (query) of an object, you can use the following query, replacing the object ID with your desired object:

SELECT
OBJECT_DEFINITION(OBJECT_ID('
[path].[to].[object]
')) AS ObDefinition
;

Making sure to change the object path with your object's full location. You can get this object ID easily via right click > View Dependencies, and Name at the bottom shows the object ID – make sure to not include the server name itself.

Then you can copy the value of the output of the query and paste it into a text editor to view the query or definition. Note that this works well on stored procedures and views since they are created with queries directly, but for tables there is often no result if they are created as the output of a stored procedure. Note that the white-space of queries are often mangled in the output query, so this might require some parsing to be readable.

Alternatively, you can find the definition for a View or Table via right click > Select Top 1000 Rows, and copy the FROM part. For a Stored Procedure, you can find this via right click > Modify, and copy from ALTER PROCEDURE.