CREAR USUARIO

-- ================================================================================

-- AUTOR:       MANUEL OMAR OLGUÍN HERNÁNDEZ

-- FECHA:       1 JUN 2015

-- DESCRIPCION  CREA UN LOGIN Y SU CUENTA DE USUARIO 

-- ================================================================================

IF NOT EXISTS(SELECT * FROM SYS.syslogins WHERE NAME = 'UserName')

       BEGIN

             CREATE LOGIN UserName

             WITH PASSWORD = 'P@$$w0rd',

             DEFAULT_DATABASE = MASTER,

             DEFAULT_LANGUAGE = ENGLISH,

             CHECK_POLICY = OFF,

             CHECK_EXPIRATION = OFF;

       END

GO----------------------------------------------------------------------------------

USE AVISPA;

GO

IF NOT EXISTS(SELECT * FROM SYS.sysusers WHERE NAME = 'UserName')

       BEGIN

             CREATE USER UserNameFOR LOGIN UserName

       END

ELSE

       BEGIN

             ALTER USER UserNameWITH LOGIN = UserName

       END

 -- agrega el usuario al ROL

ALTER ROLE [ur_RoleName] ADD  MEMBER [UserName];

 ALTER ROLE [db_datareader]  ADD MEMBER [ur_RoleName]

 ALTER ROLE [db_datawriter]  ADD MEMBER [ur_RoleName]

 ALTER ROLE [db_execute]     ADD MEMBER [ur_RoleName]

------------------------------------------------------------------------------------------

       GRANT SELECT, UPDATE, INSERT, DELETE    ON [Schema].[Tabla] TO [UserName]   

       GRANT EXECUTE                           ON [Schema].[up_Procedure] TO [UserName]

       --     EXEC sp_addrolemember  N'db_datareader' ,UserName

       --     EXEC sp_addrolemember  N'db_datawriter' ,UserName

       --     EXEC sp_addrolemember  N'db_execute' ,UserName

       --     EXEC sp_addrolemember  N'db_ddladmin' ,UserName

-- FUNCIONES

GRANT SELECT ON [Brokerage].[fn_GetFees]                           TO [ur_RoleName];

-- ESCALAR FUNCTION

GRANT EXECUTE ON [Instrument].fn_GetMaturityDate                   TO [ur_RoleName];

-------------------------------------- select a todo el esquema 

GRANT SELECT ON SCHEMA :: Contact      TO [ur_RoleName];

GO

-- PARA QUE NO VEA LAS DEFINICIONES DE LA BASE DE DATOS
-----------------------------------------------------------------------------------

DENY VIEW ANY DEFINITION TO UserName

DENY EXECUTE ON SP_TABLES TO UserName

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

 -- PARA QUE PUEDAN CREAR Y MODIFICAR PROCEDURES Y VISTAS EN CIERTOS ESQUEMAS

 -----------------------------------------------------------------------------------

 GRANT CREATE PROCEDURE               TO [ur_RoleName]

 GRANT CREATE VIEW                    TO [ur_RoleName]

 -------- LOS PERMISOS DE MODIFICAR OBJETOS SON PARA LOS SIGUIENTES ESQUEMAS

 GRANT ALTER                      ON SCHEMA::Access          TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::Brokerage       TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::CAP             TO [ur_RoleName]

 GRANT ALTER                      ON SCHEMA::Client          TO [ur_RoleName]

-----------------------------------------------------------------------------------

--=======================================================================

-- crea el rol

--=======================================================================

if NOT exists (select name from sys.database_principals where name = 'db_execute')

  BEGIN

       CREATE ROLE [db_execute] AUTHORIZATION [dbo];

  END

  GO

  GRANT EXECUTE TO [db_execute]

 GO------------------------