Solved

sql server user permissions

Posted on 2015-01-21
11
176 Views
Last Modified: 2015-01-21
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,
0
Comment
Question by:shragi
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40562277
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
 
LVL 46

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 167 total points
ID: 40562282
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
 

Author Comment

by:shragi
ID: 40562309
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
 

Author Comment

by:shragi
ID: 40562312
also  sp_change_users_login  will be removed in future versions of sql server.
https://msdn.microsoft.com/en-us/library/ms174378.aspx
0
 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40562319
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 46

Expert Comment

by:Vitor Montalvão
ID: 40562332
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
 
LVL 25

Expert Comment

by:jogos
ID: 40562356
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
 

Author Comment

by:shragi
ID: 40562370
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
 
LVL 25

Expert Comment

by:jogos
ID: 40562371
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
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 166 total points
ID: 40562397
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
 
LVL 25

Accepted Solution

by:
jogos earned 167 total points
ID: 40562499
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select Statement 2 20
SQL Server - Slabs 9 35
T-SQL:  I Want "Summary"--Not "Detail" 6 19
SQL 2008 R2 calc date formula 3 18
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now