Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2002
    Posts
    14

    Unanswered: Updating SQL Logins

    With Transact-SQL, I would like to change all SQL login names with the naming convention of ‘CHICAGO\QCP’ and with the default database type of ‘master’ to Default Database of ‘heat601’, Database Access of ‘heat601’ and Database Role as db_datareader and db_datawriter.

    What stored procedures or functions can be use to accomplish the above task?

  2. #2
    Join Date
    Jul 2002
    Posts
    229
    Please read the whole text before trying anything...

    1. Logins - can probably be done in T-SQL

    The logins are in database master, system table sysxlogins. (Note: BOL specifies a system table called syslogins with different contents than sysxlogins. The below info is based on our shop, where we have sysxlogins.)

    You can update the Default Database value for users like below, but I'd suggest not doing it this way unless you're an experienced user. Backup the master database first. This requires switching the 'allow updates' option on at the server. Do a Reconfigure after. REMEMBER to reset this setting + new reconfigure afterwards. Also, remember that SP's created when it was switched on still will still be enabled to modify system tables, according to BOL, after option is switched off again. Replace the "1" with the database code of heat601, or fetch the code from the sysdatabases table.

    use Master
    GO
    update sysxlogins
    set dbid = 1
    where name like 'CHICAGO/QCP%'
    GO


    2. Database access - can't and/or shouldn't be done in T-SQL

    To give a user access to Heat601 as db_datareader and db_datawriter, you need to add records to the system table Sysmembers, in the Heat601 database, but you're not allowed to update that table "manually" - at least, I can't find any way to get allowed to do.

    In theory, you would do something like (untested!):

    use Heat601
    GO

    insert into Sysmembers
    select s1.uid , s2.name
    from sysusers s1 , sysusers s2
    where s1.name like 'QCP%'
    and (s2.name = 'db_datareader' or s2.name = 'db_datawriter')

    (You could also fetch the user name from Sysxlogins in master and include a join
    between that table and HEat601's Sysusers table on the SID column. Don't know
    if it matters at all. )

    The above assumes the user already exists for the database. If it doesn't, you need to first insert the user first in Sysusers, with lots of data like password, SID etc, which you need to fetch from other tables
    (mostly from master.dbo.sysxlogins). Don't know how to correctly set all column values, and how to find out the new user's Uid value.

    Maybe you shouldn't try doing it this way...?
    Maybe there are commercial tools available?
    Last edited by Coolberg; 11-15-02 at 10:28.

  3. #3
    Join Date
    Oct 2002
    Posts
    369

    Exclamation Re: Updating SQL Logins

    Originally posted by Terry Ward
    With Transact-SQL, I would like to change all SQL login names with the naming convention of ‘CHICAGO\QCP’ and with the default database type of ‘master’ to Default Database of ‘heat601’, Database Access of ‘heat601’ and Database Role as db_datareader and db_datawriter.

    What stored procedures or functions can be use to accomplish the above task?

    Q1 [What stored procedures or functions can be used to manage login and integrated security?]
    A1 Generally, You can do everything you need using provided special stored procedures. It may seem like more work than directly addressing system tables, however, system tables may be changed (breaking your code) without any notice (for example, by the application of a Sql Server service pack, hotfix, etc.). If you are performing a onetime 'fix' you might choose to access and manipulate system tables directly anyway; but generally, using the provided sp_ procs for security will more readilly guarantee any stored procedures and / or scripts you create will work predictably and reliably in the future.

    Security Procedures:
    (NOTE some are from previous versions of Sql Server, and may not exist / function forever)

    sp_addalias
    sp_addapprole
    sp_addgroup
    sp_addlinkedsrvlogin
    sp_addlogin
    sp_addremotelogin
    sp_addrole
    sp_addrolemember
    sp_addserver
    sp_addsrvrolemember
    sp_adduser
    sp_approlepassword
    sp_change_users_login
    sp_changedbowner
    sp_changegroup
    sp_changeobjectowner
    sp_dbfixedrolepermission
    sp_defaultdb
    sp_defaultlanguage
    sp_denylogin
    sp_dropalias
    sp_dropapprole
    sp_dropgroup
    sp_droplinkedsrvlogin
    sp_droplogin
    sp_dropremotelogin
    sp_droprole
    sp_droprolemember
    sp_dropserver
    sp_dropsrvrolemember
    sp_dropuser
    sp_grantdbaccess
    sp_grantlogin
    sp_helpdbfixedrole
    sp_helpgroup
    sp_helplinkedsrvlogin
    sp_helplogins
    sp_helpntgroup
    sp_helpremotelogin
    sp_helprole
    sp_helprolemember
    sp_helprotect
    sp_helpsrvrole
    sp_helpsrvrolemember
    sp_helpuser
    sp_MShasdbaccess
    sp_password
    sp_remoteoption
    sp_revokedbaccess
    sp_revokelogin
    sp_setapprole
    sp_srvrolepermission
    sp_validatelogins

  4. #4
    Join Date
    Jul 2002
    Posts
    229

    Re: Updating SQL Logins

    Sorry - I completely forgot about the right approach, i.e. searching for SP's before trying to attack the system tables directly.
    My fault.

  5. #5
    Join Date
    Oct 2002
    Posts
    369

    Wink Re: Updating SQL Logins


    Sorry - I completely forgot about the right approach, i.e. searching for SP's before trying to attack the system tables directly.
    My fault.
    I fully agree with Coolberg; (in my opinion) the "right approach" in a production environment is (almost always) to make every reasonable attempt possible to use the provided special stored procedures (before resorting to more dangerous tactics and methods). No apology is necessary, though.


    An apt real world analogy might be having a mechanical breakdown in your car on an 8 lane highway (no cell phones) with:

    i A telephone call box immediatly on the OTHER side of the highway, but;
    ii The nearest phone / exit (on YOUR SIDE) a three hour walk away.

    ==> In the analogy the question becomes:
    Do you walk the three hours on your side (and summon help with the minnimum personal danger); or do you risk quite possibly becomming a "Human Road Pizza" and try to cross the 8 lanes to use the call box immediatly on the OTHER side of the highway???

  6. #6
    Join Date
    Nov 2002
    Posts
    14

    List\View Active Login Connections

    How do I list/view active logins in SQL Server 2000 using the Profiler?

    Thank you!

  7. #7
    Join Date
    Feb 2002
    Posts
    2,232
    I would use sp_who or go to management -> current activity under em. But you can use profiler - use the events -> sessions -> existingconnection. Are you trying to trace something or you just want a snapshot of current activity ?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •