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];

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

  USE [your_database];
  CREATE USER [Domain\Account] FROM LOGIN [Domain\Account];
  • 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):
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;
  • I think the OP is asking how to give local users access, not necessarily database users. I might be reading that wrong though. – Thomas Stringer Aug 2 '12 at 15:11
  • @Shark I think you're right, I've made it more complete. – Aaron Bertrand Aug 2 '12 at 15:20
  • I agree with that answer. +1 – Thomas Stringer Aug 2 '12 at 15:21
  • I want all local users access rights to this a database. Can I do this using SSMS? – Cem Girit Aug 2 '12 at 16:16
  • Yes, those queries will run in SSMS. – Aaron Bertrand Aug 2 '12 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.