SQL Server Users Explained: Back To Basics
Introduction
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 you that before reading this article, read SQL Server Logins: Back To Basics first so that you can easily relate the concepts between users & logins.
Note: All screenshots are applied to SQL Server 2012 Enterprise Evaluation Edition.
Defining SQL Server User
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.
- 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
F 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
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
click here to read more about sp_msloginmappings
- 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: Back To Basics
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 create 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’
Figure6.1: Creating login
Step2: Create user ‘ianrox’ in ‘Adventureworks2008R2’ database in ssms of ‘XYZ\yashwant.kumar’
Figure6.2: Creating user for login
Step3: Create a user ‘Joe_UserWithoutLogin’ in ‘Adventureworks2008R2’ database in ssms of ‘XYZ\yashwant.kumar’
Figure6.3: Creating user without login
Step4: Connect SQL Server with loginname ‘ianrox’.
Figure6.4: Connecting SQL Server
Step5: Run below query in ssms of login ‘ianrox’:
Figure6.5: Getting error
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.
Figure6.6: Giving role to user without login
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’.
Figure6.7: Impersonating user without login
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’.
Figure6.8: Error resolved which we got at step5 [ Figure6.5 ]
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'
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 about SQL User without login ]
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
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
** **
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]
** **
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 The 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
| Database User | Description |
| DBO | Also known as Database Owner, it has all privileges & rights to perform any task in the database. DBO user also owns the default schema dbo. We cannot drop DBO user. Note: Members of sysadmin, sa & fixed server role are mapped to dbo. |
| Guest | Guest user is disabled by default for security purpose. Guest user is a member of public role and has all permissions assigned to that role. We cannot drop guest user also, we can only enable & disable it. |
| sys | 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. sys user also cannot be dropped from the database. |
| INFORMATION_SCHEMA | INFORMATION_SCHEMA user owns all the information schema views installed in each database. It is used for retrieve 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 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'
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
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
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
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
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: Back To Basics 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: Back To Basics 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'
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;
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)
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'
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'
Figure23: Deleting extended properties
Want to know more about extended properties consult book Transact-SQL User-defined Functions By Andrew Novick.
References
1. msdn.microsoft.com
2. technet.microsoft.com
4. blog.lessthandot.com
5. Microsoft SQL Server 2005 Security Best Practices - Operational and Administrative Tasks; SQL Server Technical Article by Bob Beauchemin, SQLskills.com
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
** **
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. J