Basics of Database Administration in SQL Server: Part 4

SQL Server Users Explained and Exampled

In this part we will try to understand each and every thing related to SQL Server users. So let's move ahead to explore SQL Server users. In case if you missed Part 1 and Part 2 you can check them out from the following links:

• Basics of Database Administration in SQL Server: Part 1
• Basics of Database Administration in SQL Server: Part 2
• Basics of Database Administration in SQL Server: Part 3

I recommend that before reading this article, read Basics of Database Administration in SQL Server: Part 2 first so that you can easily relate the concepts of users and logins.

Introduction

A user is a database level security principal that interact with database objects and only has scope across the database for which it is created. To connect to a specific database on the SQL Server, the login should be mapped with the database user. In addition to it, a login can be mapped to multiple databases but users can only be mapped as one user in each database.

Properties of SQL Server Users

1. A user is a database-level principal.



Figure 1: User is a database-level principal

From Figure 1 it is very clear that "yashwant" is a "user" that belongs to "AdventureWorks2008R2" which is a database. So we can say user is an account specific to the database.

2. A valid user should be associated with a login to work with the database.

Interesting Note: sys.sysusers and sys.syslogin are both linked with a common column called SID (Security Identifier). The following is an example to make that more clear.


Figure 2: Showing how sys.sysusers and sys.syslogins linked together

In the preceding example we have executed three queries together labeled with 1, 2 and 3 in Red color within Black circles.

• The first query output shows that user "yashwant" is linked with "XYZ\yashwant.kumar" login with the SID in a Red rectangular box.
  
  
• The second query simply shows the username and their SID for the "AdventureWorks2008R2" database.
  
  
• The third query shows the loginname and their SID.

Now if we compare all three SIDs in rectangular Red boxes then we will find all SIDs are the same. Hence it is very clear from the preceding example that sys.sysusers and sys.syslogins are linked together with a common column called SID.

3. The Information about users are stored in sys.sysusers, in the database where it's mapped.

4. The scope of a user is the database only that is mapped to the user and the user can only be mapped to one database. If we try to create a new user (either with the same name or another) for the other database with the same login then SQL Server will throw the error 15063.


Figure 3: Showing that one user can map only to one database

• Read the following sentence carefully and try to understand the relation between user and login.

In the preceding example I tried to create a new database user "yashwant" for the "TestDB" database, that is also a database user for the "AdventureWorks2008R2" database and mapped to the "XYZ\yashwant.kumar" login that is a Windows authenticated login. Now in this case SQL Server will throw error 15063, because the database user "yashwant" is already mapped with "XYZ\yashwant.kumar" for the database "Adventureworks2008R2".

5. Multiple users can be associated with one server login in multiple databases.


Figure 4: Illustrating one login can be associated with multiple users in multiple databases.

From Figure 4 we can clearly see that database users "Jim", "Catherine" and "Katie" are associated with the single login "yashrox".

We can execute the following query also if we want to see all the users and logins mapping.

exec sp_msloginmappings [ click here for more on sp_msloginmappings ]

Or:

--Step 1 : Create temp table
CREATE TABLE #tempMappings
(
    LoginName nvarchar(1000),
    DBname nvarchar(1000),
    Username nvarchar(1000),
    Alias nvarchar(1000)
)

--Step 2:Insert the sp_msloginmappings into the temp table

INSERT INTO #tempMappings
EXEC master..sp_msloginmappings --Step 3 : List the results . Filter as required
SELECT
loginname,
username,
DBName
FROM
#tempMappings ORDER BY LoginName

--Step 4: Manage cleanup of temp table

DROP
TABLE #tempMappings

6. We can grant or deny permissions to a user inside a database. For example:

GRANT INSERT, UPDATE, SELECT ON Sales.Customer TO yashwant;

In this query the user "yashwant" is getting permission to insert data and update the table Customer that is in the Sales Schema. (We will explain Schema later in this article).

Property Page of Database User

We can open the property page of a database user using the following procedure:

Step 1: Expand the database.

Step 2: Go to security and expand it.

Step 3: Expand the Users.

Step 4: Right-click on username "yashwant" (in my case).

Step 5: Click on properties.

After clicking on properties you will get the following page with five tabs. 

• General.
• Owned Schemas.
• Membership.
• Securables.
• Extended Properties.

Figure 5: Showing property page of database user "yashwant"

1. General: General tab has five sections in it.

• User type.
• User name.
• Login name.
• Default language.
• Default schema.

2. User type: There are the following five ways by which we can create a user:

• SQL user with login.
• User mapped to a certificate.
• User mapped to an asymmetric key.
• Windows user.
• SQL user without login.

Figure 6: Showing user types

For a), b), c) and d) the user types refer again to the Basics of Database Administration in SQL Server: Part 3.

