SQL Server Users Elaborated

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

SQL Server Users Elaborated
In this part we will try to understand each & everything related to SQL Server users so let’s move ahead to explore SQL Server users. 
I will recommend before reading to this article, read SQL Server Logins Elaborated first so that you can easily relate the concepts between users & logins.

Introduction:
A user is a database level security principal which interact with database objects and have scope across the database only for which it is created. To connect to a specific database on the SQL Server, login should be mapped with 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) User is a database level principal.
                                            Figure1: User is a database level principal
 
From figure1 it is very clear that ‘yashwant’ is a ‘user’ which belongs to ‘AdventureWorks2008R2’ which is a database. So we can say user is an account that is specific to database.
 
2) A valid user should be associated with login to work with database.
Interesting Note: sys.sysusers and sys.syslogin both are linked with a common column called SID (Security Identifier). Below is an example to make this sentence more clear:
   Figure2: Showing how sys.sysusers and sys.syslogins linked together
 
In above example we have executed three queries together labeled with 1, 2 and 3 in red color within black circles.
 

• First query output showing that user ‘yashwant’ linked with ‘XYZ\yashwant.kumar’ login with SID which is in red rectangular box.

 

• Second query simply showing username & their sid for ‘AdventureWorks2008R2’ database.

 

• Third query showing loginname & their sid.

 
Now if we compare all three SIDs which are in rectangular red box then we will find all SIDs are same. Hence it is very clear from the above example that sys.sysusers and sys.syslogins are linked together with a common column called SID.
 
3) Information about users stored in sys.sysusers, in the database where it's mapped.
4) Scope of user is the database only that is mapped to user, and user can be map only to one database. If we try to create new user (either with same name or other) for the other database with same login then SQL Server will throw error 15063.
   Figure3: Showing that one user can map only to one database
 
Read below sentence carefully and try to understand the relation between user and login:
 
In the above example I tried to create a new database user ‘yashwant’ for ‘TestDB’ database, which is also a database user for ‘AdventureWorks2008R2’ database and mapped to ‘XYZ\yashwant.kumar’ login which is Windows authenticated login. Now in this case SQL Server will throw error 15063, because database user ‘yashwant’ already mapped with ‘XYZ\yashwant.kumar’ for database ‘Adventureworks2008R2’.
 
5) Multiple users can be associated with one server login in different databases.
 Figure4: Illustrating one login can be associated with multiple users in different databases.

From figure4 we can clearly see that database user ‘Jim’, ‘Catherine’ and ‘Katie’ are associated with single login ‘yashrox’.
we can execute below query also if we want to see all users & logins mapping:
 

exec sp_msloginmappings

Select all Open in new window

 [] 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

Select all Open in new window

6) We can grant or deny permissions to user inside database. e.g.
 
GRANT INSERT, UPDATE, SELECT ON Sales.Customer TO yashwant;
 
In this query user ‘yashwant’ is getting permission of inserting data & updating the table Customer which is in Sales Schema. [We will discuss Schema later in this article]
 
Property Page of Database User:
We can open property page of database user using below steps:
Step1: Expand the database
Step2: Go to security and expand it
Step3: Expand the Users
Step4: Right click on username ‘yashwant’ [in my case]
Step5: Click on properties
 
After clicking on properties option you will get below page with five tabs:
1) General
2) Owned Schemas
3) Membership
4) Securables
5) Extended Properties
  Figure5: Showing property page of database user ‘yashwant’
 
1) General: General tab has FIVE sections in it:
1.1 User type
1.2 User name
1.3 Login name
1.4 Default language
1.5 Default schema
 
1.1 User type: There are below five ways by which we can create a user:
a) SQL user with login
b) User mapped to a certificate
c) User mapped to an asymmetric key
d) Windows user
e) SQL user without login
  Figure6: Showing user types
 
For a), b), c), d) user types refer again SQL Server Logins Elaborated
For e) SQL Server user without login we will discuss here so let’s move ahead to explore more about it:
 
SQL User without login:

• Login does not need to exist to for this type of user.
• The authentication of these types of users happens 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 through impersonation (allowing one user to act on behalf of another user).

 
For better understanding of User without login let’s perform some practical work:
Prerequisites for experiment:
1) Login and mapped user
2) User without login
 
Step1: Create login ‘ianrox’ with below query in ssms of login ‘XYZ\yashwant.kumar’
 
Step2: Create user ‘ianrox’ in ‘Adventureworks2008R2’ database in ssms of ‘XYZ\yashwant.kumar’
 Step3: Create a user ‘Joe_UserWithoutLogin’ in ‘Adventureworks2008R2’ database in ssms of ‘XYZ\yashwant.kumar’
 Step4: Connect SQL Server with loginname ‘ianrox’.
