Create user that can execute only single stored procedure

For windows account

CREATE LOGIN [BNS\EPMDataReaders] FROM WINDOWS WITH DEFAULT_DATABASE=[AssetMgmtServicesReport], DEFAULT_LANGUAGE=[us_english]

USE AssetMgmtServices;
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
GRANT SELECT ON  [dbo].AssetInventory TO [BNS\EPMDataReaders]
GRANT SELECT ON  [dbo].Region TO [BNS\EPMDataReaders]
GRANT EXECUTE ON dbo.fnLocationString TO [BNS\EPMDataReaders]
GRANT EXECUTE ON dbo.fnFormatFloor TO [BNS\EPMDataReaders]


USE AssetMgmtServicesReport;
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
GRANT SELECT ON dbo.fnGreenIT_GetAssets TO [BNS\EPMDataReaders]
GRANT SELECT ON dbo.GreenITCategoryMapper TO [BNS\EPMDataReaders]
GRANT SELECT ON dbo.GreenITCategory TO [BNS\EPMDataReaders]
GRANT EXEC ON dbo.GreenIT_GetAssets_YYMM TO [BNS\EPMDataReaders]


USE master
CREATE USER [BNS\EPMDataReaders] FOR LOGIN [BNS\EPMDataReaders]
EXEC sp_addrolemember N'security_role', N'BNS\EPMDataReaders'

For local sql account

CREATE LOGIN [GreenItUser] WITH PASSWORD=N'Scotia123', DEFAULT_DATABASE=[AssetMgmtServicesReport], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

USE AssetMgmtServices;
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
GRANT SELECT ON  [dbo].AssetInventory TO [GreenItUser]
GRANT SELECT ON  [dbo].Region TO [GreenItUser]
GRANT EXECUTE ON dbo.fnLocationString TO [GreenItUser]
GRANT EXECUTE ON dbo.fnFormatFloor TO [GreenItUser]


USE AssetMgmtServicesReport;
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
GRANT SELECT ON dbo.fnGreenIT_GetAssets TO [GreenItUser]
GRANT SELECT ON dbo.GreenITCategoryMapper TO [GreenItUser]
GRANT SELECT ON dbo.GreenITCategory TO [GreenItUser]
GRANT EXEC ON dbo.GreenIT_GetAssets_YYMM TO [GreenItUser]


USE master
CREATE USER [GreenItUser] FOR LOGIN [GreenItUser]
EXEC sp_addrolemember N'security_role', N'GreenItUser'

Adding new assembly and functions

1. Check if there is any previous assembly exists. If there is any, then remove it.

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

2. Copy dll file to server (eg: F:\Parvez\Msdn.SqlRegex.dll')

3. Create assembly in database.

                    create assembly "Msdn.SqlRegex" from 'F:\Parvez\Msdn.SqlRegex.dll'

4. Create sql object using that assembly.
   
USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatch]    Script Date: 10/28/2014 16:34:10 ******/
CREATE FUNCTION [dbo].[RegexMatch](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS [bit] WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatch]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatch.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=11 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatch'


USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroup]    Script Date: 10/28/2014 16:34:48 ******/
CREATE FUNCTION [dbo].[RegexGroup](@input [nvarchar](max), @pattern [nvarchar](4000), @name [nvarchar](4000))
RETURNS [nvarchar](max) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroup]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroup.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=9 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroup'

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexGroups]    Script Date: 10/28/2014 16:35:31 ******/
CREATE FUNCTION [dbo].[RegexGroups](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Group] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexGroups]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexGroups.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=80 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexGroups'



USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[RegexMatches]    Script Date: 10/28/2014 16:36:02 ******/
CREATE FUNCTION [dbo].[RegexMatches](@input [nvarchar](max), @pattern [nvarchar](4000))
RETURNS  TABLE (
[Index] [int] NULL,
[Text] [nvarchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) WITH EXECUTE AS CALLER
AS
EXTERNAL NAME [Msdn.SqlRegex].[UserDefinedFunctions].[RegexMatches]
GO
EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'RegexMatches.cs' ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

GO
EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=61 ,@level0type=N'SCHEMA', @level0name=N'dbo', @level1type=N'FUNCTION', @level1name=N'RegexMatches'

5. Check created objects

   SELECT

        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class

List all the objects associated with their assembly name

SELECT
        SCHEMA_NAME(O.schema_id) AS [Schema], O.name,
        A.name AS assembly_name, AM.assembly_class,
        AM.assembly_method,
        A.permission_set_desc,
        O.[type_desc]
FROM
        sys.assembly_modules AM
        INNER JOIN sys.assemblies A ON A.assembly_id = AM.assembly_id
        INNER JOIN sys.objects O ON O.object_id = AM.object_id
ORDER BY
        A.name, AM.assembly_class