Post reply

Need help for CREATE USER. User should be user-mapped to ALL existing databasens.

  • January 4, 2011 at 6:52 am

    #388547

    Hi everyone...

    We have implementet SQL management pack for SCOM. This requries there is following setup on EVERY instance:

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

    To configure the instances for monitoring in SQL Server Management Studio

    1.In SQL Server Management Studio, create a login for “SQLMPLowPriv” on all SQL Server instances to be monitored on the agent machine, and grant the following permissions to each “SQLMPLowPriv” login:

    a.VIEW ANY DEFINITION

    b.VIEW SERVER STATE

    2.Create a “SQLMPLowPriv” user that maps to the “SQLMPLowPriv” login in each existing user database, master, msdb, and model. By putting user in the model database, it will automatically create a “SQLMPLowPriv” user in each future user-created database. See the code sample below. You will need to manually provision the user for attached and restored databases.

    3.Add the SQLMPLowPriv user on msdb to the “SQLAgentReaderRole” database role.

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

    As it is today, we have around 200 instances, with different databases.

    How is the smartest way to create this SQLMPLowPriv security login into every instance, and make sure it does a user-mapping on all existing databases?... i know that when he is user-mapped to 'model' db, then future databases inherets this security.

    Should i script my selfe out of it and run the script on every instances (i have them in my Registred Servers). For example i know "select name from master..sysdatabases" query shows the databases... maybe create user script with this combined?...

    please help!

    thanks in advance.

  • January 4, 2011 at 6:52 am

    #1268910

    heres a link for the microsoft scom sql user:

    http://technet.microsoft.com/en-us/library/dd767431.aspx

  • January 4, 2011 at 7:31 am

    #1268944

    You'll have to script it and execute it on each instances.

    You can use this script to get started:Select '

    USE [master]

    GO

    CREATE LOGIN [SQLMPLowPriv] FROM WINDOWS WITH DEFAULT_DATABASE=[master];

    GRANT VIEW ANY DEFINITION TO [SQLMPLowPriv];

    GRANT VIEW SERVER STATE TO [SQLMPLowPriv];

    GO

    '

    UNION ALL

    Select '

    USE [' + name + ']

    GO

    CREATE USER [SQLMPLowPriv] FOR LOGIN [SQLMPLowPriv];

    GO

    '

    From sys.databases Where state = 0

  • January 4, 2011 at 7:36 am

    #1268951

    so far i get this...

    Msg 15407, Level 16, State 1, Line 1

    'SQLMPLowPriv' is not a valid Windows NT name. Give the complete name: <domain\username>.

    Msg 15151, Level 16, State 1, Line 2

    Cannot find the login 'SQLMPLowPriv', because it does not exist or you do not have permission.

    Msg 15151, Level 16, State 1, Line 3

    Cannot find the login 'SQLMPLowPriv', because it does not exist or you do not have permission.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '

    UNION ALL

    Select '.

    Msg 911, Level 16, State 1, Line 4

    Database '' + name + '' does not exist. Make sure that the name is entered correctly.

    Msg 15007, Level 16, State 1, Line 1

    'SQLMPLowPriv' is not a valid login or you do not have permission.

    Msg 105, Level 15, State 1, Line 1

    Unclosed quotation mark after the character string '

    From sys.databases Where state = 0

    '.

    i did not mention the user is on domain named: PLYS\SQLMPLowPriv

  • January 4, 2011 at 7:38 am

    #1268954

    oh sorry, it is a security group in the domain. named PLYS\SQLMPLowPriv

  • January 4, 2011 at 7:41 am

    #1268958

    OK, so modify the script accordingly and it should work.

    John

  • January 4, 2011 at 7:43 am

    #1268961

    yeah 🙂 ... think i got it working... but,, the user is not shown in my security login... how come?

    i can see its in every db under security - users.

  • January 4, 2011 at 8:04 am

    #1268979

    and when i create the user PLYS\SQLMPLowPriv from GUI, i dont get any errors.

    hmm.... seems like the script doesnt work entirely?...

  • January 4, 2011 at 8:14 am

    #1268989

    I'm afraid I don't know what you mean. Are you getting any error messages when you run the script?

    Are you saying that you run the script, and then when you check in the GUI, the logins and/or users don't exist? If that's the case, you probably just need to use Refresh.

    John

  • January 4, 2011 at 8:19 am

    #1268992

    hi john,

    ok.. when i ran the script first time, it created the user in every DB. so far so good. but it did not show at all in my security login for the instance. (yup i did refresh couple of times and log out and in)... So I tried to add the user via GUI.. it didnt complain!,, thats weird?!..

    anyways.. now i deleted the user in model database..

    When i run the script, my result windows output is:

    USE [master] GO CREATE LOGIN [PLYS\SQLMPLowPriv] FROM WINDOWS WITH DEFAULT_DATABASE=[master]; GRANT VIEW ANY DEFINITION TO [PLYS\SQLMPLowPriv]; GRANT VIEW SERVER STATE TO [PLYS\SQLMPLowPriv]; GO

    USE [master] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [tempdb] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [model] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [msdb] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [ekspo_phi_pnn] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [ekspo_phi_pnn_PROD] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [Ekspo_phi_pnn_PROD_L60] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [Ekspo_PNN_PHI] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [Elarkiv_indexes] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [FladfilImport] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [FladfilImport2] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [FladfilImport2_L60] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [FladfilImport2_Staging] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [phi_konto] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [PHI_konto_PROD] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [phi_medl1_PROD_L60] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [phi_medl1_PROD_L70] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [phi_medl1] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [PNN_konto_PROD] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_konto_PROD_L60] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_konto_PROD_L70] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_konto] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [PNN_medl1_PROD] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_medl1_PROD_L60] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_medl1_PROD_L70] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [PNNPHI_statdata] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [PROD_Elarkiv_indexes] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [pnn_medl1] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    USE [test] GO CREATE USER [PLYS\SQLMPLowPriv] FOR LOGIN [PLYS\SQLMPLowPriv]; GO

    But now,, no users are added in either database!... neither any databases (model for example,, and yet another db).. And it again is not created as a security login... hmmmm

  • January 4, 2011 at 8:26 am

    #1268997

    Mmmm... do you understand that you have to copy your query output, paste it back into the query window and run it in order for the logins and users to be created? That's the only reason I can think of that it's not working.

    John

  • January 4, 2011 at 8:40 am

    #1269016

    hehe.. embarrising 🙂 yea know i see it ... thank you very very much!! I bow greatly 😀

    regards arash from denmark.

  • January 4, 2011 at 8:46 am

    #1269023

    and if i want to create a stored procedure for this... where i can put in a variable for the domain..

    for example,, i want to type in PLYS... so it creates PLYS\SQLMPLowPriv.

    and after that,, the script should automatically take the output and run it.

    wow... it thats possible 😀 :D: D

  • January 4, 2011 at 8:51 am

    #1269031

    Arash

    Should be possible... just include a @Domain parameter in your stored procedure definition and build that into the script that you already have - something like this snippet:

    SET @sql = 'CREATE LOGIN [' + @Domain + '\MyLogin] FROM WINDOWS'

    John

Viewing 14 posts - 1 through 13 (of 13 total)

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