Role to enable the adding of users

[daveonion]

Hi, i was wondering if anyone could tell me what server role i should give a user to enable them to add user's to the server using the default schema (dbo).

Thanks in advance

 

[mrdenny]

To create logins on the server the user will need to be a member of the security admin fixed server role.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[daveonion]

Hi,
I have added the user to the security admin role however when they create the login it won't use the default schema of dbo instead it creates a new schema consisting of the user login name ie domain/user

Please advise

thanks

 

[mrdenny]

To create users within each database the user needs to be a member of the db_accessadmin fixed database role. You can then specify which default schema the user should use. You can't do this from the create login screen where you create the login. You have to open each database and create the user manually, or edit the user after putting the user in the database and change the default schema.

Or setup a SQL Script to handle all the creates, and use the T/SQL script.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog