Giving all permissions to all users for a database in SQL Server

Question

Since the database is created under one user's log in, other users using this computer cannot access this particular database. How can I grant the same privileges to all users who log in to this computer for this database? Step by step instructions through SSMS or by a script will be appreciated greatly. Thank you

Answer 1

You want local Windows users to have the same permissions as the database owner of a specific database? How about:

• Manually create logins for each Windows account:

-- repeat this whole block for each user account

USE [master];
GO
CREATE LOGIN [Domain\Account] FROM WINDOWS;
GO

  -- repeat just this portion for each database that account should belong to

  USE [your_database];
  GO
  CREATE USER [Domain\Account] FROM LOGIN [Domain\Account];
  GO

• Add the users to the dbo role of each database to which you've added a user account above, using dynamic SQL (uncomment the EXEC line when you think it looks right):

DECLARE @sql NVARCHAR(MAX);
SET @sql = N'';

SELECT @sql = @sql + N'
  EXEC sp_addrolemember N''db_owner'', N''' + name + ''';'
FROM sys.database_principals
WHERE principal_id BETWEEN 5 AND 16383
AND [type] IN ('U', 'S');

PRINT @sql;
--EXEC sp_executesql @sql;