sp_addlogin,sp_adduser,sp_addrolemember

Question

sp_addlogin,sp_adduser,sp_addrolemember

Senchi

Ten Centuries

Points: 1184
More actions
December 14, 2016 at 11:49 am

#332815

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 ?

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

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

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 1

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

Senchi Ten Centuries Points: 1184 More actions · Answer 2

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

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