
USE XC_DATA
GO

if not exists (select * from master.dbo.syslogins where loginname = 'XCManager')
BEGIN
	declare @logindb nvarchar(132), @loginlang nvarchar(132) 
	select @logindb = 'XC_DATA'
	select @loginlang = @@language
	exec sp_addlogin 'XCManager', 'sutron', @logindb, @loginlang
END
GO

EXEC sp_grantdbaccess 'XCManager', 'XCManager'
GO

if not exists (select * from dbo.sysusers where name = 'XCMANAGERS' and uid > 16399)
	EXEC sp_addrole 'XCMANAGERS'
GO

GRANT CREATE TABLE TO XCMANAGERS;
GO

GRANT CREATE VIEW TO XCMANAGERS;
GO

GRANT CREATE PROCEDURE TO XCMANAGERS;
GO

GRANT CREATE FUNCTION TO XCMANAGERS;
GO

GRANT CREATE RULE TO XCMANAGERS;
GO

-- Grant him the manager role
exec sp_addrolemember 'XCMANAGERS', 'XCManager'
GO

-- Create the role for users
if not exists (select * from dbo.sysusers where name = 'XCUSERS' and uid > 16399)
	EXEC sp_addrole 'XCUSERS'
GO

if not exists (select * from master.dbo.syslogins where loginname = 'XCONNECT')
BEGIN
	exec sp_droplogin 'XCONNECT'
	declare @logindb nvarchar(132), @loginlang nvarchar(132) 
	select @logindb = 'XC_DATA'
	select @loginlang = @@language
	exec sp_addlogin 'XCONNECT', 'xconnect', @logindb, @loginlang
END
GO

EXEC sp_grantdbaccess 'XCONNECT', 'XCONNECT'
GO

exec sp_addrolemember 'XCUSERS', 'XCONNECT'
GO

