Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


create proc sys.sp_MSloginmappings
	@loginname nvarchar(258) = null, @flags int = 0

	create table #loginmappings(
		LoginName  nvarchar(128) NULL,
		DBName     nvarchar(128) NULL,
		UserName   nvarchar(128) NULL,
		AliasName  nvarchar(128) NULL

	 * @flags bits:
	 *		0x01	- current db only
	 * Added @dbname so dbo can see everyone in current database.
	 * Use hacky 4.21 syntax so it will run there, instead of a case..when.
	declare @checkmultilogin int
	select @checkmultilogin = 1
	if ((@flags & 0x01 <> 0) and user_id() = 1)
		select @checkmultilogin = 0

	declare @logincount int
	select @logincount = 0
	if (@loginname is not null)
		select @logincount = count(*) from dbo.syslogins where loginname = @loginname

	/* Gotta be sa or dbo to see other than just current login. */
	declare @numlogins int, @whereloginname nvarchar(258), @name nvarchar(258), @retval int
	if (@loginname is null)
		select @numlogins = 2
		select @numlogins = count(*) from dbo.syslogins where loginname = @loginname

	if (@numlogins = 0) begin
		RAISERROR (15007, -1, -1, @loginname)		/* Login not found */
		return 1
	if (@checkmultilogin <> 0) begin
      /* We do not want to allow everybody to execute this SP */
		if (is_member(N'db_ddladmin') <> 1 and is_member(N'db_owner') <> 1 and is_member(N'db_accessadmin') <> 1 and is_member(N'db_securityadmin') <> 1 and (@numlogins > 1 or suser_sid() <> suser_sid(@loginname))) begin
			RAISERROR (14301, -1, -1, N'')				/* Only sa can see other than the current login */
			return 1
	if (@loginname is not null)
		select @whereloginname = N' and loginname = ' + QUOTENAME(@loginname, '''')
      select @whereloginname = N' '

	 * This proc returns a result set with one or more rows for each database for which a login is a user or aliased to one.
	 * If loginname is specified, the results are limited to that login.  First load a temp table with all logins that are
	 * in a db, then add those which aren't mapped to any db.
	if (@flags & 0x01 <> 0) begin
		INSERT #loginmappings select l.loginname, db_name(), u.name, null from master.dbo.syslogins l, dbo.sysusers u where l.sid = u.sid and l.loginname is not NULL
		 * We only allow multi-db on a 6.x server because dynamic exec() didn't exist before then,
		 * hence there is no way to loop thru every database.  This is caught in SQLDMO so no
		 * need for error message here; we'll just return no result sets.
	end else begin
		exec @retval = sys.sp_MSforeachdb
			N'use [?] INSERT #loginmappings select l.loginname, db_name(), u.name, null from master.dbo.syslogins l, dbo.sysusers u where l.sid = u.sid and l.loginname is not NULL'
		if (@retval <> 0)
			return 1
		insert #loginmappings select l.loginname, null, null, null from master.dbo.syslogins l where l.loginname not in (select LoginName from #loginmappings) and l.loginname is not NULL

	 * Now bring them out by loginname, each in its own result set.
	 * If this is for all logins, we'll return all logins; if for curdb,
	 * only those in #loginmappings (i.e. only those mapped in curdb).
	exec(N'declare hCForEachLogin cursor global for select loginname from master.dbo.syslogins where loginname is not NULL ' + @whereloginname + N' order by loginname')
	if (@@error = 0)
		open hCForEachLogin
	if (@@error <> 0)
		return @@error
	fetch hCForEachLogin into @name
	while (@@fetch_status >= 0) begin
      /* Use '=' instead of 'LIKE' in comparision, so we can handle wide card character correctly */
		if ((@flags & 0x01 = 0) or exists (select * from #loginmappings where LoginName = @name))
			select * from #loginmappings where LoginName = @name
		fetch hCForEachLogin into @name
	end /* FETCH_SUCCESS */
	close hCForEachLogin
	deallocate hCForEachLogin
	return @@error

Last revision 2008RTM

  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash