How to find user existence

Posted on 2003-12-10
Last Modified: 2007-12-19
Hi all,
i got a problem like i need to write create datebase script,and after creation i need to add login and also add user to that
particular database.
Sp aftre creation of database i am writing following script.


if exists(select * from sysusers where name = @SQL_LOGIN)
    exec sp_dropuser @SQL_LOGIN
if exists(select * from syslogins where name= @SQL_LOGIN)
    exec sp_droplogin @SQL_LOGIN      
    exec sp_addlogin @SQL_LOGIN,@PASSWORD,@DEFDB
    exec sp_adduser @SQL_LOGIN,@SQL_LOGIN

But here the problem is.First i created one database and run the above script, but again i want to create a database
with different name, regarding below condition it need to drop the login and create new login.As that login is dbo
for first database it cannot be dropped.

So i have to write logic like,if login exists i need to check whether that login is dbo for any other database or not.
If yes i have to choose new login else drop login and create with same name.
But i am not getting how to write logic for checking login is dbo for any other database or not.

If you don't understand the question pls ask me again.
Waiting for kind response.
Question by:nathbalaka
  • 2

Accepted Solution

rherguth earned 50 total points
ID: 9923391
It sounds like you want to completely remove the login by making sure it's been removed from all databases, first.  This is a script I found that creates a stored procedure sp_RemoveLogin that does the removal:
The source for the script (requires a free login):

Google cached version:

Use master
if exists (select *
             from dbo.sysobjects
            where id = object_id(N'[dbo].[sp_RemoveLogin]')
              and OBJECTPROPERTY(id, N'IsProcedure') = 1)
   drop procedure [dbo].[sp_RemoveLogin]

Create procedure sp_RemoveLogin
       @name sysname = null
--Name        : sp_RemoveLogin        for SQL 7.0 & 2K
--Description : Attempts to remove a login from a SQL Server whether STD or NT.
--Parameters  : @name - the login to be removed, ie.e, <login> or
--                      <domain>\<login>
--Comments    : Removing login from a SQL server can be a tedious, manual
--              process checking for database access in each database, object
--              ownership in each database, granted permissions (the login is
--              the grantor), jobs & packages owned by the login. This procedure
--              automates the process as much as possible. The following rules
--              are applied when issues are encountered:
--              1) If the login owns databases (as will occur when a restore
--                 is done manually) the ownership is changed to sa.
--              2) If the login is a user in a particlar db and owns objects,
--                 then the proc attempts to reassign ownership to dbo. If an
--                 object by the same name is already owned by dbo a message is
--                 displayed and manual intervention is required.
--              3) If this login as a user in a db has granted permissions then
--                 those permissions are removed.
--              4) Once object ownership is taken care and grants are dropped
--                 then the user can be removed from the db.
--              5) If the user is aliased it is dropped.
--              6) This process continues for each db. Once all dbs are
--                 processed if there were any objects that could not be handle  
--                 without manual intervention a message is displayed to that
--                 effect.
--              7) If the login owns jobs or packages in msdb those are changed
--                 to sa.
--              8) Any open connections the login has are killed and finally the
--                 login is removed from the SQL Server.
--              9) If a session could not be killed a message is displayed to that
--                 effect.
--Date        : 07/02/2001
--Author      : Clinton Herring
--History     :

-- Create a temp holding tables
If (Select object_id('tempdb.dbo.#Parm')) > 0
   Exec ('Drop table #Parm')
Create table #Parm(value int null)

-- Declare variables    
Declare @sid varbinary(85),
        @dbname sysname,
        @cmd varchar(4096),
        @spid int

-- Check for master db
If db_name() <> 'master'
      Print 'This stored procedure must be run from the master database.'

-- Check for a null parameter
IF @name is null
      Print 'This stored procedure requires a valid login as a parameter.'

-- Check for logins not allowed to be dropped using this procedure
IF @name in ('BUILTIN\Administrators', 'distributor_admin', 'sa', 'repl_publisher', 'repl_subscriber')
      Print 'You may not drop the following logins using this stored procedure:'
      Print '   BUILTIN\Administrators, distributor_admin, sa, repl_publisher, repl_subscriber'