Step5: : Run below query in ssms of login ‘ianrox’:
The above query is giving an error and it is quite obvious because user ‘ianrox’ don’t have access on ‘AdventureWorks2008R2’ DB.
 
To rectify this problem we are going to take help of User without Login concept. We have already created a user ‘Joe_UserWithoutLogin’ in step3 which don’t have any login.
 
Step6: Here we will grant db_datareader access to ‘Joe_UserWithoutLogin’ in ssms of ‘XYZ\yashwant.kumar’ to access ‘AdventureWorks2008R2’ DB.
 Step7: Here we will impersonate user ‘Joe_UserWithoutLogin’ who already has access to ‘AdventureWorks2008R2’ DB to login ‘ianrox’ in ssms of login ‘XYZ\yashwant.kumar’.
 
Step8: Now user ‘ianrox’ should be able to Execute As user ‘Joe_UserWithoutLogin’ to read the tables from ‘AdventureWorks2008R2’ database in ssms of login ‘ianrox’.
 From the query now we ianrox is able to fetch records from ‘AdventureWorks2008R2’ database. It is giving 290 rows as a result.
 
So here it is clear how we can give permissions to ‘Joe_UserWithoutLogin’ and impersonate it in any user to access database or fetch records. It is also clear we were not able to fetch records from ‘AdventureWorks2008R2’ database from ssms of login ‘ianrox’ which is clearly visible in step5 but after impersonating it to user ‘ianrox’ we can fetch records which is very clear in step8.
 
At this stage I am assuming that you are able to understand the importance of User without login concept.
 
T-SQL to list out user without logins:
Execute below query to list user 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'
                      go

Select all Open in new window

 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

Select all Open in new window

 [] Read More ]
  Figure7: t-sql to find out user without logins
 
Drawback of the above t-sql:
The only drawback is that you have to execute the above t-sql for every database but if you are good in coding then this is not a problem for you; this is problem for me because I am lazy guy and average in coding :(
 
Problem you can face:
We are talking about permissions, impersonation here. There is a common problem also which you can face when deleting a user or login and the below 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”).
 Figure8: Showing error 15284, when deleting a user or login
  
Troubleshooting of problem:
Here I am trying to delete user ‘UserwithoutLogin’ which have impersonate permissions to user ‘ianrox’ and from the error message it is quite obvious we cannot delete it. So what we do next to solve this problem. We can solve problem in below mentioned steps:
 
Step1: Try to find out permission name, grantee with below query for the database in which the user exist & you are getting error:
 

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

Select all Open in new window

 Figure9: Finding out grantee_principal_id using grantor_principal_id and user_id
 
Step2: Find out user name where impersonate permission given by ‘UserWithoutLogin’ user with below query:
From step1 we are able to find grantee_principal_id, so using this we will try to find out user name for that grantee_principal_id using below query:
 

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

Select all Open in new window

 Figure10: Finding out user which have impersonate permission
 
Step3: Now we have every detail to rectify our problem. User ‘ianrox’ have impersonate permission from ‘UserWithoutLogin’.
 
So here we can revoke permissions from user ‘ianrox’ using below query and after that we will be able to delete user ‘UserWithoutLogin’:
 

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

Select all Open in new window

 
Step4: You can delete user by right click on user name and select option delete. User will delete successfully.
[] Read More ]
 
Orphaned Users vs User Without Logins:
Now I am going to put some light on another interesting fact that is Orphaned Users. Some people say that both are same, I completely disagree with this. According to Mr.Julian Watson a blogger, owner of 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 above said 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 great job to make it very clear in simple words. It makes sense also. [] Read more ]
 
1.2 User name: user name is the box in which we can give the name for database user. In my case the user name is ‘yashwant’.
  Figure11: Illustration of database user name
 
1.2.1 FOUR By Default Database Users:
With the creation of every database whether it is system database or user database FOUR types of users created by default:
a) DBO
b) Guest
c) Sys
d) INFORMATION_SCHEMA
             Figure12: Showing FOUR database users created by default

Default Database User Description:
[] Read More about FOUR by default created database users] 

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

Select all Open in new window

          Figure13: Showing guest, sys, information_schema are users without logins
 
1.3 Login Name:
It is the place where we enter login for the user. [] Refer figure11 ]
In figure8; ‘XYZ\yashwant.kumar’ is the login name for database user ‘yashwant’.
 
1.4 Default language:
We can select the desired language from the drop down list [] Refer figure11 ].
 
1.5 Default schema:
We can specify schema by browsing from the list of schemas that will own objects created by the user ‘yashwant’.
In our case default schema is dbo. [] Refer figure11 ]
 
