sp_addlogin,sp_adduser,sp_addrolemember

  • MS SQLServer 2014

    Correct me if I am wrong : I have to run 3 stored procedures to add a user to server and database ?

    However, when adding a user :

    Data_Module.UniStoredProc2.Connection :=Data_Module.UniConnection1;

    Data_Module.UniStoredProc2.StoredProcName:='sp_adduser';

    Data_Module.UniStoredProc2.ParamByName('loginame').AsString := cxTextEdit1.Text;

    Data_Module.UniStoredProc2.ParamByName('name_in_db').AsString := cxTextEdit1.Text;

    Data_Module.UniStoredProc2.PrepareSQL;

    Data_Module.UniStoredProc2.ExecProc;

    This works ok (user gets added) but I end up with an added 'Owned schema' with users name as its name as well.

    Is this normal or is there a way to avoid it ?

  • Senchi (12/14/2016)


    MS SQLServer 2014

    Correct me if I am wrong : I have to run 3 stored procedures to add a user to server and database ?

    However, when adding a user :

    Data_Module.UniStoredProc2.Connection :=Data_Module.UniConnection1;

    Data_Module.UniStoredProc2.StoredProcName:='sp_adduser';

    Data_Module.UniStoredProc2.ParamByName('loginame').AsString := cxTextEdit1.Text;

    Data_Module.UniStoredProc2.ParamByName('name_in_db').AsString := cxTextEdit1.Text;

    Data_Module.UniStoredProc2.PrepareSQL;

    Data_Module.UniStoredProc2.ExecProc;

    This works ok (user gets added) but I end up with an added 'Owned schema' with users name as its name as well.

    Is this normal or is there a way to avoid it ?

    if you want to avoid creating the schema as well, you can use the CREATE USER statement instead of SP_ADDUSER

    https://msdn.microsoft.com/en-us/library/ms173463.aspx

  • There is no stored procedure for creating user under System Stored procedures

  • Don't use those, they're deprecated and old (SQL 2000). Use the DDL statements instead

    CREATE LOGIN ...

    CREATE USER ...

    ALTER ROLE ...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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