Makale numaras�: 889696 - Son G�zden Ge�irme: 02 Kas�m 2007 Cuma - G�zden ge�irme: 1.3

SQL Server �rne�ini bir�ok veritaban� varolan bir SQL Server Enterprise Manager'den yava� yan�t kar��la�abilirsiniz

�ngilizce ve T�rk�e'yi yan yana g�r�nt�leme

Buraya t�klayarak �ngilizce ve T�rk�e'yi yan yana g�r�nt�leyebilirsiniz.

Otomatik terc�me makalelerin ne oldu�unu a��klayan yaz�y� okumak i�in buraya t�klay�n

Bu makalenin ge�erli oldu�u �r�nleri g�r�n.

Sistem �pucuBu makale, kulland��n�zdan farkl� bir i�letim sistemine y�neliktir. Sizinle ilgili olmayabilecek makale i�eri�i devre d�� b�rak�ld�.

Hepsini a� | Hepsini kapa

Belirtiler

Bir�ok veritaban� i�in bir varsay�lan �rne�i veya Microsoft SQL Server'�n adland�r�lm�� bir �rne�i var, �zellikle, Databases klas�re ula�mak i�in gelen, SQL Server Enterprise Manager yava� yan�t kar��la�abilirsiniz. Birden fazla bin veritabanlar�n�n Databases</a1> klas�r� varsa, be� dakikadan fazla olan gecikmeler ya�an�r.

�ste

Neden

SQL Server Enterprise Manager her veritaban� eri�ilebilirli�ini belirlemek i�in sp_MSdbuseraccess sakl� yordam� �a��r�r. Her bir SQL veritaban�nda kar�� sonra sp_MSdbuseraccess sakl� yordam� �al��t�r�r Server veritaban�ndaki kullan�c� �ubu�undaki izin d�zeyini belirleyin. Veritabanlar�n�n x numaral� sp_MSdbuseraccess sakl� yordam� �al��t�r�r x + 1 kez. Bu birden �ok arama tamamlanmas� birka� dakika s�rebilir. Me�gul olan bir sistem bitirmek i�in daha uzun s�rmesine aramalar yapabilirsiniz.

�ste

��z�m

Bu makalede, de�i�tirilmi� bir s�r�m� sp_MSdbuseraccess depolanm�� yordam i�erir. De�i�tirilmi� s�r�m�n� sp_MSdbuseraccess sakl� yordam�, kullan�c�n�n sahip oldu�u izinler, t�m veritabanlar�n�n sp_MSdbuseraccess sakl� yordam� �al��t�rmak yerine, yaln�zca bu veritabanlar�nda sp_MSdbuseraccess depolanan yordam�n� �al��t�rarak sorunu ��zebilirsiniz. Daha fazla kullan�c� kullan�c� kazanmak i�in k��k bir performans eri�ebilece�i veritabanlar�n�n de�i�tirilmi� s�r�m�n� sp_MSdbuseraccess sakl� yordam�n� kullanarak eri�ir.

A�a��daki bilgiler sp_MSdbuseraccess depolanm�� yordam�n de�i�tirilmi� s�r�m� i�in ge�erlidir:

Kullan�c� yaln�zca veritabanlar�n�n, SQL Server Enterprise Manager'da eri�iminizin oldu�unu da g�rebilirsiniz. A�a��daki ko�ullardan biri do�ru oldu�unda kullan�c� veritaban�na eri�ebilir:
- Exepsp_grantdbaccess sakl� yordam�n� kullanarak, kullan�c� bir veritaban�na eri�imi verildi.
- Veritaban�, bir konuk hesab� i�erir.
Bir�ok veritaban� eri�imi olan kullan�c�lar i�in yan�tlama saatinin SQL Server Enterprise Manager artabilir.

�ste

Daha fazla bilgi

De�i�tirilmi� olarak sp_MSdbuseraccess depolanan yordam�n kodu i�in �u ad�mlar� izleyin:

Copy the following code:

/*******************************************************************************/
/* exec sp_MSdbuseraccess 'perm', 'dbname' -- selecting priv bit from specified database                       */
/* exec sp_MSdbuseraccess 'db', 'dbname'   -- select databases, must change the database if dbname is specified */
/* exec sp_MSdbuseraccess 'init', 'dbname' -- noop                                                       */
/*******************************************************************************/
print N''
print N'Dropping sp_MSdbuseraccess'
print N''
go
if exists (select * from master.dbo.sysobjects where (OBJECTPROPERTY(id, N'IsProcedure') = 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name = N'sp_MSdbuseraccess')
	drop procedure sp_MSdbuseraccess
go

print N''
print N'Creating sp_MSdbuseraccess'
print N''
go
create proc sp_MSdbuseraccess
	@mode nvarchar(10) = N'perm', @qual nvarchar(128) = N'%'
as
   set deadlock_priority low
   
   create table #TmpDbUserProfile (
      dbid        int NOT NULL PRIMARY KEY,
      accessperms int NOT NULL
      )

   create table #TmpOut (
      name        nvarchar(132) NOT NULL,
      version     smallint,
      crdate      datetime,
      owner       nvarchar(132),
      dbid        smallint NOT NULL,
      status      int,
      category    int,
      status2     int,
      fulltext    int,
      )

   set nocount on

   declare @accessbit int
	if (lower(@mode) like N'perm%') begin
      /* verify */
      declare @id int, @stat int, @inval int
      select @id = dbid, @stat = status from master.dbo.sysdatabases where name = @qual
      if (@id is null) begin
         RAISERROR (15001, -1, -1, @qual)
         return 1
      end

      /* Can we access this database? */
      declare @single int
      select @single = DATABASEPROPERTY( @qual, N'issingleuser' )
