Microsoft SQL Server: Setting Up Target User Privileges

To make an import into the Microsoft SQL Database you can utilize the existing user or create a new user and assign appropriate privileges to it.
If you decide to create a new user in Microsoft SQL you can follow the statements below:

create login tester with password = 'Pwd'
alter login tester with default_database = imp – Assign the default database 'test' for the login
use imp – We need to create a user in the required database, so we need to use it
create user test_user for login tester

After creating a new user you will need to assign CREATE privileges to it to be able to create all types of the objects in the target database. It can be done following the queries below:

GRANT ALTER ON SCHEMA::dbo TO test_user
go
grant create table to test_user
go
grant create view to test_user
go
grant create synonym to test_user
go
grant create procedure to test_user

Or instead of all the GRANT CREATE statements you can just add your user to a “db_ddladmin” role:

EXEC sp_addrolemember 'db_ddladmin', N'MyUserName'

Then you will need to add privileges to be able to load the data into the target MSSQL database.

grant select to test_user
go
grant insert to test_user
go
grant update to test_user
go
grant delete to test_user
go

You can just add your login to a sysadmin role right after the user creation:

EXEC master..sp_addsrvrolemember @loginame = N'tester ', @rolename = N'sysadmin'

More information about the roles and privileges that are already added in the role, you can see in the screenshot below:

If you have any other questions, please contact us: support@ispirer.com