-- Check to see if the login exists.
If exists (select * from master.dbo.syslogins where loginname = @name)

      -- Display a message
      Print 'Attempting to find and drop ''' + @name + ''' from each database...'

      -- retrieve the sid of the login
      Set @sid = suser_sid(@name)

      -- Does this login own any databases
      If exists(select * from sysdatabases where sid = @sid)
            Select @cmd = 'use master declare @cmd varchar(512) Exec sp_configure ''allow updates'',1 ' +
                          'Reconfigure with override Waitfor delay ''00:00:01'' ' +
                          'Print ''   Fixing db owner issues in master...'' ' +
                          'Select @cmd = ''Update sysdatabases set sid = 0x01 where sid = suser_sid(''''' + @name + ''''')'' ' +
                          'Exec (@cmd) Exec sp_configure ''allow updates'',0 Reconfigure with override '
            Exec (@cmd)

      -- If the login exists begin checking each database for this login as a users in
      -- that database.
      Select @dbname = min(name) from master.dbo.sysdatabases

      -- Loop through each database.
      While @dbname is not null

            -- Here dynamic sql is required to use the 'Use command'.
            -- This loop checks for db and msdb ownership issues & granted permissions.
            -- Build a command.
            Select @cmd  = 'use ' + @dbname + ' declare @uid int, @cmd varchar(512), @name sysname ' +
                           'If exists (select * from sysusers where sid = suser_sid(''' + @name + ''') and isaliased = 0) ' +
                           'Begin Print ''   Processing db ' + @dbname + '...'' Select @uid = uid, @name = name from ' +
                           'sysusers where sid = suser_sid(''' + @name + ''') If exists (select * from sysobjects ' +
                           'where uid = 1 and name in (select name from sysobjects where uid = @uid)) ' +
                           'Begin Print ''   The following objects are owned by the user in database ' + @dbname + '.'' ' +
                           'Print ''   Objects with the same name owned by dbo already exist. Please decide '' ' +
                           'Print ''   what to do with these objects before attempting to drop this user.'' Print '''' ' +
                           'Select convert(varchar(50), name) ''name'', type from sysobjects where uid = @uid ' +
                           'Insert into #parm values(1) End ' +
                           'Else Begin Exec sp_configure ''allow updates'', 1 Reconfigure with override ' +
                           'waitfor delay ''00:00:01'' select @cmd = ''update sysobjects set uid = 1 where uid = '' ' +
                           '+ convert(varchar(5),@uid) + ' +
                           ''' Delete from syspermissions where grantor = '' + convert(varchar(5),@uid) ' +
                           'Print ''   Fixing object ownership issues in '' + db_name() + ''...'' Exec (@cmd) ' +
                           'Exec sp_configure ''allow updates'', 0 Reconfigure with override ' +
                           'Exec sp_revokedbaccess @name End Print '''' End ' +
                           'If exists(select * from sysusers where suid = suser_id(''' + @name + ''') and isaliased = 1) ' +
                           'Begin Exec sp_dropalias ''' + @name + ''' Print '''' End'                      
            -- Execute the command
            Exec (@cmd)

            -- If the database is msdb then fix any job or package onwership issues.
            If @dbname = 'msdb' and
               (exists(select * from msdb.dbo.sysjobs where owner_sid = @sid) or
                exists(select * from msdb.dbo.sysdtspackages where owner_sid = @sid))
                  Select @cmd = 'use msdb declare @cmd varchar(512) ' +
                                'Exec sp_configure ''allow updates'', 1 Reconfigure with override ' +
                                'waitfor delay ''00:00:01'' select @cmd = ' +
                                '''update sysdtspackages set owner = ''''sa'''', owner_sid = ' +
                                '0x01 where owner_sid = suser_sid(''''' + @name + ''''') ' +
                                'update sysjobs set owner_sid = 0x01 where owner_sid = suser_sid(''''' + @name+ ''''')'' ' +
                                'Print ''   Fixing job &/or package ownership issues in msdb.'' ' +
                                'Exec (@cmd) Exec sp_configure ''allow updates'', 0 Reconfigure with override '
                  Exec (@cmd)

            Select @dbname = min(name) from master.dbo.sysdatabases where name > @dbname
      -- Did we have any issues that could not be resolved?
      If exists(select * from #parm where value = 1)
         Print 'Cannot drop the login at this time.'
            Truncate table #parm

            -- Check for any connection by this login and attempt to kill them.
            If exists (Select * from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null)
                  Insert into #parm Select spid from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null
                  Select @spid = min(value) from #parm
                  While @spid is not null
                        Select @cmd = 'Kill ' + convert(varchar(5),@spid)
                        Exec (@cmd)
                        Select @spid = min(value) from #parm where value > @spid

            -- Not all kill commands succeed; check again
            If exists (Select * from master.dbo.sysprocesses where loginame = @name and sid <> 0x01 and sid is not null)
                  Print 'Could not kill all active sessions for this login.'
                  Print 'Cannot drop the login at this time.'
                  If charindex('\', @name) > 0
                     Exec sp_revokelogin @name
                     Exec sp_droplogin @name

      Print 'The login ''' + @name + ''' does not exist on SQL Server ''' + @@servername + '''.'


Expert Comment

ID: 10240231
Are you going to close this Q?

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Link Quickbooks point of sale to server 12 107
Cassandra Select Query 1 53
Fixing Non-Standard characters in text 8 91
Update from TABLE-A to TABLE-B 5 38
Database tuning – How to start and what to tune. This question is frequently asked by many people, both online and offline. There is no hard and fast rule-of-thumb for performance tuning, however, before beginning the tuning process one should a…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now