1.5.1 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.
 
1.5.2 How to view schema ?
We can see schema by navigating Database>>Security>>Schemas in object explorer of ssms.
OR
We can see list of schemas by executing below query:
 

use TestDB
                      go
                      SELECT * FROM sys.schemas
                      go

Select all Open in new window

                                   Figure14: Showing list of schemas in TestDB database
 
1.5.3 Features of schema:

• Owner of any schema is the user who created it. We can check owner of schema by navigating right click on schema name and then click on properties.
• Ownership of 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 search button. After that we can change ownership from a given owners.
• We can move objects from one schema to other schema.

Example:
Step1: Execute below query:

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

Select all Open in new window

 Output: Here ‘Employee’ table is in ‘HumanResources’ schema
Figure15: Showing default schema for ‘Employee’ table in ‘Adventureworks2008R2’ database
 
Step2: In this step we are going to transfer ‘Employee’ table which is in ‘HumanResources’ schema to ‘dbo’ schema using below 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

Select all Open in new window

 Figure16: Transferring ‘Employee’ table to ‘dbo’ schema
 

• Multiple users can share a single default schema.

 
Execute below query to users and schema associated with them:

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

Select all Open in new window

In my case user ‘yashwant’ and ‘Jim’ are sharing same schema i.e. ‘Human Resources’ which is also clear from below screenshot.
   Figure17: Showing multiple users can share a single default schema
 

• One schema can contain objects owned by multiple users.If multiple users can share single schema then it is also possible that the objects within that schema also owned by different users.

 
2) Owned Schemas:
This page lists all possible schemas that can be owned by the database user. We have already discussed schemas refer heading 1.5 default schema.
 
3) Membership:
In this page we can see lists all possible database membership roles that can be owned by the user. Refer SQL Server Logins Elaborated for more information.
 
4) Securables:
Securables are the resources which we can assign permissions either at server level which includes resources like Endpoints, Logins, Server Roles and Databases or at database-level which includes resourcess like Users, Database Roles, Certificates and Schemas. Refer again SQL Server Logins Elaborated for more information.
 
5) 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 database itself.
 
We can create, update, delete and of course view extended properties.
 
5.1. Creating Extended Properties:
Using Stored Procedure "sp_addextendedproperty" we can create extended properties. Here I am creating extended properties for database version of database TestMore.
 
Example:

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

Select all Open in new window

                              Figure18: Creating extended properties
 
5.2 Viewing Extended Properties:
We can view extended properties by below THREE ways:
 
5.2.1 Using table "sys.extended_properties"
5.2.2 Using the "fn_listextendedproperty" function
5.2.3 Using ssms GUI
 
5.2.1 Using table sys.extended_properties:
By using below query we can view extended properties:
 

SELECT * FROM sys.extended_properties;

Select all Open in new window

                 Figure19: Viewing extended properties
 
5.2.2 Using the fn_listextendedproperty function:
"fn_listextendedproperty" is a builtin function, it returns Extended Property values of the database object.
 

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

Select all Open in new window

 Figure20: Viewing extended properties by fn_listextendedproperty
 
5.2.3 Using ssms GUI:
Open object explorer and right click on database properties.

 Figure21: Viewing extended properties by ssms GUI
 
5.3 Updating Extended Properties:
Using 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'

Select all Open in new window

                        Figure22: Updating extended properties
 
5.4 Deleting Extended Properties:
Using Stored Procedure "sp_dropextendedproperty", we can delete/drop extended properties.
 
Example:

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

Select all Open in new window

                           Figure23: Deleting extended properties
 
Want to know more about extended properties consult book Transact-SQL User-defined Functions By Andrew Novick.
 
6) References:
1. www.msdn.microsoft.com
2. www.technet.microsoft.com
3. www.sqlmatters.com
4. www.blog.lessthandot.com
5. Microsoft SQL Server 2005 Security Best Practices - Operational and Administrative Tasks; SQL Server Technical Article by Bob Beauchemin, SQLskills.com
Books:
6. Transact-SQL User-defined Functions by Andrew Novick
7. Microsoft SQL Server 2005 Programming For Dummies By Andrew Watt
8. Mastering Microsoft SQL Server 2005 By Mike Gunderloy, Joseph L. Jorden, David W. Tschanz
9. Beginning SQL Server 2012 for Developers By Robin Dewson
10. Beginning Microsoft SQL Server 2008 Administration By Chris Leiter, Dan Wood, Michael Cierkowski, Albert Boettger

7) 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. Hope you will not get bore and will enjoy this.
 
Happy Reading and Keep sharing your knowledge. :)