Permissions on SQL Server

Permissions on SQL Server

                                                  User Permissions on SQL Server some times little bit pane full work if you need to manage lot's of users.(Ex: Domain Users + SQL Server Users) This article I'm going to explain about SQL Server user Permissions, create users ,grants and revoke.

Create User and SET GRANT to Connect only

--initially we have created two databases call MSDN and --Oracle
USE [MSDN]
GO
CREATE LOGIN DatabaseUserGroup WITH PASSWORD = 'DatabaseUserGroup';
CREATE USER DatabaseUserGroup WITH DEFAULT_SCHEMA = DatabaseUserGroup;
GO
GRANT CONNECT TO DatabaseUserGroup;
GO
CREATE SCHEMA DatabaseUserGroup;
GO

Well, now verify above user,

USE [master]
GO
USE MSDN
GO
SELECT * FROM [MSDN]..sysobjects;
GO
USE ORACLE
GO
SELECT * FROM [ORACLE]..sysobjects;
GO

(0 row(s) affected)
Msg 916, Level 14, State 1, Line 1
The server principal "DatabaseUserGroup" is not able to access the database "ORACLE" under the current security context.
Msg 916, Level 14, State 1, Line 1
The server principal "DatabaseUserGroup" is not able to access the database "ORACLE" under the current security context.

According to above error now you can notice, Database "Oracle" can not connect above user(User:DatabaseUserGroup) and Database "MSDN" can connect. For View User Permissions you can use following T-SQL Scripts,

SELECT    spr.[principal_id], spr.[name], spr.[type_desc],
        CASE spr.[is_disabled] WHEN 1 THEN 'Disabled' ELSE 'Enable' END [DisabledOREnable],
        smp.[state_desc], smp.[permission_name]
FROM    sys.server_principals AS spr INNER JOIN
        sys.server_permissions AS smp ON smp.[grantee_principal_id] = spr.[principal_id]
ORDER BY spr.[principal_id];

SELECT
        dp.[class_desc],
        dp.[permission_name],
        dp.[state_desc],
        ObjectName = OBJECT_NAME(dp.[major_id]),
        GranteeName = dpr.[name],
        dpr.[type_desc],
        dpr.[default_schema_name]
FROM sys.database_permissions dp INNER JOIN sys.database_principals dpr
ON dp.[grantee_principal_id] = dpr.[principal_id]
ORDER BY dp.[class_desc],OBJECT_NAME(dp.[major_id]);

SELECT * FROM sys.fn_my_permissions(NULL, 'SERVER');
GO
SELECT * FROM sys.fn_my_permissions(NULL, 'DATABASE');
GO

"sp_helplogins" Procedure will help you to find information about logins and the users associated with them in each database. But you need to have Execute Grant for execute this system procedure,

EXEC [master]..sp_helplogins;
GO
EXEC [master]..sp_helpuser;
GO