RENAME OF USERS CHANGE OWNER SHIP

Question

Post reply

RENAME OF USERS CHANGE OWNER SHIP

shiv-356842

SSCarpal Tunnel

Points: 4312
More actions
September 24, 2007 at 10:33 am

#70252

Hi Friends,
I want to change my USER NAME of the USER.
CHANGE THE OWNER SHIP Of the user 'shiv' - 'kumar'
what is command of that one OR GUI how can i change plz explain me
Thx

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 738683 More actions · Answer 1

Which is it? You've asked two things here. Are you changing the user name or the ownership of something.

Lowell SSC Guru Points: 323546 More actions · Answer 2

here's the answer to both questions: change a login name:

--Usage: EXEC pr_RenameLogin 'webdev', 'WebDeveloper'

--modified from from http://www.planet-source-code.com/vb/scripts/ShowCode.asp?txtCodeId=408&lngWId=5

---Code should be put in master database ---

CREATE PROCEDURE pr_RenameLogin (@CurrentLogin sysname, @NewLogin sysname)

AS

BEGIN

DECLARE @SQLState AS VARCHAR(200)

--here's the bad thing: allowing adhoc updates!!!!

IF EXISTS(SELECT [name] FROM master.dbo.sysxlogins WHERE [name] = @CurrentLogin)

BEGIN

EXEC master.dbo.sp_configure 'allow updates', '1'

RECONFIGURE WITH OVERRIDE

--Update user login name in master db

SET @SQLState = 'UPDATE master.dbo.sysxlogins SET [name] = ''' + @NewLogin + ''' WHERE [name] = ''' + @CurrentLogin + ''''

EXEC (@SQLState)

--Update user login name in each db where has access as in in sysusers table

SET @SQLState = 'EXEC master.dbo.sp_MSForEachDB ''UPDATE ?.dbo.sysusers SET [name] = ''''' + @NewLogin + ''''' where [name] = ''''' + @CurrentLogin + ''''''''

EXEC (@SQLState)

--Put the configuration back to normal

EXEC master.dbo.sp_configure 'allow updates', '0'

RECONFIGURE WITH OVERRIDE

PRINT 'User ' + @CurrentLogin + ' Renamed Successfully to ' + @NewLogin

END

ELSE

BEGIN

PRINT 'User ' + @CurrentLogin + ' Not found in the master database. Check your spelling and confirm this is the correct server.'

END

GO

change object owner from one user to another:

select 'EXEC sp_changeobjectowner "' + u.name + '.' + o.name + '","kumar"' from sysobjects o, sysusers u

where type = 'u'

and u.uid = o.uid and u.uid <> 1

and u.name='shiv'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!