Sql – How to create a new database schema in SQL Express and give access to any user account of the current machine

sqlsql-server-2008

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.

Best Answer

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.

Related Topic