question

askmlx121 avatar image
askmlx121 asked

when new dabase created how can I gave the access permission to all users in server?

Hi I have created new database in my server. in server i have 30 users how can I gave the access permission to all users in server? is any query to give permission in single transaction?
sql-server-2005server
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

askmlx121 avatar image askmlx121 commented ·
Great thanking you.......... can we assign one by one.........? it is time consuming........ any other way to reduce the time..........
0 Likes 0 ·
Usman Butt avatar image
Usman Butt answered
First of all I would not blindly give all the users permissions in one go without reviewing it. It could lead to a very bad situation. So for the sake of less work and simplicity but with a better approach could be to have a batch printed which would be run after reviewing thoroughly. Following is a snippet which would add all SQL Logins which are not disabled to db_datareader database role. (CURSOR used intentionally, so modification could be easy) DECLARE @SQLLoginName sysname DECLARE ServerPrincipals CURSOR FOR SELECT [name] FROM sys.server_principals WHERE [type] = 'S' AND [is_disabled] = 0 OPEN ServerPrincipals FETCH NEXT FROM ServerPrincipals INTO @SQLLoginName WHILE @@FETCH_STATUS = 0 BEGIN PRINT 'CREATE USER ['+ @SQLLoginName + '] FOR LOGIN ['+ @SQLLoginName + '] GO' PRINT 'EXEC sp_addrolemember ''db_datareader '', '''+ @SQLLoginName +''' GO' FETCH NEXT FROM ServerPrincipals INTO @SQLLoginName END CLOSE ServerPrincipals DEALLOCATE ServerPrincipals
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
For simplicity have all of your users put into an active directory security group then add that group to your server logins and then add that login as a database user with the permission you need it to have
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Cyborg avatar image
Cyborg answered
If you are worried about fixing orphan users after a restore of the database run the following query to fix them.

-- This will report the orphan users.
sp_change_users_login  @Action =  'action' 

-- This will fix the orphan users. e.g This mapps the user "MyUser" to the login "MyUser".
EXEC sp_change_users_login @Action = 'Update_One', @UserName = 'MyUser', @LoginName = 'MyUser'

-- Execute the following query in "Result to text mode" (ctrl+ T) to genrate script for fixing the orphan users. copy the results and run in new window.
SET NOCOUNT ON
USE MyDB
GO

select 'EXEC sp_change_users_login @Action = ''Update_One'', @UserName = '''+name+''', @LoginName = '''+Name+''''+CHAR(10)+'GO'
from sysusers
where issqluser = 1 
and   (sid is not null and sid <> 0x0)
and   (len(sid) <= 16)
and   suser_sname(sid) is null
order by name

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

ThomasRushton avatar image
ThomasRushton answered
If you want to have this setting applied for all databases which are still to be created on a server, you could try making the changes in the `model` database first, and *then* create the databases. The `model` database is a template for the creation of new SQL Server databases. For more information about what can (and cannot) be done here, see: http://msdn.microsoft.com/en-us/library/ms186388.aspx
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DirkHondong avatar image DirkHondong commented ·
Exactly what I thought when I read the header
1 Like 1 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.