return(1)
	end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

	-- CHECK IF @sid IS CURRENTLY LOGGED IN (ignore cached remote connections) --
	if exists(select * from master.dbo.sysprocesses where sid = @sid and status != 'dormant')
	begin
		raiserror(15434, -1, -1, @loginame)
		return(1)
	end

    -- CHECK IF ANY DATABASES ARE OWNED BY LOGIN --
	if exists(select * from master.dbo.sysdatabases where sid = @sid)
	begin
		raiserror(15174, -1, -1, @loginame)
		select 'Databases owned by login:' = name
                from master.dbo.sysdatabases where sid = @sid
		return(1)
	end

	-- COLLECT ALL INSTANCES OF USE OF THIS LOGIN IN SYSUSERS --
	declare @dbname		sysname
	declare ms_crs_dbname cursor local keyset for select name from master.dbo.sysdatabases
	open ms_crs_dbname
	fetch ms_crs_dbname into @dbname
	while @@fetch_status >= 0
	begin
		if (has_dbaccess(@dbname) = 1)
		begin
			select @exec_stmt = 'use ' + quotename( @dbname , '[') + '
				   insert into #db_list (dbname, user_name)
				select N'+ quotename( @dbname , '''')+', name from sysusers
				where sid = suser_sid(N' + quotename( @loginame , '''') + ') '
			exec (@exec_stmt)
		end
		else
			raiserror(15622,-1,-1, @dbname)

		fetch ms_crs_dbname into @dbname
	end
	deallocate ms_crs_dbname

    -- ERROR IF LOGIN USED AS USER IN ANY DATABASE --
	if (select count(*) from #db_list) <> 0
	begin
		raiserror(15175,-1,-1,@loginame)
		select
			'Database name:' = dbname,
			'User name:' = user_name,
			'Mapping type:' = 'user'
		from #db_list
		order by dbname
		return (1)
	end

    -- VERIFY NO JOBS IN MSDB OWNED BY THIS LOGIN --
	if db_id('msdb') is not null
        and object_id('msdb.dbo.sp_check_for_owned_jobs') is not null
	begin
        exec msdb.dbo.sp_check_for_owned_jobs @loginame, '#retval'
	    if exists (select job_count from #retval where job_count > 0)
	    begin
		    declare @job_count int
		    select @job_count = job_count from #retval
		    raiserror(14248, -1, -1, @job_count)
		    return (1)
	    end
	end

    -- DELETE THIS LOGIN (ALSO DELETES REMOTE LOGINS MAPPED TO IT) --
	delete from master.dbo.sysxlogins where sid = @sid

    -- FINALIZATION: SUCCESS/FAILURE MESSAGE
	if @@rowcount > 0
	begin
		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')

		raiserror(15479,-1,-1)
		return (0)
	end
	else
	begin
		raiserror(15007,10,-1,@loginame)
		return (1)
	end     -- sp_droplogin
go

----------------------- sp_MSaddlogin_implicit_ntlogin ------------------------

raiserror(15339,-1,-1,'sp_MSaddlogin_implicit_ntlogin')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MSaddlogin_implicit_ntlogin
    @loginame		sysname
AS

declare @default_lang sysname

    -- NO-OP IF LOGIN ALREADY EXISTS --
	-- if suser_sid(@loginame) is null

	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
	begin

        -- MUST BE NT NAME --
        if (charindex('\', @loginame) = 0)
            return (1)

	    declare	@newsid	varbinary(85),
                @status smallint

        -- OBTAIN NT SID FOR THIS LOGIN (SET STATUS BITS) --
        select @status = 4      -- ntlogin(4)
	    select @newsid = get_sid('\U'+@loginame, NULL)	    -- NT user
	    if (@newsid IS Null)
	    begin
            select @newsid = get_sid('\G'+@loginame, NULL)  -- NT group
	        IF (@newsid IS Null)
		        return (1)
	    end
        else
            select @status = @status | 8    -- NTUser
		
		-- FAIL IF SID ALREADY IN SYSLOGINS 
		if exists(select * from master.dbo.syslogins where sid = @newsid)
			return (1)

		select @default_lang = name from master.dbo.syslanguages
		where langid = @@default_langid 	--server default language


        -- ADD IMPLICIT LOGIN ENTRY --
	    INSERT into master.dbo.sysxlogins Values
            (NULL, @newsid, @status, getdate(), getdate(),
                @loginame, NULL, 1, isnull(@default_lang, N'us_english'))
		if @@error <> 0		-- this indicates we saw duplicate row
			return @@error

		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')

    end

    -- RETURN FAILURE/SUCCESS
    return (0) -- sp_MSaddlogin_implicit_ntlogin
go

------------------------------- sp_grantlogin ---------------------------------

raiserror(15339,-1,-1,'sp_grantlogin')
go
create procedure sp_grantlogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 1, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 1, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_grantlogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15401,-1,-1 ,@loginame)
		    return (1)
	    end
    end

    -- UPDATE LOGIN BITS --
    update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15480,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15481,-1,-1,@loginame)
        return (0)
	end -- sp_grantlogin
go

------------------------------- sp_validatelogins ---------------------------------

raiserror(15339,-1,-1,'sp_validatelogins')
go
create proc sp_validatelogins
AS
	-- Must be securityadmin (or sysadmin) to execute
	if is_srvrolemember('securityadmin') = 0 and is_srvrolemember('sysadmin') = 0
	begin
		raiserror(15247,-1,-1)
		return 1
	end

	-- Use get_sid() to determine if nt name is still valid (builtin is only available from system procs!)
	select 'SID' = sid, 'NT Login' = loginname from master.dbo.syslogins
		where isntname = 1 and get_sid(loginname, NULL) is null
	return 0 -- sp_validatelogins
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'xp_grantlogin')
go
create procedure xp_grantlogin
    @loginame       sysname,
    @logintype      varchar(5) = Null       -- ignored unless 'admin'
AS
	set nocount on

    -- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
    if (charindex('\', @loginame) = 0)
    begin
        declare @defdom varchar(25)
        exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                'DefaultDomain', @defdom out
        select @loginame = @defdom + '\' + @loginame
    end

	Declare @ret   int     -- return value of sp call
    execute @ret = sp_grantlogin @loginame
    if (@ret = 0 and @logintype = 'admin')
        execute @ret = sp_addsrvrolemember @loginame, 'sysadmin'
    return (@ret)
go

checkpoint
go
------------------------------- sp_denylogin ----------------------------------

raiserror(15339,-1,-1,'sp_denylogin')
go
create procedure sp_denylogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 3, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1, -1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 3, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_denylogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15401,-1,-1 ,@loginame)
		    return (1)
	    end
    end

    -- UPDATE LOGIN BITS --
    update master.dbo.sysxlogins set xstatus = (xstatus & ~2) | 1, xdate2 = getdate()
        where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15482,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15483,-1,-1,@loginame)
        return (0)
	end -- sp_denylogin
go

------------------------------- sp_revokelogin --------------------------------

raiserror(15339,-1,-1,'sp_revokelogin')
go
create procedure sp_revokelogin
    @loginame		sysname
AS
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare	@sid	varbinary(85)

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
	begin
	   dbcc auditevent (105, 2, 0, @loginame, NULL, NULL, NULL)
	   raiserror(15247,-1,-1)
	   return (1)
	end
	ELSE
	begin
	   dbcc auditevent (105, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_revokelogin')
		return (1)
	end

    -- DISALLOW SQL LOGIN (IE. MUST BE 'DOMAIN\USER') --
	if (charindex('\', @loginame) = 0)
	begin
		raiserror(15407, -1, -1, @loginame)
		return (1)
	end

    -- REMOVE ROW IF EXISTS FOR LOGIN PROVIDED IT IS AN NT NAME --
    -- select @sid = suser_sid(@loginame)
	-- if @sid is not null
	if exists(select * from master.dbo.syslogins where loginname = @loginame and isntname = 1)
	begin
		select @sid = sid from master.dbo.syslogins where loginname = @loginame and isntname = 1

        -- For nt logins, skip sid foreign-key checks. --
        -- also deletes remote logins mapped to this user --
        delete from master.dbo.sysxlogins where sid = @sid

		-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
		exec('use master grant all to null')
	end
	else
	begin
		-- CHECK IF THIS IS A VALID NT NAME BY GETTING ITS SID FROM NT
		declare	@newsid	varbinary(85)
		select @newsid = get_sid('\U'+@loginame, NULL)	    -- NT user
		IF (@newsid IS Null)
		begin
			select @newsid = get_sid('\G'+@loginame, NULL)  -- NT group
			IF (@newsid IS Null)
			begin
				raiserror(15401,-1,-1 ,@loginame)
				return (1)
			end
		end
	end


    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
	begin
		raiserror(15484,-1,-1,@loginame)
        return (1)
	end
	else
	begin
		raiserror(15485,-1,-1,@loginame)
        return (0)
	end -- sp_revokelogin
go


-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'xp_revokelogin')
go
create procedure xp_revokelogin
    @loginame       sysname
AS
	set nocount on

    -- IF NAME NOT 'DOMAIN\USER', ADD DEFAULT DOMAIN --
    if (charindex('\', @loginame) = 0)
    begin
        declare @defdom varchar(25)
        exec master.dbo.xp_instance_regread 'HKEY_LOCAL_MACHINE',
                'SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',
                'DefaultDomain', @defdom out
        select @loginame = @defdom + '\' + @loginame
    end

	Declare @ret   int     -- return value of sp call
    execute @ret = sp_revokelogin @loginame
    return (@ret)
go

------------------------------- sp_defaultdb ----------------------------------

raiserror(15339,-1,-1,'sp_defaultdb')
go
create procedure sp_defaultdb
    @loginame   sysname,	-- login name
    @defdb      sysname     -- default db
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int    -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
        AND not @loginame = suser_sname()
	begin
	    dbcc auditevent (106, 1, 0, @loginame, NULL, NULL, NULL)
		raiserror(15132,-1,-1)
		return (1)
	end
	ELSE
	begin
	   dbcc auditevent (106, 1, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_defaultdb')
		return (1)
	end


    -- VALIDATE DATABASE NAME --
    if db_id(@defdb) IS NULL
	begin
		raiserror(15010,-1,-1,@defdb)
		return (1)
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end

    -- CHANGE DEFAULT DATABASE --
    update master.dbo.sysxlogins set dbid = db_id(@defdb), xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	IF (@@error <> 0)
	    return (1)
    raiserror(15486,-1,-1)
	return (0) -- sp_defaultdb
go

checkpoint
go
---------------------------- sp_defaultlanguage -------------------------------

raiserror(15339,-1,-1,'sp_defaultlanguage')
go
create procedure sp_defaultlanguage
    @loginame sysname,			-- login name
    @language sysname = NULL	-- default language
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret   int     -- return value of sp call

    -- CHECK PERMISSIONS --
	IF (not is_srvrolemember('securityadmin') = 1)
        AND not @loginame = suser_sname()
	begin
	    dbcc auditevent (106, 2, 0, @loginame, NULL, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	ELSE
	begin
	   dbcc auditevent (106, 2, 1, @loginame, NULL, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_defaultlanguage')
		return (1)
	end

	-- VALIDATE LANGUAGE --
	IF (@language is not Null)
	begin
		Execute @ret = sp_validlang @language
		IF (@ret <> 0)
			return (1)
	end
	else
	begin

		select @language = name from master.dbo.syslanguages
		where langid = @@default_langid  --default language

		if @language is null
			select @language = N'us_english'
	end

    -- ADD ROW FOR NT LOGIN IF NEEDED --
	if not exists(select * from master.dbo.syslogins where loginname = @loginame)
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end

    -- CHANGE DEFAULT LANGUAGE --
    update master.dbo.sysxlogins set language = @language, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	if @@error <> 0
    	return (1)
    raiserror(15487,-1,-1,@loginame,@language)
	return (0) -- sp_defaultlanguage
go

--------------------------- sp_addsrvrolemember -------------------------------

raiserror(15339,-1,-1,'sp_addsrvrolemember')
go
create procedure sp_addsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
            @sid        varbinary(85)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 1, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT A SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 1, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

	select @sid = sid from master.dbo.syslogins where loginname = @loginame
    -- ADD ROW FOR NT LOGIN IF NEEDED --
    if @sid is null
    begin
        execute @ret = sp_MSaddlogin_implicit_ntlogin @loginame
        if (@ret <> 0)
	    begin
		    raiserror(15007,-1,-1,@loginame)
		    return (1)
	    end
    end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus | @rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15488,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_addsrvrolemember
go

checkpoint
go
--------------------------- sp_dropsrvrolemember ------------------------------

raiserror(15339,-1,-1,'sp_dropsrvrolemember')
go
create procedure sp_dropsrvrolemember
    @loginame sysname,			-- login name
    @rolename sysname = NULL	-- server role name
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @rolebit    smallint,
            @ismem      int,
			@sid		varbinary(85)

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_dropsrvrolemember')
		return (1)
	end

    -- VALIDATE SERVER ROLE NAME, CHECKING PERMISSIONS --
    select @ismem = is_srvrolemember(@rolename)
    if @ismem is null
    begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
        raiserror(15402, -1, -1, @rolename)
        return (1)
    end
    if @ismem = 0
	begin
		dbcc auditevent (108, 2, 0, @loginame, NULL, @rolename, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end

	-- AUDIT THE SUCCESSFUL SECURITY CHECK --
	dbcc auditevent (108, 2, 1, @loginame, NULL, @rolename, NULL)

    -- OBTAIN THE BIT FOR THIS ROLE --
    select @rolebit = CASE @rolename
            WHEN 'sysadmin'         THEN 16
            WHEN 'securityadmin'    THEN 32
            WHEN 'serveradmin'      THEN 64
            WHEN 'setupadmin'       THEN 128
            WHEN 'processadmin'     THEN 256
            WHEN 'diskadmin'        THEN 512
            WHEN 'dbcreator'        THEN 1024
			WHEN 'bulkadmin'		THEN 4096
            ELSE NULL END

	select @sid = sid from master.dbo.syslogins where loginname = @loginame
	-- ERROR IF USER DOESNT EXIST --
	if @sid is null
    begin
	    raiserror(15007,-1,-1,@loginame)
	    return (1)
    end
    -- CANNOT CHANGE SA ROLES --
	else if @sid = 0x1	-- 'sa'
    begin
        raiserror(15405, -1 ,-1, @loginame)
        return (1)
    end

    -- UPDATE ROLE MEMBERSHIP --
    update master.dbo.sysxlogins set xstatus = xstatus & ~@rolebit, xdate2 = getdate()
	    where name = @loginame and srvid IS NULL

	-- UPDATE PROTECTION TIMESTAMP FOR MASTER DB, TO INDICATE SYSLOGINS CHANGE --
	exec('use master grant all to null')

	raiserror(15489,-1,-1,@loginame,@rolename)

    -- FINALIZATION: RETURN SUCCESS/FAILURE
	return (@@error) -- sp_dropsrvrolemember
go

-- GRANT PUBLIC ACCESS (SP'S DO INTERNAL PERMISSIONS CHECKS) --
grant execute on sp_addlogin to public
grant execute on sp_password to public
grant execute on sp_droplogin to public
grant execute on sp_grantlogin to public
grant execute on sp_validatelogins to public
grant execute on xp_grantlogin to public
grant execute on sp_denylogin to public
grant execute on sp_revokelogin to public
grant execute on xp_revokelogin to public
grant execute on sp_defaultdb to public
grant execute on sp_defaultlanguage to public
grant execute on sp_addsrvrolemember to public
grant execute on sp_dropsrvrolemember to public
grant execute on sp_attach_db to public
grant execute on sp_attach_single_file_db to public
go

/**************************  END LOGIN-SECURITY ******************************/


/******************************************************************************
************************  DATABASE-ACCESS-SECURITY  ***************************
******************************************************************************/
checkpoint
go
if object_id('sp_MSadduser_implicit_ntlogin','P') IS NOT NULL
	drop procedure sp_MSadduser_implicit_ntlogin
if object_id('sp_MScheck_uid_owns_anything','P') IS NOT NULL
	drop procedure sp_MScheck_uid_owns_anything
if object_id('sp_grantdbaccess','P') IS NOT NULL
	drop procedure sp_grantdbaccess
if object_id('sp_revokedbaccess','P') IS NOT NULL
	drop procedure sp_revokedbaccess
if object_id('sp_adduser','P') IS NOT NULL
	drop procedure sp_adduser
if object_id('sp_dropuser','P') IS NOT NULL
	drop procedure sp_dropuser
if object_id('sp_addalias','P') IS NOT NULL
	drop procedure sp_addalias
if object_id('sp_dropalias','P') IS NOT NULL
	drop procedure sp_dropalias
if object_id('sp_addrole','P') IS NOT NULL
	drop procedure sp_addrole
if object_id('sp_droprole','P') IS NOT NULL
	drop procedure sp_droprole
if object_id('sp_addgroup','P') IS NOT NULL
	drop procedure sp_addgroup
if object_id('sp_dropgroup','P') IS NOT NULL
	drop procedure sp_dropgroup
if object_id('sp_addapprole','P') IS NOT NULL
	drop procedure sp_addapprole
if object_id('sp_approlepassword','P') IS NOT NULL
	drop procedure sp_approlepassword
if object_id('sp_setapprole','P') IS NOT NULL
	drop procedure sp_setapprole
if object_id('sp_dropapprole','P') IS NOT NULL
	drop procedure sp_dropapprole
if object_id('sp_addrolemember','P') IS NOT NULL
	drop procedure sp_addrolemember
if object_id('sp_droprolemember','P') IS NOT NULL
	drop procedure sp_droprolemember
if object_id('sp_changegroup','P') IS NOT NULL
	drop procedure sp_changegroup
if object_id('sp_change_users_login','P') IS NOT NULL
    drop procedure sp_change_users_login
if object_id('sp_changedbowner','P') IS NOT NULL
	drop procedure sp_changedbowner
if object_id('sp_check_removable_sysusers','P') IS NOT NULL
	drop procedure sp_check_removable_sysusers
if object_id('sp_changeobjectowner', 'P') IS NOT NULL
	drop procedure sp_changeobjectowner
go

----------------------- sp_MSadduser_implicit_ntlogin -------------------------

raiserror(15339,-1,-1,'sp_MSadduser_implicit_ntlogin')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MSadduser_implicit_ntlogin
    @ntname         sysname
AS
    -- NO-OP IF LOGIN ALREADY EXISTS --
	if user_id(@ntname) is null
	begin

        -- MUST BE NT NAME --
        if (charindex('\', @ntname) = 0)
            return (1)

	    declare	@newsid	varbinary(85),
                @status smallint,
                @uid    smallint

        -- OBTAIN NT SID FOR THIS USER (SET STATUS BITS) --
        select @status = 4      -- ntlogin(4)
	    select @newsid = get_sid('\U'+@ntname, NULL)	    -- NT user
	    if (@newsid is Null)
	    begin
	        select @newsid = get_sid('\G'+@ntname, NULL)    -- NT group
	        IF (@newsid IS Null)
		        return (1)
	    end
        else
            select @status = @status | 8    -- NTUser

        -- FAIL IF SID ALREADY IN SYSUSERS --
        if exists (select sid from sysusers where sid = @newsid)
            return (1)

        -- OBTAIN NEW UID (RESERVE 1-4) --
        if user_name(5) IS NULL
            select @uid = 5
        else
		    select @uid = min(uid)+1 from sysusers
                where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                    and user_name(uid+1) is null        -- uid not in use
        if @uid is null
	    begin
		    raiserror(15065,-1,-1)
		    return (1)
	    end

        -- ADD IMPLICIT SYSUSERS ENTRY --
        insert into sysusers values
            (@uid, @status, @ntname, @newsid, 0x00, getdate(), getdate(), 0, NULL)

        -- INVALIDATE UID CACHE FOR THIS DB --
        grant all to null
    end

    -- RETURN FAILURE/SUCCESS --
    return @@error -- sp_MSadduser_implicit_ntlogin
go

------------------------ sp_MScheck_uid_owns_anything -------------------------

raiserror(15339,-1,-1,'sp_MScheck_uid_owns_anything')
go
----------------------------------------
-- NOTE: FOR INTERNAL SECURITY USE ONLY!
--      DO NOT DOCUMENT OR USE!
----------------------------------------
create procedure sp_MScheck_uid_owns_anything
    @uid            smallint        -- uid to for which to check ownership
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @isowner    int
    select @isowner = 0

    -- CHECK IF USER OWNS ANY OBJECTS --
    select @isowner = 0
    if exists (select uid from sysobjects where uid = @uid)
	begin
		raiserror(15183,-1,-1)
		select name, type from sysobjects where uid = @uid
        select @isowner = 1
	end

    -- CHECK IF USER OWNS ANY TYPES --
    if exists (select uid from systypes where uid = @uid)
    begin
		raiserror(15184,-1,-1)
		select user_type = name, physical_type = type_name(xtype)
					from systypes where uid = @uid
        select @isowner = 1
	end

    -- CHECK IF USER GRANTED ANY PERMISSIONS --
    if exists (select grantor from syspermissions where grantor = @uid)
    begin
		raiserror(15284,-1,-1)
		select 'Grantee'=user_name(grantee) ,'Object'=object_name(id)
					from syspermissions where grantor = @uid
        select @isowner = 1
	end

    -- CHECK IF USER OWNS ANY ROLES --
    if exists (select altuid from sysusers where altuid = @uid
                and (issqlrole = 1 or isapprole = 1))
    begin
		raiserror(15421,-1,-1)
		select 'Role Name' = name,
               'Type' = CASE WHEN issqlrole=1 THEN 'SQL Role'
                             ELSE 'App Role' END
            from sysusers where altuid = @uid and (issqlrole = 1 or isapprole = 1)
        select @isowner = 1
    end

    return @isowner	-- sp_MScheck_uid_owns_anything
go

------------------------------ sp_grantdbaccess -------------------------------

raiserror(15339,-1,-1,'sp_grantdbaccess')
go
create procedure sp_grantdbaccess
	@loginame       sysname,
	@name_in_db     sysname = NULL OUT
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int,    -- return value of sp call
            @uid        smallint,
            @sid        varbinary(85),
            @status     smallint

    if @name_in_db is null
        select @name_in_db = @loginame

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
        dbcc auditevent (109, 3, 0, @loginame, @name_in_db, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
        dbcc auditevent (109, 3, 1, @loginame, @name_in_db, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_grantdbaccess')
		return (1)
	end

    -- VALIDATE NAME-IN-DB --
    if @name_in_db <> @loginame
    begin
		exec @ret = sp_validname @name_in_db
		if @ret <> 0
			return(1)
        if (charindex('\', @name_in_db) > 0)
        begin
            raiserror(15006,-1,-1,@name_in_db)
            return (1)
        end
    end

    -- CHECK FOR SPECIAL USER GUEST --
    if @name_in_db = 'guest'
    begin
        -- ERROR IF NOT USER, OR ALREADY ADDED --
        if @loginame <> 'guest'
        begin
		    raiserror(15062,-1,-1)
		    return(1)
        end
        if exists (select * from sysusers where hasdbaccess = 1 and name = 'guest')
        begin
            raiserror(15023,-1,-1,'guest')
            return (1)
        end

        -- ENABLE USER GUEST --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where name = 'guest'
        return (0)
    end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = case when (charindex('\', @loginame) <> 0) then 4 else 0 end
    if @status = 0
        select @sid = sid from master.dbo.syslogins         -- sql user
            where isntname = 0 and loginname = @loginame
    if @sid is null
    begin
        -- NT GROUPS REQUIRE DOMAIN NAME --
        if @status = 4
            select @sid = get_sid('\G'+@loginame, NULL)     -- nt group
        if @sid is null
        begin
            select @sid = get_sid('\U'+@loginame, NULL)     -- nt user
            if @sid is not null
                select @status = 12
        end
    end
    -- PREVENT USE OF CERTAIN LOGINS --
	else if @sid = 0x1	-- 'sa'
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    if @sid is null
    begin
        if @status = 0
            raiserror(15007,-1,-1,@loginame)
        else
            raiserror(15401,-1,-1,@loginame)
        return (1)
    end

    -- CHECK IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin
        -- ERROR IF LOGIN IS ALREADY ALIASED --
        if exists (select sid from sysusers where sid = @sid and isaliased = 1)
        begin
		    raiserror(15022,-1,-1)
		    return (1)
        end

        -- ERROR IF ALREADY EXISTS UNDER DIFFERENT NAME --
        if (not user_sid(user_id(@name_in_db)) = @sid)
        begin
		    raiserror(15063,-1,-1)
		    return (1)
        end

        -- ERROR IF LOGIN ALREADY HAS ACCESS --
        if exists (select sid from sysusers where sid = @sid and hasdbaccess = 1)
        begin
            if @status = 4
    		    raiserror(15024,-1,-1,@name_in_db)
            else
		        raiserror(15023,-1,-1,@name_in_db)
		    return (1)
        end

        -- GIVE DATABASE ACCESS TO THIS LOGIN --
        update sysusers set status = (status & ~1) | 2, updatedate = getdate()
                    where sid = @sid
        return @@error
	end

	if @name_in_db = 'sys'
		raiserror(15355,-1,-1)

    if user_id(@name_in_db) is not null OR
		@name_in_db IN ('system_function_schema','INFORMATION_SCHEMA')
    begin
        -- SYSUSERS NAME ALREADY EXISTS --
        if @status = 4
    		raiserror(15024,-1,-1,@name_in_db)
        else
		    raiserror(15023,-1,-1,@name_in_db)
        return (1)
    end

    -- OBTAIN NEW UID (RESERVE 1-4) --
    if user_name(5) IS NULL
        select @uid = 5
    else
		select @uid = min(uid)+1 from sysusers
            where uid >= 5 and uid < (16384 - 1)    -- stay in users range
                and user_name(uid+1) is null        -- uid not in use
    if @uid is null
	begin
		raiserror(15065,-1,-1)
		return (1)
	end

    -- INSERT SYSUSERS ROW --
    insert into sysusers select
        @uid, @status | 2, @name_in_db, @sid, 0x00, getdate(), getdate(), 0, NULL

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    -- PRINT SUCCESS --
    raiserror(15341,-1,-1, @loginame)

    -- RETURN SUCCESS STATUS --
    return @@error -- sp_grantdbaccess
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'sp_adduser')
go
create procedure sp_adduser
	@loginame       sysname,	    -- user's login name in syslogins
	@name_in_db     sysname = NULL, -- user's name to add to current db
	@grpname		sysname = NULL  -- role to which user should be added.
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @ret        int

    -- LIMIT TO SQL/NT USERS IN SYSLOGINS (BCKWRD COMPAT ONLY!)
	if not exists (select * from master.dbo.syslogins where loginname = @loginame
			and (isntuser = 1 or isntname = 0))
        and @loginame <> 'guest'
    begin
        raiserror(15007,-1,-1,@loginame)
        return (1)
    end

	-- VALIDATE THE ROLENAME --
    if @grpname is not null and
	   not exists (select * from sysusers where name = @grpname and issqlrole = 1)
    begin
	    raiserror(15014,-1,-1,@grpname)
	    return (1)
    end

    if @name_in_db is null
        select @name_in_db = @loginame

	-- In Hydra only the user dbo can do this --
    if (not is_member('dbo') = 1)
	begin
	    -- AUDIT FAILED SECURITY CHECK --
        dbcc auditevent (109, 1, 0, @loginame, @name_in_db, @grpname , NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
    else
    begin
        -- AUDIT SUCCESSFUL SECURITY CHECK --
        dbcc auditevent (109, 1, 1, @loginame, @name_in_db, @grpname , NULL)
    end

    -- ADD THE USER TO THE DATABASE --
    execute @ret = sp_grantdbaccess @loginame, @name_in_db OUT
    if (@ret <> 0)
        return (1)

    -- ADD USER TO ROLE IF GIVEN. NOP FOR 'public' --
    if (@grpname is not null) and (@grpname <> 'public')
    begin
        execute @ret = sp_addrolemember @grpname, @name_in_db
        if @ret <> 0
		begin
			-- ROLL BACK THE ABOVE sp_grantdbaccess --
			if @name_in_db = 'guest'
				update sysusers set status = status & ~2, updatedate = getdate()
                            where name = 'guest'
			else
				delete from sysusers where name = @name_in_db
            return (1)
		end
    end

    -- RETURN SUCCESS --
    return (0) -- sp_adduser
go

checkpoint
go

----------------------------- sp_revokedbaccess -------------------------------

raiserror(15339,-1,-1,'sp_revokedbaccess')
go
create procedure sp_revokedbaccess
	@name_in_db     sysname
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @uid        smallint,
            @ret        int

    -- CHECK PERMISSIONS --
    if (not is_member('db_accessadmin') = 1) and
       (not is_member('db_owner') = 1)
	begin
		dbcc auditevent (109, 4, 0, NULL, @name_in_db, NULL, NULL)
		raiserror(15247,-1,-1)
		return (1)
	end
	else
	begin
		dbcc auditevent (109, 4, 1, NULL, @name_in_db, NULL, NULL)
	end

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_revokedbaccess')
		return (1)
	end

    -- CHECK IF SYSUSER EXISTS --
    select @uid = uid from sysusers where name = @name_in_db
            and (issqluser = 1 or isntname = 1)         -- is droppable entity
            and (name <> 'guest' or hasdbaccess = 1)    -- special case guest
    if @uid is null
    begin
		raiserror(15008,-1,-1,@name_in_db)
        return (1)
    end

    -- CANNOT DROP DBO/INFORMATION_SCHEMA/public --
	if @uid in (1,0,3,4) --dbo, public, INFORMATION_SCHEMA, system_function_schema
	begin
		raiserror(15181,-1,-1)
		return (1)
	end

    -- CANNOT DROP GUEST IN MASTER/TEMPDB --
	if lower(@name_in_db) = 'guest' and db_id() in (1, 2)
	begin
		raiserror(15182,-1,-1)
		return(1)
	end

    -- CHECK IF USER OWNS ANYTHING --
    execute @ret = sp_MScheck_uid_owns_anything @uid
    if @ret <> 0
        return (1)

    -- REMOVE SYSPERMISSIONS ROWS AND DEPENDENT ALIASES --
    delete from syspermissions where grantee = @uid
    if exists (select altuid from sysusers where altuid = @uid and isaliased = 1)
    begin
        delete from sysusers where altuid = @uid and isaliased = 1
       	raiserror(15490,-1,-1)
    end

    -- DROP USER: SPECIAL HANDLING FOR GUEST (REMOVE HASDBACCESS) --
    if lower(@name_in_db) = 'guest'
        update sysusers set status = status & ~2, updatedate = getdate()
			where uid = user_id('guest')
    else
	begin
	    delete from sysusers where uid = @uid

		delete from sysproperties where type =  2 and id = 0 and
		smallid = @uid

	end
    -- RETURN SUCCESS/FAILURE --
    if @@error <> 0
        return (1)

    -- INVALIDATE CACHED PERMISSIONS --
    grant all to null

    raiserror(15491,-1,-1)
    return (0) -- sp_revokedbaccess
go

-- FOR BACKWARD COMPATIBILTY ONLY --
raiserror(15339,-1,-1,'sp_dropuser')
go
create procedure sp_dropuser
	@name_in_db     sysname     -- user name to drop
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
    declare @ret    int
    declare @targetName sysname
    -- LIMIT TO SQL/NT USERS (BCKWRD COMPAT ONLY!)
    if not exists (select * from sysusers where name = @name_in_db
                    and (isntuser = 1 or isntname = 0))
    begin
        raiserror(15008,-1,-1,@name_in_db)
        return (1)
    end
    -- store target name
    select  @targetName = (select sl.name from master..syslogins sl,sysusers su where su.name = @name_in_db and su.sid = sl.sid)

    -- DROP THE USER FROM THE DATABASE --
    execute @ret = sp_revokedbaccess @name_in_db
    if @ret <> 0
    begin
        -- AUDIT FAILED SECURITY CHECK
    dbcc auditevent (109, 2, 0, @targetName, @name_in_db, NULL, NULL)
        return (1)
    end

    -- AUDIT SUCCESSFUL SECURITY CHECK --
    dbcc auditevent (109, 2, 1, @targetName, @name_in_db, NULL, NULL)

    -- FINALIZATION: PRINT/RETURN SUCCESS --
    return (0) -- sp_dropuser
go

-------------------------------- sp_addalias ----------------------------------

raiserror(15339,-1,-1,'sp_addalias')
go
create procedure sp_addalias
    @loginame       sysname,    -- name of the pretender
    @name_in_db     sysname     -- user to whom to alias the login
as
    -- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
	set nocount on
	declare @sid        varbinary(85),
            @targuid    smallint,
            @newuid     smallint,
            @status     smallint,
            @dbname     sysname

    -- CHECK PERMISSIONS --
    create table #Trace_Status (TraceFlag int, Status int)
	DBCC TRACESTATUS('no_output', 4650) with NO_INFOMSGS
	if @@rowcount > 0
	begin
		insert into #Trace_Status exec('DBCC TRACESTATUS(4650) WITH NO_INFOMSGS')
	end
       
    if (not is_member('db_owner') = 1) and 
		((not exists (select * from #Trace_Status where TraceFlag = 4650 and Status = 1)) or (not is_member('db_accessadmin') = 1))
	begin
		drop table #Trace_Status
		raiserror(15247,-1,-1)
		return (1)
	end

	drop table #Trace_Status

    -- DISALLOW USER TRANSACTION --
	set implicit_transactions off
	IF (@@trancount > 0)
	begin
		raiserror(15002,-1,-1,'sp_addalias')
		return (1)
	end

    -- VALIDATE LOGIN NAME (OBTAIN SID) --
    select @status = CASE WHEN charindex('\', @loginame) > 0 THEN 12 ELSE 0 END
    if @status = 0
        select @sid = suser_sid(@loginame)          -- sql user
    -- retry sql user as nt with dflt domain
    if @sid is null
    begin
        select @sid = get_sid('\U'+@loginame, NULL) -- nt user
        if @sid is null
        begin
            if @status = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
        select @status = 12
    end
    -- PREVENT USE OF CERTAIN LOGINS --
	else if @sid = 0x1
	begin
		raiserror(15405, -1, -1, @loginame)
		return (1)
	end

    -- VALIDATE NAME-IN-DB (OBTAIN TARGET UID) --
    select @targuid = uid from sysusers where name = @name_in_db
                        and (issqluser = 1 or isntuser = 1)
						and uid NOT IN (3,4)	-- INFORMATION_SCHEMA, system_function_schema
    if @targuid is null
	begin
		raiserror(15008,-1,-1,@name_in_db)
		return (1)
	end

    -- ERROR IF LOGIN ALREADY IN DATABASE --
    if exists (select sid from sysusers where sid = @sid)
    begin