SQL Server security model
To be able to access data from a database, a user must pass through two stages of authentication, one at the SQL Server level and the other at the database level. These two stages are implemented using Logins names and User accounts respectively. A valid login is required to connect to SQL Server and a valid user account is required to access a database.
Login: A valid login name is required to connect to an SQL Server instance. A login could be:
A Windows NT/2000 login that has been granted access to SQL Server
An SQL Server login, that is maintained within SQL Server
These login names are maintained within the master database. So, it is essential to backup the master database after adding new logins to SQL Server.
User: A valid user account within a database is required to access that database. User accounts are specific to a database. All permissions and ownership of objects in the database are controlled by the user account. SQL Server logins are associated with these user accounts. A login can have associated users in different databases, but only one user per database.
During a new connection request, SQL Server verifies the login name supplied, to make sure, that login is authorized to access SQL Server. This verification is called Authentication. SQL Server supports two authentication modes:
Windows authentication mode:requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.
Mixed mode: Mixed mode allows users to connect using Windows authentication or SQL Server authentication. Your DBA must first create valid SQL Server login accounts and passwords. These are not related to your Microsoft Windows NT/2000 accounts. With this authentication mode, you must supply the SQL Server login and password when you connect to SQL Server. If you do not specify SQL Server login name and password, or request Windows Authentication, you will be authenticated using Windows Authentication.
Get the effectively granted
--get all the permission in object level for specific user
EXECUTE AS LOGIN = 'User Name'
SELECT t.name, t.type_desc,a.permission_name
FROM sys.objects t CROSS APPLY fn_my_permissions(QUOTENAME(t.name), 'OBJECT') a
ORDER BY type_desc,name
REVERT
--get all permission on the DataBase for specific user
EXECUTE AS LOGIN = 'User Name'
SELECT d.name, a.subentity_name, a.permission_name
FROM sys.databases d CROSS APPLY fn_my_permissions(QUOTENAME(d.name), 'DATABASE') a
REVERT
--get all permission on the server for specific user
EXECUTE AS LOGIN = 'User Name'
SELECT @@SERVERNAME, a.subentity_name, a.permission_name
FROM fn_my_permissions(NULL, 'SERVER') a
REVERT