Post reply

how to create a sql login and add to database user which already exists

  • November 2, 2012 at 12:09 am

    #284141

    hi everyone ,

    how to create a sql login and add to database user which already exists

    1. user "appp" is already exists in the datasbase "real" which is restored from old backup after fresh sql server installation

    2. now we want to create the login "appp" [with sqlserver authnetication ] and give access to the database "real" with server role = bulkadmin and database role =public

    can any one give script for this ????

    Thanks
    Naga.Rohitkumar

  • November 2, 2012 at 2:30 am

    #1555319

    Create the login using CREATE LOGIN

    Then use ALTER USER in the database in question to re-map the user to the login

    Then use sp_addrolemember / sp_addsrvrolemember / sp_droprolemember / sp_dropsrvrolememeber to add remove the login/user to the nessesary DB or server level roles

  • November 5, 2012 at 4:26 am

    #1555958

    if u dont mind can u expalin with detaled script

    Thanks
    Naga.Rohitkumar

  • November 5, 2012 at 4:42 am

    #1555966

    IF EXISTS (SELECT 1 FROM sys.database_principals WHERE name ='USerName')

    DROP USER [USerName]

    CREATE USER [USerName] FOR LOGIN [LoginName]

    EXEC sp_addrolemember 'db_datareader','USerName'

    You can also refer MSDN forums for user permissons

    http://www.mssqltips.com

  • November 5, 2012 at 6:24 am

    #1556016

    CREATE LOGIN [appp]

    WITH PASSWORD=N'NotTheRealPassword'

    MUST_CHANGE,

    DEFAULT_DATABASE=[master],

    CHECK_EXPIRATION=ON,

    CHECK_POLICY=ON;

    USE real;

    GO

    ALTER USER [appp] WITH LOGIN = [appp];

    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!

  • November 12, 2012 at 2:07 am

    #1558055

    a new login will have different SID from the existing user "appp" So to

    Re-maps that user to new login by changing the user's Security Identifier (SID) to match the login's SID. which is done by ALTER USER command

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • December 11, 2012 at 12:42 am

    #1567272

    can plz tell , how will u change sid of particular login

  • December 11, 2012 at 1:50 am

    #1567283

    Google sp_help_revlogin, it will genereate the create script with the particular SID, then you just need to run it on your secondary server.

  • December 11, 2012 at 5:30 am

    #1567392

    It's deprecated but still usable in SQL Server 2008 R2 and it's a one liner. As follows

    USE [real]

    GO

    EXEC sys.sp_change_users_login 'Auto_Fix', 'appp', NULL, 'somepassword'

    GO

    Then just use

    EXEC sp_addsrvrolemember 'appp', 'bulkadmin'

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • December 11, 2012 at 9:14 am

    #1567499

    you can also use, in case u want give dbowner role

    use database name

    sp_changedbowner 'login name'

Viewing 10 posts - 1 through 9 (of 9 total)

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