Post reply

Sa Profile

  • December 1, 2004 at 11:41 am

    #86097

    Hi everyone, I'm new in sql server and i need How can I get all users that has sa profile?, I want to know in different Sql Servers data bases the users with sa profile.

    Thanks a lot.

     

    Sincerely

    er_sql

     

     

     

     

     

  • December 1, 2004 at 12:44 pm

    #532096

    If you are talking about the sa or System Admin role, then run this proc in query analyzer: sp_helpsrvrolemember

     

    Hope this helps,

    Kathi

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • December 1, 2004 at 12:58 pm

    #532098

    Yes I want to know how many users in data bases are login in with sa role, some visual basic development are running and connecting to sql server and I worried if they are using the sa role.

    Thanks

     

  • December 1, 2004 at 1:09 pm

    #532102

    OK, now I understand.  Here is a stored proc that you can use to watch for sa or any other account.  I created a new database for the two tables and the proc, and then set up a job to run the proc.  You may want to modify this for your use.  It doesn't save duplicates.

     

    CREATE  procedure usp_FindAccountUse @account varchar(20), @systemDBs char(1)= 'N' as

    --********************************************************************

    -- Proc: usp_FindAccountUse

    -- Author: Kathi Kellenberger

    -- Created: 1/20/04

    -- Purpose: Use to keep track of the use of an account.

    -- Parameters:

    --  @account -- the account to track

    --  @systemDBs -- 'Y' if want to track system db use

    --       'N' (the default) if don't want to track

    -- Notes:

    -- Requires two tables

    -- create table save_who (spid int, ecid int, status varchar(30),

    --                        loginname varchar(20), hostname varchar(20),

    --                        blk int, dbname varchar(40), cmd varchar(16),

    --        recorded dateTime)

    -- create table temp_who (spid int, ecid int, status varchar(30),

    --                        loginname varchar(20), hostname varchar(20),

    --                        blk int, dbname varchar(40), cmd varchar(16))

    --**********************************************************************

     print 'delete old work table'

     delete from temp_who where loginname = @account

     

     print 'save results of sp_who into work table'

     insert into temp_who exec sp_who

     print 'insert new records into save_who'

     if @systemDBs = 'N'

      begin

       print 'inserting all records except for system dbs'

       insert into save_who (spid, ecid, status, loginname, hostname, blk, dbname, cmd, recordKey)

       select *, convert(varchar,spid) + '-' + loginname + '-' + hostname + '-' + isnull(dbname,'none')

       from temp_who

       where convert(varchar,spid) + '-' + loginname + '-' + hostname + '-' + isnull(dbname,'none') not in (select isnull(recordKey,'') from save_who)

       and loginname = @account

       and dbname not in ('master','msdb')

      end

     else

      begin

       print 'inserting all records'

       insert into save_who (spid, ecid, status, loginname, hostname, blk, dbname, cmd, recordKey)

       select *, convert(varchar,spid) + '-' + loginname + '-' + hostname + '-' + isnull(dbname,'none')

       from temp_who

       where convert(varchar,spid) + '-' + loginname + '-' + hostname + '-' + isnull(dbname,'none') not in (select isnull(recordKey,'') from save_who)

       and loginname = @account

      end

     

     

    GO

    Aunt Kathi Data Platform MVP
    Author of Expert T-SQL Window Functions
    Simple-Talk Editor

  • December 1, 2004 at 4:22 pm

    #532119

    Ok, thanks a lot for your help.

Viewing 5 posts - 1 through 4 (of 4 total)

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