Create Login:

USE [master]

GO

DECLARE              @USERNAME VARCHAR(20) = 'MyUser'

                          ,@PASSWORD VARCHAR(20) = 'MyUser123'

DECLARE @SQL VARCHAR(MAX) = ''

IF EXISTS (SELECT LOGINNAME FROM DBO.SYSLOGINS WHERE NAME = @USERNAME)

BEGIN

                SET @SQL = 'DROP LOGIN ' + @USERNAME

                EXEC(@SQL)

                IF @@ERROR = 0

                BEGIN

                                PRINT 'Login ' + @USERNAME + ' Dropped.'

                END

END

BEGIN

                SET @SQL = 'CREATE LOGIN [' + @USERNAME + '] WITH PASSWORD=''' + @PASSWORD + ''', DEFAULT_DATABASE=[MASTER], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF'

                EXEC(@SQL)

                IF @@ERROR = 0

                BEGIN

                                PRINT 'Login ' + @USERNAME + ' Created On Master.'

                END

                PRINT CHAR(13)

END

GO

 

Create User:

USE [databasename]

GO

DECLARE @USERNAME VARCHAR(20) = 'MyUser'

DECLARE @SQL VARCHAR(MAX) = ''

IF EXISTS(SELECT NAME FROM SYS.DATABASE_PRINCIPALS WHERE NAME= @USERNAME)

BEGIN

                SET @SQL = 'DROP USER ' + @USERNAME

                EXEC(@SQL)

                IF @@ERROR = 0

                BEGIN

                                PRINT 'User ' + @USERNAME + ' Dropped.'

                END

END

SET @SQL = 'CREATE USER [' + @USERNAME + '] FOR LOGIN [' + @USERNAME + '] WITH DEFAULT_SCHEMA=[dbo]'

EXEC(@SQL)

IF @@ERROR = 0

BEGIN

                PRINT 'User ' + @USERNAME + ' Created.'

END

PRINT CHAR(13)

GO

 

Grant Permission:

DECLARE @USERNAME VARCHAR(20) = 'MyUser', @SCHEMANAME VARCHAR(20) = '', @OBJECTNAME VARCHAR(100) = ''

DECLARE @SQL VARCHAR(MAX) = ''

DECLARE VW_CURSOR CURSOR

                FOR SELECT

                                SCHEMA_NAME(SCHEMA_ID) AS SCHEMA_NAME

                                ,NAME AS VIEW_NAME

                FROM

                                SYS.VIEWS

                ORDER BY

                                SCHEMA_NAME

OPEN VW_CURSOR

FETCH NEXT FROM VW_CURSOR

INTO @SCHEMANAME, @OBJECTNAME

WHILE @@FETCH_STATUS = 0

BEGIN

                SET @SQL = 'GRANT SELECT ON [' + @SCHEMANAME + '].[' + @OBJECTNAME + '] TO ' + @USERNAME

                EXEC(@SQL)

                PRINT 'Select Access Granted On [' + @SCHEMANAME + '].[' + @OBJECTNAME + '] TO ' + @USERNAME

                PRINT CHAR(13)

                FETCH NEXT FROM VW_CURSOR

                INTO @SCHEMANAME, @OBJECTNAME

END

CLOSE VW_CURSOR

DEALLOCATE VW_CURSOR