For e) (SQL Server user without a login) we will explain here so let's move ahead to explore more about it.

SQL User without login:

• A login does not need to exist to create this type of user.
  
  
• The authentication of these types of users happen at the database level.

Use of SQL User without login:

• From SQL 2005 we have the ability to create users without logins. This feature was added to replace application roles.
  
  
• By using SQL users without logins it is easier to move the application to a new instance and limits the connectivity requirements for the function.
  
  
• We can use this type of user in the database using impersonation (allowing one user to act on behalf of another user).

For a better understanding of a user without a login let's do some practical work.

Prerequisites for experiment:

• Login and mapped user.
• User without login.

Step 1

Create a login "ianrox" with the following query in the SSMS of login "XYZ\yashwant.kumar".


Figure 6.1: Create Login

Step 2

Create user "ianrox" in the "Adventureworks2008R2" database in the SSMS of "XYZ\yashwant.kumar".


Figure 6.2: Create User

Step 3

Create a user "Joe_UserWithoutLogin" in the "Adventureworks2008R2" database in the SSMS of "XYZ\yashwant.kumar".


Figure 6.3: User Without Login

Step 4

Connect SQL Server with the loginname "ianrox".


Figure 6.4: Connect SQL Server

Step 5

Run the following query in the SSMS of login "ianrox".


Figure 6.5: Run The following Query

The preceding query is giving an error and it is quite obvious because user "ianrox" doesn't have access to the "AdventureWorks2008R2" DB.

To rectify this problem we will get the benefit of a user without a Login. We have already created the user "Joe_UserWithoutLogin" in Step 3 that doesn't have a login.

Step 6

Here we will grant db_datareader access to "Joe_UserWithoutLogin" in the SSMS of "XYZ\yashwant.kumar" to access the "AdventureWorks2008R2" DB.


Figure 6.6: AdventureWorks2008R2

Step 7

Here we will impersonate the user "Joe_UserWithoutLogin" who already has access to the "AdventureWorks2008R2" DB to login "ianrox" in the SSMS of the login "XYZ\yashwant.kumar".


Figure 6.7: Already Success

Step 8

Now the user "ianrox" should be able to Execute As the user "Joe_UserWithoutLogin" to read the tables from the "AdventureWorks2008R2" database in the SSMS of the login "ianrox".


Figure 6.8: Execute

From the query, now ianrox is able to fetch records from the "AdventureWorks2008R2" database. It is giving 290 rows as a result.

So here it is clear how to provide permissions to "Joe_UserWithoutLogin" and impersonate it in any user to access the database or fetch records. It is also clear we were not able to fetch records from the "AdventureWorks2008R2" database from the SSMS of login "ianrox" that is clearly visible in Step 5 but after impersonating of the user "ianrox" we can fetch records which is very clear in Step 8.

At this stage I am assuming that you are able to understand the importance of the user without login concept.

T-SQL to list users without logins

Execute the following query to list users without logins:

use AdventureWorks2008R2 go
SELECT
    name,
    principal_id,
    type_desc,
    authentication_type_desc,
    sid
FROM
    sys.database_principals
where
    authentication_type_desc = 'none'
    and type_desc = 'sql_user'
    or use AdventureWorks2008R2 go
SELECT
    name,
    principal_id,
    type_desc,
    authentication_type_desc,
sid
FROM
    sys.database_principals
WHERE
    DATALENGTH(sid) > 16
AND sid not in
(
    SELECT
    sid
    FROM
        sys.server_principals
)
AND type = 'S'
AND principal_id > 4 [ Read More ]

Figure 7: T-SQL to determine user without logins

Drawback of the preceding T-SQL

The only drawback is that you must execute the preceding T-SQL for every database but if you are good in coding then this is not a problem for you. This is a problem for me because I am a lazy guy and average in coding.

Problem you can experience

We are talking about permissions and impersonation here. There is a common problem also that you can encounter when deleting a user or login and the following common error message you will see.

“The database principal has granted or denied permissions to objects in the database and cannot be dropped, (Microsoft SQL Server, Error: 15284”).


Figure 8: Showing error 15284, when deleting a user or login

Troubleshooting of problem

Here I am trying to delete the user "UserwithoutLogin" that has impersonate permissions to user "ianrox" and from the error message it is quite obvious we cannot delete it. So what do we do next to solve this problem.? We can solve the problem in the following procedure.

Step 1

Try to determine the permission name and grantee with the following query for the database in which the user exists and you are getting an error.

use AdventureWorks2008R2
go
select * from sys.database_permissions
where
grantor_principal_id = user_id('UserWithoutLogin') GO

Figure 9: Finding out grantee_principal_id using grantor_principal_id and user_id

Step 2

Determine the user name where the impersonate permission is given by the "UserWithoutLogin" user with the following query.

