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.
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
EXECline 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;