Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_addalias

 

Aliasses are no longer supported in SQL 2008. Add the user a normal user in de datase. If you justed to made a alias to the dbo use add the user know as db_owner.

EXEC sp_addrolemember N'db_owner', N'loginname'

See also sp_changedbowner




Syntax
create procedure sys.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    int,
            @dbname     sysname,
			@isext		bit,
			@ret		int

    -- CHECK PERMISSIONS
    if (not is_member('db_owner') = 1)
	begin
		raiserror(15247,-1,-1)
		return (1)
	end

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

    -- ERROR IF login if is a role
	if exists(select * from sys.server_principals where name = @loginame and type = 'R')
    begin
	    raiserror(15007,-1,-1,@loginame)
	    return (1)
    end


	BEGIN TRANSACTION
	
    -- VALIDATE LOGIN NAME (OBTAIN SID)
	select @isext = case when charindex('\', @loginame) > 0 then 1 else 0 end

    if @isext = 0
    begin
		-- Get SHARED LOCK on Login
		EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 0)
		select @ret = @@error
		if @ret = 0
		begin
		-- We already have a lock on the login. So dont need to check if sid is null
			select @sid = suser_sid(@loginame)          -- sql user

		-- PREVENT USE OF CERTAIN LOGINS --	
			if @sid = 0x1	-- 'sa'
    		begin
   				ROLLBACK TRANSACTION
		        raiserror(15405, -1 ,-1, @loginame)
    		    return (1)
	    	end		
		end
	end
	
    -- retry sql user as nt with dflt domain
    if @isext <> 0 or @ret <> 0
    begin
        select @sid = get_sid('\U'+@loginame) -- nt user
        if @sid is null
        begin
       		ROLLBACK TRANSACTION
            if @isext = 0
                raiserror(15007,-1,-1,@loginame)
            else
                raiserror(15401,-1,-1,@loginame)
            return (1)
        end
   		select @isext = 1
    end

	-- EXCL LOCK ALIAS
	EXEC %%Owner(Name = @name_in_db).Lock(Exclusive = 1)

	-- VALIDATE NAME-IN-DB (OBTAIN TARGET UID)
	if @@error = 0 -- if name exists, verify further
		select @targuid = principal_id from sys.database_principals where name = @name_in_db
						and (type = 'S' or type = 'U')
						and principal_id NOT IN (3,4)	-- INFORMATION_SCHEMA, sys
    if @targuid is null
	begin
		ROLLBACK TRANSACTION
		raiserror(15008,-1,-1,@name_in_db)
		return (1)
	end

    -- ERROR IF LOGIN ALREADY ALIASED
    if exists (select * from sys.sysusers where sid = @sid and isaliased = 1)
    begin
	    ROLLBACK TRANSACTION
	    raiserror(15022,-1,-1)
	    return (1)
    end

    -- ERROR IF LOGIN ALREADY A USER
    if exists (select * from sys.database_principals where sid = @sid)
    begin
        select @name_in_db = name, @dbname = db_name() from sys.database_principals where sid = @sid
        ROLLBACK TRANSACTION
        raiserror(15278,-1,-1,@loginame,@name_in_db,@dbname)
        return (1)
    end

    -- ALTER NAME TO AVOID CONFLICTS IN NAME SPACE
    select @loginame = '\' + @loginame
    if user_id(@loginame) is not null
    begin
		ROLLBACK TRANSACTION
	    raiserror(15023,-1,-1,@loginame)
        return (1)
    end

    -- INSERT SYSUSERS ROW
	EXEC %%Alias().NewAlias(Name = @loginame, SID = @sid,
		AliasID = @targuid, IsExternal = @isext)	-- may fail

	select @ret = @@error
	
	if @ret <> 0
	begin
		ROLLBACK TRANSACTION
		-- Fail If Certificate Login	
		if @ret = 2
			raiserror(15582, -1, -1)
		else
		-- Fail If Duplicate
	    	raiserror(15023,-1,-1,@loginame)

		return (1)
	end

	COMMIT TRANSACTION

    -- RETURN SUCCESS
    return (0) -- sp_addalias

 
Last revision
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash