x

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?

more ▼

asked Oct 15, 2012 at 01:05 PM in Default

askmlx121 gravatar image

askmlx121
2.5k 64 74 77

Great thanking you.......... can we assign one by one.........?

it is time consuming........

any other way to reduce the time..........
Oct 15, 2012 at 01:42 PM askmlx121
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

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
more ▼

answered Oct 15, 2012 at 01:41 PM

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

(comments are locked)
10|1200 characters needed characters left
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
more ▼

answered Oct 15, 2012 at 01:21 PM

Fatherjack gravatar image

Fatherjack ♦♦
42.6k 75 79 108

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 15, 2012 at 01:28 PM

Cyborg gravatar image

Cyborg
10.6k 36 40 45

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 15, 2012 at 07:28 PM

ThomasRushton gravatar image

ThomasRushton ♦
33.9k 18 20 44

Exactly what I thought when I read the header
Oct 16, 2012 at 07:44 AM DirkHondong
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1947
x86

asked: Oct 15, 2012 at 01:05 PM

Seen: 712 times

Last Updated: Oct 16, 2012 at 07:44 AM