/*      if ((@single <> 0) or ((@stat & SQLDMODBStat_Inaccessible) <> 0)) begin  */
      if ((@single <> 0) or
         (DATABASEPROPERTY(@qual, N'isdetached') <> 0) or
         (DATABASEPROPERTY(@qual, N'isshutdown') <> 0) or
         (DATABASEPROPERTY(@qual, N'issuspect') <> 0) or
         (DATABASEPROPERTY(@qual, N'isoffline') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinload') <> 0) or
         (DATABASEPROPERTY(@qual, N'isinrecovery') <> 0) or
         (DATABASEPROPERTY(@qual, N'isnotrecovered') <> 0)) begin
         select @inval = 0x80000000
         select @inval
         return 0
      end
      select @accessbit = has_dbaccess(@qual)
      if ( @accessbit <> 1) begin
         select @inval = 0x40000000
         select @inval
         return 0
      end

      /** We can access this database, and we must locate the specified database to get the priv bit **/
      declare @dbTempname nvarchar(258)
      declare @tempindex int
      SELECT @dbTempname = REPLACE(@qual, N']', N']]')
      exec (N'[' + @dbTempname + N']' + N'..sp_MSdbuserpriv ')
      return 0
   end

   /* If 'db', we want to know what kind of access we have to the specified databases */
   /* If we are not in the master database, we are selecting a single database, and we want to correct role bit to save round trip */
   if (lower(@mode) like N'db%') begin
      /*  Make sure that you are in either the master database or the current database, so that you do not affect other databases. */
      declare @dbrole int
      select @dbrole = 0x0000

      if (db_id() <> 1)
         select @qual = db_name()

      /* If dbname contains a single quotation mark ('), double the single quotation mark for the cursor because the cursor statement is inside two single quotation marks ('').  */
      declare @qual2 nvarchar(517)
      SELECT @qual2 = REPLACE(@qual, N'''', N'''''')

      /* The preprocessor will not replace information within quotation marks, and we have to use the str function. */
      declare @invalidlogin nvarchar(12)
      select @invalidlogin = ltrim(str(convert(int, 0x40000000), 11))
      declare @inaccessible nvarchar(12)
      select @inaccessible = ltrim(str(convert(int, 0x80000000), 11))

      /* We cannot 'use' a database that has a version that is less than the minimum version. */
      /* The SQL Server 6.0 version minimum is 406; the SQL Server 6.5 version minimum is 408.  The SQL Server 7.0  version is 408; however,  it might change later. */
      declare @mindbver smallint
      if (@@microsoftversion >= 0x07000000)
         select @mindbver = 408
      else
         select @mindbver = 406

      /* Select all matching databases -- we want an entry even for the inaccessible databases. */
      declare @dbid smallint, @dbidstr nvarchar(12), @dbstat int, @dbname nvarchar(258), @dbver smallint
      declare @dbbits int, @dbbitstr nvarchar(12)

      /* !!! If the database name contains a left bracket ([), the LIKE operator cannot find the name because the LIKE operator treats a left bracket  as a wildcard character. */
      /* !!! If the @qual2 variable is a percent sign (%), the equal (=) operator does not work. */
      declare @temp int
      select @tempindex = charindex(N'[', @qual2)
      if (@tempindex <> 0)
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master.dbo.sysdatabases where name = N''' + @qual2 + N'''')
      else
         exec(N'declare hCdbs cursor global for select name, dbid, status, version from master.dbo.sysdatabases where name like N''' + @qual2 + N'''')

      open hCdbs

      /* Loop for each database; if the database can be accessed, recursively call ourselves to add the database. */
      fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      while (@@fetch_status >= 0) begin
         /* The preprocessor will not replace information within quotation marks, and we have to use the str function. */
         select @dbidstr = ltrim(str(convert(int, @dbid)))

         /* If the database is a single user database and there is an entry for it in sysprocesses that is not us, we cannot use it. */
         declare @single_lockedout int
         select @single_lockedout = DATABASEPROPERTY( @dbname, N'issingleuser' )
         if (@single_lockedout <> 0)
            select @single_lockedout = 0 where not exists
               (select * from master.dbo.sysprocesses p where dbid = @dbid and p.spid <> @@spid)

         /* First, see if the database can be accessed (not in load, not in recovery, not offline, not in single-use with another user besides us, and so on.) */
/*         if ((@single_lockedout <> 0) or ((@dbstat & SQLDMODBStat_Inaccessible) <> 0) or (@dbver < @mindbver)) begin   */
         if ((@single_lockedout <> 0) or
            (@dbver < @mindbver) or
            (DATABASEPROPERTY(@dbname, N'isdetached') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isshutdown') <> 0) or
            (DATABASEPROPERTY(@dbname, N'issuspect') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isoffline') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinload') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isinrecovery') <> 0) or
            (DATABASEPROPERTY(@dbname, N'isnotrecovered') <> 0) ) begin
            /* Inaccessible, but we can set dbo if we are an sa or if the suser_id function is the database owner sid. */
            exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @inaccessible + N')')
            end
         else begin
            /* Determine whether the current user has access to the database. */
            select @accessbit = has_dbaccess(@dbname)
            if ( @accessbit <> 1) begin
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @invalidlogin + N')')
               end
            else begin
               /* The current user does have access to this database, and we are not trying to obtain priv at this point. */
               select @dbbits = 0x03ff
               select @dbbitstr = ltrim(convert(nvarchar(12), @dbbits))
               exec (N'insert #TmpDbUserProfile values (' + @dbidstr + N', ' + @dbbitstr + N')')
               end
            end

         fetch hCdbs into @dbname, @dbid, @dbstat, @dbver
      end /* while FETCH_SUCCESS */
      close hCdbs
      deallocate hCdbs

      /* Select sysdatabases information in the temp table first to avoid a deadlock in the restore process. */
      if (@tempindex <> 0)
         insert #TmpOut
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status, o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
            from master.dbo.sysdatabases o where o.name = @qual
      else
         insert #TmpOut
         select o.name, o.version, o.crdate, suser_sname(o.sid), o.dbid, o.status, o.category, o.status2, DatabaseProperty(o.name, N'isfulltextenabled')
            from master.dbo.sysdatabases o where o.name like @qual

      /* 1. If on all databases,  dbrole is dummy, we must obtain it later. */
      /* 2. Do not double the single quotation mark (') characters in the database name. */
      /* 3. To speed up the connection, the accessperms column only indicates whether the user can access the database. The column does not contain */
      /*    permission information. We will retrieve the permission information by using the sp_MSdbuserpriv stored procedure when we need that information. */
      /* !!! If the name contains a left bracket ('[), the LIKE operator cannot find it because the LIKE operator treats a left bracket  as a wildcard character. */
      /* !!! If the @qual2 variable is a percent sign (%), the equal (=) operator does not work. */
      if (@tempindex <> 0)
         select o.name, o.version, o.crdate, o.owner, o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, o.fulltext, o.status2,
            collation = convert(sysname, databasepropertyex(o.name, N'collation'))
            from #TmpOut o left outer join #TmpDbUserProfile t on t.dbid = o.dbid 
	where o.name = @qual and (t.accessperms & 0x40000000 = 0) order by o.name
      else
         select o.name, o.version, o.crdate, o.owner, o.dbid, lSize = 0, NonDbo = 0, Status = o.status, spaceavail = 0,
            LogOnSepDev = 1, o.category, t.accessperms, @dbrole, o.fulltext, o.status2,
            collation = convert(sysname, databasepropertyex(o.name, N'collation'))
            from #TmpOut o left outer join #TmpDbUserProfile t on t.dbid = o.dbid 
		where o.name like @qual and (t.accessperms & 0x40000000 = 0) order by o.name

      DROP TABLE #TmpDbUserProfile
      DROP TABLE #TmpOut
      return 0
   end
go
/* End sp_MSdbuseraccess */

exec sp_MS_marksystemobject sp_MSdbuseraccess
go
grant execute on sp_MSdbuseraccess to public
go

SQL Query Analyzer'� a��n ve sonra kodu, bo� bir sorgu ��z�mleyicisi penceresine yap��t�r�n.
EXECUTE komutu SQL Server veritabanlar�na kar�� de�i�tirilmi� sp_MSdbuseraccess sakl� yordam� �al��t�rmak i�in kullan�n.

Not Bu kod de�i�tirilmi� sp_MSdbuseraccess depolanm�� yordam�n sp_MS_marksystemobject sakl� yordam�n� kullanarak bir sistem nesnesi olarak i�aretler.

Bu kod uygulamak ve daha sonra karar varsay�lan davran��a geri d�nmek, a�a��daki ad�mlar� izleyin:

Sp_MSdbusreaccess depolanm�� yordam�n �zg�n CREATE PROCEDURE komut dosyas�n� bulun.

Not Komut dosyas�, genellikle Sqldmo.sql bulunur Program Files\Microsoft SQL Server\MSSQL\Install klas�r�ndeki bir dosya.
Sqldmo.sql a�mak dosyas� SQL Query Analyzer'�.

Komut dosyas�nda a�a��daki toplu i�i bulun ve ge�erli s�r�m� sp_MSdbuseraccess depolanm�� yordam�n b�rakmak i�in toplu i� �al��t�r�n:

if exists
				(select * from master.dbo.sysobjects where (OBJECTPROPERTY(id, N'IsProcedure')
				= 1 or OBJECTPROPERTY(id, N'IsExtendedProc') = 1) and name =
				N'sp_MSdbuseraccess') drop procedure sp_MSdbuseraccess go

"I�lem sp_MSdbuseraccess yarat ile" ba�layan dize i�in arama
Ba�layan toplu i� olarak �al��t�rmak "i�lem sp_MSdbuseraccess olu�turma", SQL Query Analyzer'�n �zg�n yordam�n� yeniden olu�turun.

�ste

Bu makaledeki bilginin uyguland�� durum:

Microsoft SQL Server 2000 Personal Edition
Microsoft SQL Server 2000 Standard Edition
Microsoft SQL Server 2000 Developer Edition
Microsoft SQL Server 2000 Enterprise Edition

�ste

kbmt kbprb kbtshoot KB889696 KbMttr

�ste

Otomatik Terc�me

�NEML�: Bu makale, bir ki�i taraf�ndan �evrilmek yerine, Microsoft makine-�evirisi yaz�l�m� ile �evrilmi�tir. Microsoft size hem ki�iler taraf�ndan �evrilmi�, hem de makine-�evrisi ile �evrilmi� makaleler sunar. B�ylelikle, bilgi bankam�zdaki t�m makalelere, kendi dilinizde ula�m�� olursunuz. Bununla birlikte, makine taraf�ndan �evrilmi� makaleler m�kemmel de�ildir. Bir yabanc�n�n sizin dilinizde konu�urken yapabilece�i hatalar gibi, makale; kelime da�arc��, s�z dizim kurallar� veya dil bilgisi a��s�ndan yanl��lar i�erebilir. Microsoft, i�eri�in yanl�� evrimi veya onun m��teri taraf�ndan kullan�m�ndan do�an; kusur, hata veya zarardan sorumlu de�ildir. Microsoft ayr�ca makine �evirisi yaz�l�m�n� s�k�a g�ncellemektedir.

Makalenin �ngilizcesi a�a��daki gibidir:889696� (http://support.microsoft.com/kb/889696/en-us/ )

�ste

This site in other countries/regions:

SQL Server �rne�ini bir�ok veritaban� varolan bir SQL Server Enterprise Manager'den yava� yan�t kar��la�abilirsiniz

Belirtiler

Neden

��z�m

Daha fazla bilgi

Bu makaledeki bilginin uyguland�� durum:

Anahtar Kelimeler:�

Di�er Destek Siteleri

Topluluk

Hemen Yard�m Al

Makale �evirileri

�lgili Destek Merkezleri

This site in other countries/regions:

SQL Server �rne�ini bir�ok veritaban� varolan bir SQL Server Enterprise Manager'den yava� yan�t kar��la�abilirsiniz

Belirtiler

Neden

��z�m

Daha fazla bilgi

Bu makaledeki bilginin uyguland��� durum:

Anahtar Kelimeler:�

Di�er Destek Siteleri

Topluluk

Hemen Yard�m Al

Makale �evirileri

�lgili Destek Merkezleri

Bu makaledeki bilginin uyguland�� durum: