Menu
I need to give a new login read access to all 300 databases on a server. How can I accomplish this without checking 300 checkboxes in the user mapping area?
853
Expand Security - Logins, then Right Click on login and then click Properties. Go to User Mapping tab and select the database on which you want to give permission and at bottom select db_datareader as shown below. Click Ok and you're done.
Login to SQL Server Management Studio. In Object Explorer on the left pane, expand the Databases folder and select the concerned database and navigate to the by expanding Security and Users folders. Right-click the User to which you want to GRANT or REVOKE the permissions.
For the existing view, you can go to the Properties of the view in SSMS, add users in the Permissions, and then grant select permission in the permissions list.
One way would be to Set "Results to Text" on the query menu in SSMS then execute the below.
It doesn't actually make the change but generates a script for you to review and execute.
SET NOCOUNT ON; DECLARE @user_name SYSNAME , @login_name SYSNAME; SELECT @user_name = 'user_name', @login_name = 'login_name' SELECT ' USE ' + QUOTENAME(NAME) + '; CREATE USER ' + QUOTENAME(@user_name) + ' FOR LOGIN ' + QUOTENAME(@login_name) + ' WITH DEFAULT_SCHEMA=[dbo]; EXEC sys.sp_addrolemember ''db_datareader'', ''' + QUOTENAME(@user_name) + '''; EXEC sys.sp_addrolemember ''db_denydatawriter'', ''' + QUOTENAME(@user_name) + '''; GO ' FROM sys.databases WHERE database_id > 4 AND state_desc = 'ONLINE' Or you could look at sys.sp_MSforeachdb as here or Aaron Bertrand's improved version here
If you are not seeing all of the characters when you run this, open the Query Options for Text and check the setting for 'Maximum number of characters displayed in each column'. Make sure this is set to a value large enough to display all characters.
103
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
