March 21, 2019 at 10:28 am
So let's say I have a database, ABC, where there is a user ABCDE, and I script out the user and get:USE [ABC]
GO
/****** Object: User [SMUNSON] Script Date: 3/21/2019 12:24:25 PM ******/
CREATE USER [ABCDE] FOR LOGIN [ABCDE] WITH DEFAULT_SCHEMA=[dbo]
GO
And I look at the server and see that the only logins are sa and another login that I know, but definitely NOT seeing ABCDE.
If I were to use that same script, modified to be a new user name, with login same as the user, and again, there is no login at the server level, what would happen? What would the result be, assuming I have the authority to execute the command ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 21, 2019 at 10:45 am
the result is you would get an error from the login not existing. If you need the user, you can always create the user without a login
create user [abcde] without login
March 21, 2019 at 10:48 am
Okay, but then why would the existing user like this script out that way? Did those logins perhaps "used to exist" ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 21, 2019 at 12:06 pm
sgmunson - Thursday, March 21, 2019 10:48 AMOkay, but then why would the existing user like this script out that way? Did those logins perhaps "used to exist" ?
It will still script orphans but that's also another one that's been a bit messed up in SSMS. It used to be it would script those out and add WITHOUT LOGIN. But if it was an orphan, it really wasn't a loginless user so that wouldn't be correct. I would guess it is an orphaned user. And you're using a version of SSMS without the issue of adding without login.
In order to tell the difference between an orphan and a loginless user, you need to get the datalength of the SID in database_principals. One of the MS blogs has the query for the two to determine loginless vs orphaned - check the comments:
Quick guide to DB users without logins in SQL Server 2005
sp_change_users_login does pick up the differences as it checks the length of the SID.
Sue
March 28, 2019 at 10:20 am
Okay, that's a good resource on users without logins. Thanks for that. Still not sure I understand the reason one would choose to use that ability, so maybe you can elaborate on that? Couldn't quite get that out of the content on that page.
However, now I need to manage my SSRS folders using AD groups, so I can create the login for the AD group at the server level, but which database on the SSRS server needs the user added to it? Or do I not create a user? Or do I add that user to the ReportServer database?
So if my AD group is domain\SSRS_GRPNAME, I create a server level login of [domain\SSRS_GRPNAME], but then where does the user get created? Or does it ?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2019 at 10:59 am
sgmunson - Thursday, March 28, 2019 10:20 AMOkay, that's a good resource on users without logins. Thanks for that. Still not sure I understand the reason one would choose to use that ability, so maybe you can elaborate on that? Couldn't quite get that out of the content on that page.However, now I need to manage my SSRS folders using AD groups, so I can create the login for the AD group at the server level, but which database on the SSRS server needs the user added to it? Or do I not create a user? Or do I add that user to the ReportServer database?
So if my AD group is domain\SSRS_GRPNAME, I create a server level login of [domain\SSRS_GRPNAME], but then where does the user get created? Or does it ?
It's good for differentiating the orphans and loginless users based on the length of the SID. I use loginless users for elevating privs and using execute as. It's just more secure. Those are considered a replacement for application roles also.
On the SSRS site, you don't need to do anything with the SSRS databases or server - unless the users are going to hit data sources that use databases on that server and it's set to use their credentials. It's just the data sources they may need access to (based on how those are setup to authenticate) or the reports themselves. If the data sources have some other credentials setup then the users just need access to the reports. So it's data sources and reports that you'd want to pay attention to for the permissions.
If you add a group, a user isn't created. The user just gets access through their permissions in that group.
Sue
March 28, 2019 at 12:01 pm
If the user is an active directory user, it's also possible that the user was created based on their membership in an active directory group that has a login. SQL Server will allow that setup and it does work properly, I've used it in a case where an entire group needs login to a database but maybe a specific user needs extra permissions that the rest of the group doesn't.
March 28, 2019 at 12:27 pm
Okay, so to mash together Sue's input and Chris's, I can just create the login for the domain group on the SSRS Server, and then apply permissions to that login to the SSRS folders, and that should work, right?
EDIT: Once they get to the report, the data sources each have a specified set of credentials.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2019 at 2:14 pm
You may need to give read-only rights on the data sources (I think I remember having to do this on a SharePoint embedded instance), but it should work. we have many groups that have access to reports, but no access to the underlying database(s)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply