Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

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;
share|improve this answer
    
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
show 2 more comments

Your Answer

 
discard

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

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