sql server user permissions

Hi - I have couple of databases configured as below

DBA1
Users:   admin1, admin2, admi3

DBB1
Users: user1, user2, user3

now I want to access data from DBA1, so I gave user1 read only permissions to DBA1
and i got the issue fixed, but that DBA1 refreshes every day and i need to assign that user1 read only permissions daily.

so I worked around and created a view in DBB1 for table in DBA1.
but i run the query i get error saying user1 does not have access to DBA1.table1

I thought when i created a view in second database DBB1 for a table in first database DBA1 my issue will be fixed.
but it did not - so how can I fix this issue.

Thanks,
shragiAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
jogosConnect With a Mentor Commented:
All the user1-fix access is only necessary when that user is also existing and has reason to exist on the other location.....  and  then we already guess that database is comming from other location.
Still answer on question 'what do you mean by refresh db'  is key on how to get the security in order.    When db is reacreated and gets filled during day then the create of database must be alteredto add security for user1.  

And Scott explaned what I meant with compatible users, it is the sid of the login that mus be the same for not having to fix the security between instances.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Every time you refresh DBA1 you need to run the following command:
EXEC sp_change_users_login 'Auto_Fix', 'user1'

Open in new window


It's only a matter of orphaned users and that SP will fix it for you.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
Forgot to tell you that the command should be executed on the DBA1 context:
USE DBA1
GO
EXEC sp_change_users_login 'Auto_Fix', 'user'

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
shragiAuthor Commented:
well I am not doing the DBA1 refresh, it is done by admin's and I am not sure how will they react to that.
So is there another way, I mean can't I give some explicit permissions to user1 saying don't ask about permissions that needed to run the view in second database.
0
 
shragiAuthor Commented:
also  sp_change_users_login  will be removed in future versions of sql server.
https://msdn.microsoft.com/en-us/library/ms174378.aspx
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
can't I give some explicit permissions to user1 saying don't ask about permissions that needed to run the view in second database.
No. Problem is when the 2nd database is refreshed (DROP & CREATE) for him don't exist a 2nd database anymore and that's why the user got orphaned and that SP was built by Microsoft thinking on these kind of situations.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
also  sp_change_users_login  will be removed in future versions of sql server.
Don't worry with that. Only when they say that is deprecated is when you should think in something else. Even when it's deprecated they only get rid of it two versions after. So imagine that will be deprecated in SQL Server 2016, so only when SQL Server 2020 will be released that won't work (counting that every 2 years we'll have a new version of SQL Server).

Anyway they should replace that SP with some other one ;)
0
 
jogosCommented:
What do you mean by refresh DBA1?

If it is comming from another database (restore,  recreate...)  then the security is used from the source. So your authorisation must be fixed ther so with each refresh it  is present.   But then sitll the action Victor proposed can be necessary.

Best is to add the give security-action to the refresh process.
"and I am not sure how will they react to that." .. that is security or it is allowed and you must take actions to keep it allive or it is not allowed.  Seems to me a exact task for a dba to solve.
0
 
shragiAuthor Commented:
I used it and not sure what it did.

USE DBA1
GO
EXEC sp_change_users_login 'Auto_Fix', 'user', NULL, 'pwd'
GO

I executed the above sp and did not see user1 in the DBA1/Security/Users in SSMS
so what exactly did it do ?

Thanks,
0
 
jogosCommented:
also  sp_change_users_login  will be removed in future versions of sql server.

USE DBA1
GO
Alter user  user1 with login =user1

Open in new window


And what it does.  Link the security given to user1 in your DBA1 again to the login in your instance. When is that needed? When you  restore a database to another instance and want the security for that to work on the new instance.    Not always needed when the logins are compatible.

And that is why I said put it in the source and maybe the action Victor propossed can still be needed.   Without puting it in the original db then the action does nothing because user1 is not found.
0
 
Scott PletcherConnect With a Mentor Senior DBACommented:
If the problem is that the user is out of sync, you still shouldn't use that procedure any more as it is obsolete.  But even if you do use it, you should NEVER use "Auto_Fix", use "Update_One" instead.  I don't know of any DBA that wants SQL automatically creating logins, let alone for obsolete users(!).

The most permanent fix would be to adjust that's login sid on the current box to be the same as it is on the instance that DBA1 comes from.  It's a bit more work up front, but then it works from then on without running any additional commands.
0
All Courses

From novice to tech pro — start learning today.