June 21, 2007 at 1:47 pm
I want to creat a user that has the ability to do anything to a specific database. I know how to do this in mysql, but not mssql. Can you point me in the correct direction? I made a new database called test. How do i create a user for the test database that can add remove drop etc. This should be a sql server authentication as I want to access the db over the internet.
June 21, 2007 at 1:58 pm
Do you want to do it via code or GUI?
-SQLBill
June 22, 2007 at 12:50 am
You can use the GUI to create a new user assign permission. Start the management studio and navigate to security folder to play with the users, roles and permissions.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 22, 2007 at 5:30 am
In query analyser: (presuming your new login is called test, does not have to be)
use master
go
EXEC sp_addlogin 'test', 'make a password up'
exec sp_defaultdb 'test',test
use test
go
exec sp_grantdbaccess 'test','test'
exec sp_addrolemember "db_owner", "test"
---------------------------------------------
or in enterprise manager:
expand security
right click logins --> new login
enter login name
select sql server authentication
choose a password
set database field to test
under database access tab
tick database test
tick db_owner in 'permit in database role'
select OK
confirm password when prompted
---------------------------------------------------------------------
June 22, 2007 at 7:32 am
George --
Just FYI.. you are in a 2005 forum and remember that you can't use enterprise manager with sql 2005. also few syntax for logins have changed in sql 2005.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply