A colleague, moving it’s first steps into .NET and SQL Server to move it’s applications in 21st century, taking a look at a database I made asked me the following questions:
Why do I found the SQL User (MyUser) both in Server Security and in your Database? And Why did you tell me you granted permission to a Database Role (db_MyRole) instead of giving them to the user?
How the security works in SQL Server:
To answer his questions I’ve decided to explain the basics here in the hope to be helpful to more beginners.
In order to be able to connect an Application to SQL Server, the Application needs to know a Username and a Password (in Sql Server mode) or the Windows Domain User that uses the application or a Domain Group of which it is member needs to be mapped as a trusted User or Group in SQL Server.
This can be made manually by the SQL Server Administrator using SQL Management Studio and going to the Security Folder Using the Add Login feature, or it can be done using the following code in a query (connecting to the SQL Server with Administrator rights).
CREATE LOGIN [MyUser] WITH PASSWORD=N'MyPassword'
, DEFAULT_DATABASE=[master]
, DEFAULT_LANGUAGE=[us_english]
, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
CREATE LOGIN [MyWindowsUserOrGroup]
FROM WINDOWS WITH DEFAULT_DATABASE=[master]
, DEFAULT_LANGUAGE=[us_english]
But this is not enough, with this credentials an Application can open a connection to the SQL Server, but the server gives no automatic access to it’s data to a new login.
To access a Database, the logins (Sql or trusted) first need to be mapped on the database (or databases), this can be achieved from SQL Management Studio, going to the Database Security Folder and adding the user, or through the Usermapping feature in the Login Mask on server security, otherwise it can be done using the following T-SQL queries.
CREATE USER [MyUser]
FOR LOGIN [MyUser] WITH DEFAULT_SCHEMA=[dbo]
CREATE USER [MyWindowsUserOrGroup]
FOR LOGIN [MyWindowsUserOrGroup] WITH DEFAULT_SCHEMA=[dbo]
After this operation is done, the application can connect to the server and access the database, but still it can’t see any data, because we didn’t grant it any permission on the database objects, such as Tables, Stored Procedures, Views, Functions and more.
We can grant permission on the objects to our logins in two ways, mapping them directly to the User or Windows User, generate a database Role, give permissions to the role and then assign the role to the users. The second way is the one I choose because a user can be changed for any reason, so the role can be passed to another user and we are sure the user has all permissions he needs. The role can also be assigned to more than one user and again we are sure the users have all the correct permissions and if we need to add some permissions or remove some permission we do it on the role and all users benefit of the modifications.
Again, this can be done both from the SQL management studio and by code.
USE [MyDatabase]
GO
CREATE ROLE [MyRole] AUTHORIZATION [sa]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect1] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT EXECUTE ON [dbo].[MySpSelect2] TO [MyRole]
GO
use [MyDatabase]
GO
GRANT SELECT ON [dbo].[MyTable2] TO [MyRole]
GO
After the role is made, we made the users member of the role and we have reached our goal. Also this operation can be done using SSMS or the following query.
USE [FyRepository]
GO
EXEC sp_addrolemember N'MyRole', N'Myuser'
GO
In Short
To access the data of a SQL Server database, an Application needs to be able to Connect to the Server through a Login, the Login needs to be mapped as a Database User on the database (s) needed, and the Database User mapped needs to be member of one or more Database Roles that grant it permissions on the database objects.
Technorati Tag:
SQL,
T-SQL,
Security