Sql-server – can I create an SSRS group without adding the group to the box itself and without using Active Directory

sql-server-2008ssrs

I want to know if it is possible to create a group "internal" to SSRS without having to add the group to the ReportServer box itself.

I do not have permissions to the box to create groups and/or add users to existing groups. I know that this is a way (the only way?) to have groups that can be assigned permissions in the SSRS interface.

I do have all the permissions I need within the SSRS interface. I have the System Administrator System Role on the ReportServer instance, as well as full sysadmin rights to the ReportServer database.

I am trying to simplify our security structure and it would be peachy if I could create a group, assign users to that group, and then permissions to the group. But all I seem to be able to do is assign permissions to the groups set up on the box.

Any ideas?

EDIT: Active Directory groups are not an option for me. 🙁

Best Answer

There are a couple of courses of action for you:

If you're friendly with your Windows Admin, I'd thoughtfully think out some AD Groups and then present them to the admin and ask them to be created (in whatever workflow you have).
From there you can assign permissions on the individual folders by these groups. Click on the folder, then click on Properties.

enter image description here

On the left you will see "Security," click that. Then click "Edit Item Security." You may get a dialog that tells you that the item currently inherits from parent. You may need to wait for a bit before this completes, but be patient.

enter image description here

If successful you will have checkboxes next to the principles already with access. From here choose "New Role Assignment"

enter image description here

Now you can add your AD Group in the next page and assign it the permissions necessary. Try with DOMAIN\GroupName

enter image description here

Obviously this will be much easier if you have AD groups, but if you can't get that, then you can do this with individual domain users. That would be a management nightmare?

Related Solutions

Sql-server – How to assign an entire Active Directory group security access in SQL Server 2008

Set the AD group as a login. And "login" means server level login not the AD concept of user/login. In SQL Server speak, this is a server level principal
Create a mapped user in. You shouldn't really permission a user directly on tables. And "user" means database user not the AD concept of user: in SQL Server speak, this is a "database level principal"
Add user to role (also a "database level principal")
GRANT permissions to the roles on the tables (a table or proc etc is a "securable")

Sample script

USE master;
GO
CREATE LOGIN [MYDOMAIN\APPLICATION SUPPORT] FROM WINDOWS;
GO
USE mydb;
GO
CREATE USER [MYDOMAIN\APPLICATION SUPPORT] FROM LOGIN [MYDOMAIN\APPLICATION SUPPORT];
GO
CREATE ROLE rSupport;
GO
EXEC sp_addrolemember 'rSupport', 'MYDOMAIN\APPLICATION SUPPORT';
GO
GRANT SELECT, INSERT,UPDATE, etc ON Mytable TO rSupport;
GO

sp_addrolemember is deprecated starting with SQL Server 2012, where ALTER ROLE should be used instead.

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

You need to look at the Effective Permissions http://msdn.microsoft.com/en-us/library/ms178358.aspx

I've never understood why there isn't a version of fn_my_permissions http://msdn.microsoft.com/en-us/library/ms176097.aspx which simply takes a user.

To do that you have to EXECUTE AS the other user: http://sqlarea.blogspot.com/2008/04/fnmypermissions.html

Best Answer

Related Solutions

Sql-server – How to assign an entire Active Directory group security access in SQL Server 2008

Sql-server – Trying to better understand how SELECT rights are managed in a/the SQL Server db

Related Question