Richardsoet
asked on
Permission SQL
The below script created a user SQL\srvSSISAcc on all my databases and granted datawriter and datareader to all databases but the use is not created in the security login, I need this name to be created on Security login before granting user mapping to all datbases
USE master
GO
DECLARE @DatabaseName VARCHAR(32)
DECLARE @SQL NVARCHAR(max)
DECLARE @User VARCHAR(64)
SET @User = '[SQL\srvSSISAcc]' --Your User
DECLARE Grant_Permission CURSOR LOCAL FOR
SELECT name FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN Grant_Permission
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @SQL = 'USE '+ '[' + @DatabaseName + ']' +'; '+ 'CREATE USER ' + @User +
'FOR LOGIN ' + @User + '; EXEC sp_addrolemember N''db_datareader'',
' + @User + '; EXEC sp_addrolemember N''db_datawriter'', ' + @User + ''
EXEC sp_executesql @SQL
PRINT @SQL
FETCH NEXT FROM Grant_Permission INTO @DatabaseName
END
CLOSE Grant_Permission
DEALLOCATE Grant_Permission
You are using a windows. Login versus SQL login?
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql
Not clear what you are asking here.
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-login-transact-sql
Not clear what you are asking here.
ASKER
I want to be able to add create user for login with default_schema = dbo to my existing script above as part of cursor loop
Please help
Please help
ASKER
As part of my cursor loop , I want to be able to create login for windows with default _DATABASE AS DBO OWNER IN ADDITION TO WHAT THE LOOP CURSOR IS DOING at the momnet
ASKER
add this function to my cursor so that any user SET @USER will be created with the below
CREATE LOGIN [<domainName>\<login_name>
GO
CREATE LOGIN [<domainName>\<login_name>
GO
You shoukd lay out your logic/process
The login has to be created first if not present
I am having difficulty understanding what your process is in terms of when it is trigger and by what.
You might dare better to use existing capabilities.
I.e. If using windows based credential,
Add a Windows security group.
In SQL server add this group and assign it the rights you want.
Repeat for the different schemes you might have.
This way, once you make a Windows account a member of one or several of these groups, the user will have the respective access rights on the SQL.
The creation of this sp, indicates that you 've go be through thus process on a regular basis.
The login has to be created first if not present
I am having difficulty understanding what your process is in terms of when it is trigger and by what.
You might dare better to use existing capabilities.
I.e. If using windows based credential,
Add a Windows security group.
In SQL server add this group and assign it the rights you want.
Repeat for the different schemes you might have.
This way, once you make a Windows account a member of one or several of these groups, the user will have the respective access rights on the SQL.
The creation of this sp, indicates that you 've go be through thus process on a regular basis.
ASKER
The above script is creating user for all databases with dbo owner ...I want the script to be able to add the same user to the security login as well with dbo owner, when checked securtity tab , login the user is not been created..
if you can add this for me as well to be able create user at the security login level
if you can add this for me as well to be able create user at the security login level
ASKER
want the set @user XXX\XXXX to be created at security login level as well instead of creating it on only the database level
Why not consider the suggestion I made, and do this once and be done with.
Add a login group, assign it rights, than use your script to set
Add create login after the set user but you have to check first whether the user exists to avoid getting an error.....
In the current,
Adding individual users as you gave seems extremely inefficient.
Presumably you have a similar script to remove a user from these databases when they are no longer active.
Add a login group, assign it rights, than use your script to set
Add create login after the set user but you have to check first whether the user exists to avoid getting an error.....
In the current,
Adding individual users as you gave seems extremely inefficient.
Presumably you have a similar script to remove a user from these databases when they are no longer active.
ASKER
Could you please modify my script to accomdate this changes please
Again: A user (per database) needs one login (per server).
Conclusion: You need to create one login before running your script. Thats it. Just create this user manually and then run your script..
Conclusion: You need to create one login before running your script. Thats it. Just create this user manually and then run your script..
I provided add the create login after you set the user.
ASKER
Please paste the section of the script that you amended to accommodate the creation of the user in the security ,login which after allow the user to be added to all databasesn as the dbo owner...
The script is not doing this bit creation of the user in the security ,login
The script is not doing this bit creation of the user in the security ,login
Just after your entry line 8: "SET @User=..."
On live 9
Create login @User from Microsoft
With default_database=
With default_language =
Will not check whether the user exist.when it tries to create. It will error out, but might continue with the processing.
Perhaps you are unwilling to grant the AD domain admins the responsibility of assigning users to groups that will do what I suggested which would manage access based on group membership.
On live 9
Create login @User from Microsoft
With default_database=
With default_language =
Will not check whether the user exist.when it tries to create. It will error out, but might continue with the processing.
Perhaps you are unwilling to grant the AD domain admins the responsibility of assigning users to groups that will do what I suggested which would manage access based on group membership.
@Arnold, domain group membership is imho not the problem. He wants simply to automate setting permissions for one service account to all databases on a server.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Have you created that login first?
User and Login are two separate, distinct objects. Logins are not automatically created, if they don't exists when trying to create a User and bind it to this non-existing Login.