From step 1 we are able to find grantee_principal_id, so using this we will try to determine the user name for that grantee_principal_id using the following query.

SELECT * FROM sys.[database_principals] WHERE [principal_id] = 8
go

Figure 10: Finding out user that have impersonate permission

Step 3

Now we have every detail to rectify our problem. The user "ianrox" has impersonate permission from "UserWithoutLogin".

So here we can revoke permissions from the user "ianrox" using the following query and then we will be able to delete the user "UserWithoutLogin".

REVOKE IMPERSONATE ON User::[UserWithoutLogin] TO [ianrox]

Step 4

You can delete the user by right-clicking on the user name and select the option to delete. The user will be deleted successfully.

Orphaned Users vs User Without Logins

Now I will put some light on another interesting fact, that is Orphaned Users. Some people say that both are the same, I completely disagree with this. According to Mr.Julian Watson, a blogger and owner of the SqlMatters website. In his words, “The users without logins are sometimes confused with orphaned users, however these two types of users are quite different. A user without login is a special type of user that has deliberately been set up without an associated login. In contrast, an orphaned user is one where the user is not currently associated with a login, most commonly because a database has been restored from another server and the association with the login has either been lost or the login does not exist on the new server. Normally when orphaned users are discovered, they are just connected back to their associated logins. However a user without login is one that does not have, and cannot have, an associated login. While this might not sound like a very useful type of user (and indeed in my experience they're not that commonly used) they can be used in conjunction with impersonation from another login. Sometimes they are used as a replacement for application roles”.

Now I completely agree with the preceding statement of Mr. Julian Watson and I assume that everybody reading this article also agrees. Again a huge thanks to Julian Watson for his work, he did a great job in making it very clear in simple words. It makes sense also.

1. User name: user name is the box in which we can provide the name for the database user. In my case the user name is "yashwant".


Figure 11: Illustration of database user name

2. The FOUR by default Database Users: With the creation of every database, whether it is a system database or user database, four types of users are created by default.

• DBO.
• Guest.
• Sys.
• INFORMATION_SCHEMA.

Figure 12: Showing four database users created by default

Database User	Description
DBO	Also known as Database Owner, it has all privileges and rights to do any task in the database. The DBO user also owns the default schema dbo. We cannot drop the DBO user. Note: Members of sysadmin, sa and fixed server role are mapped to dbo.
Guest	The Guest user is disabled by default for security purposes. The Guest user is a member of the public role and has all permissions assigned to that role. We cannot drop the guest user either, we can only enable and disable it.
SYS	The sys user gives other users access to system objects such as system tables, system views, extended Stored Procedures, and other objects that are part of the system catalog. The sys user also cannot be dropped from the database.
INFORMATION_SCHEMA	The INFORMATION_SCHEMA user owns all the information schema views installed in each database. It is used to retrieve the metadata and cannot be dropped.

[ Read More ]

Point to be noted

Guest, sys and INFORMATION_SCHEMA users don't have any logins, we can say these are the users without logins.

We can execute the following query to support this point.

use AdventureWorks2008R2
go
SELECT name,principal_id,type_desc,authentication_type_desc,sid
FROM sys.database_principals
where authentication_type_desc='none' and type_desc='sql_user'

Figure 13: Showing guest, sys, information_schema are users without logins

1. Login Name

It is the place where we enter the login for the user. (Refer to Figure 11).

In Figure 8 "XYZ\yashwant.kumar" is the login name for the database user "yashwant".

2. Default language

We can select the desired language from the drop down list (Refer to Figure 11).

3. Default schema

We can specify the schema by browsing from the list of schemas that will own objects created by the user "yashwant".

In our case the default schema is dbo. (Refer to Figure 11.)

What is schema

A schema is a namespace that exists independently of the user who created it. We can also say that it is a container for objects in the databases. Schemas are the new security feature from SQL Server 2005 onwards.

How to view schema

We can see the schema by navigating to Database >> Security >> Schemas in the Object Explorer of SSMS.

or

We can see a list of schemas by executing the following query.

use TestDB
go
SELECT * FROM sys.schemas
go

Figure 14: Showing list of schemas in TestDB database

Features of schema:

• The owner of any schema is the user who created it. We can check the owner of the schema by right-clicking on the schema name and then clicking on properties.
  
  
• The ownership of a schema can be transferable from one user to another. To do this just right-click on the schema name and click on properties. Under the schema owner you will find a search button. Then we can change the ownership from the given owners.
  
  
• We can move objects from one schema to another schema.

Example

Step 1

Execute the following query.

use AdventureWorks2008R2
go
SELECT name, [schema] = SCHEMA_NAME(schema_id)
FROM sys.tables
WHERE name = 'Employee'
go

Output: Here the "Employee" table is in the "HumanResources" schema as in Figure 15.


Figure 15: Showing default schema for "Employee" table in "Adventureworks2008R2" database

Step 2

In this step we will transfer the "Employee" table in the "HumanResources" schema to the "dbo" schema using the following query.

use AdventureWorks2008R2
go
   ALTER SCHEMA dbo --new schema name where we want to move it
   TRANSFER humanresources.Employee --old schema name with table which we want to move
go

Figure 16: Transferring "Employee" table to "dbo" schema 

• Multiple users can share a single default schema.

Execute the following query to the users and schema associated with them.

use AdventureWorks2008R2
go
   select name, type_desc, default_schema_name from sys.database_principals
go

In my case the user "yashwant" and "Jim" are sharing the same schema, "Human Resources", and that is also clear from the screenshot in Figure 17.


Figure 17: Showing multiple users can share a single default schema 

• One schema can contain objects owned by multiple users.

If multiple users can share a single schema then it is also possible that the objects within that schema are also owned by multiple users.

Owned Schemas

This page lists all the possible schemas that can be owned by the database user. We have already discussed schemas refer heading 3 default schema.

Membership

The Membership page lists all the possible database membership roles that can be owned by the user. Refer to Basics of Database Administration in SQL Server: Part 3 for more information.

Securables

Securables are the resources that we can assign permissions, either at the server level that includes resources like Endpoints, Logins, Server Roles and Databases or at the database level that includes resourcess like Users, Database Roles, Certificates and Schemas. Refer again to the basics of Database Administration in SQL Server: Part 3 more information.

Extended Properties

Extended properties are metadata that allow us to customize the information, storing the data within the database and describe table, procedure, column, function, user and the database itself.

We can create, update, delete and of course view extended properties.

Creating Extended Properties

Using the Stored Procedure "sp_addextendedproperty" we can create extended properties. Here I am creating extended properties for the database version of the database TestMore.

Example

USE [TESTMore]
EXEC sys.sp_addextendedproperty
@name = N'DatabaseVersion',
@value = N'11.0.3000.0'

Figure 18: Creating extended properties

Viewing Extended Properties

We can view extended properties in the following three ways. 

• Using table "sys.extended_properties".
• Using the "fn_listextendedproperty" function.
• Using SSMS GUI.

Using table sys.extended_properties

Using the query in Figure 19 we can view extended properties:

SELECT * FROM sys.extended_properties;

Figure 19: Viewing extended properties

Using the fn_listextendedproperty function

"fn_listextendedproperty" is a builtin function, it returns the Extended Property values of the database object.

SELECT name, value FROM fn_listextendedproperty(default,default,default,default,default,default,default)

Figure 20: Viewing extended properties by fn_listextendedproperty

Using SSMS GUI

Open the Object Explorer and right-click on the database properties.



Figure 21: Viewing extended properties by SSMS GUI

Updating Extended Properties

Using the Stored Procedure "sp_updateextendedproperty" we can update extended properties.

Example

USE [TESTMore]
EXEC sys.sp_updateextendedproperty
@name = N'DatabaseVersion',
@value = N'11.0.3000.1'

Figure 22: Updating extended properties

Deleting Extended Properties

Using the Stored Procedure "sp_dropextendedproperty", we can delete/drop extended properties.

Example

USE [TESTMore]
EXEC sp_dropextendedproperty
@name = N'DatabaseVersion'

Figure 23: Deleting extended properties

Want to learn more about extended properties? The consult the book Transact-SQL User-defined Functions By Andrew Novick.

Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.

References: 

• msdn.microsoft.com
• technet.microsoft.com
• www.sqlmatters.com
• blog.lessthandot.com
• Microsoft SQL Server 2005 Security Best Practices - Operational and Administrative Tasks; SQL Server Technical Article by Bob Beauchemin, SQLskills.com.
• Transact-SQL User-defined Functions By Andrew Novick.
• Microsoft SQL Server 2005 Programming For Dummies By Andrew Watt.
• Mastering Microsoft SQL Server 2005 By Mike Gunderloy, Joseph L. Jorden, David W. Tschanz.
• Beginning SQL Server 2012 for Developers By Robin Dewson.
• Beginning Microsoft SQL Server 2008 Administration By Chris Leiter, Dan Wood, Michael Cierkowski, Albert Boettger.

Conclusion

This is all about SQL Server Users. I tried hard to explain what I know using figures and tried to touch and cover all the things related to SQL Server Users. I hope you will not get bored and will inistead enjoy this. To know more about SQL Server Database Administration click on below links:

• Basics of Database Administration in SQL Server: Part 4
• Basics of Database Administration in SQL Server: Part 5
• Basics of Database Administration in SQL Server: Part 6
  

Happy reading and keep sharing your knowledge.