Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Mixed Mode / SQL Authentication is disabled.

Only Windows Authentication is available.

A database/schema has been created in the default instance (.\SQLEXPRESS) and is named "MyDatabase".

Using a SQL script that will be run by an administrator using the command line interface, I want to give full access to every user account of the machine (excluding guest account) to that schema.

Thanks a lot in advance.

share|improve this question

1 Answer 1

First you need to tell the SQL Server to allow everything to be able to log into the server.

CREATE LOGON [YourMachine\Users] FROM WINDOWS;
GO

Then go into the database and setup a user based on that login.

Use YourDatabase
GO
CREATE USER [YourMachine\Users] FOR LOGIN [YourMachine\Users]
GO

Then you can add the new user to the dbo role.

EXEC sp_addrolemember @rolename='dbo', @membername='YourMachine\Users'
GO

All together the script will look something like this.

CREATE LOGON [YourMachine\Users] FROM WINDOWS;
GO
Use YourDatabase
GO
CREATE USER [YourMachine\Users] FOR LOGIN [YourMachine\Users]
GO
EXEC sp_addrolemember @rolename='dbo', @membername='YourMachine\Users'
GO

Just change YourMachine to be your computer's name and Yourdatabase to be your database name.

share|improve this answer
    
can I replace YourMachine by . ? –  Pierre Jul 31 '10 at 9:14
    
Msg 15401, Level 16, State 1, Line 1 Windows NT user or group 'PC144\Users' not found. Check the name again. Msg 15401, Level 16, State 1, Line 1 Windows NT user or group 'PC144\Users' not found. Check the name again. Msg 15014, Level 16, State 1, Procedure sp_addrolemember, Line 36 The role 'dbo' does not exist in the current database. –  Pierre Jul 31 '10 at 9:17
    
No, you can't use ".". Check to make sure that the local group Users does exist. It might have been renamed. –  mrdenny Aug 5 '10 at 21:08

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.