Excel report permissions

(0) Share

Report

Posted on by Chuck Evans

1,175

Are there any user friendly tools to add windows logins to SQL and windows users to rpt roles. Doing this in SQL Management Studio is a little cumberson with a large number of companies and users.

Thanks
Chuck

*This post is locked for comments

All responses (2)

Answers (1)

GPDavid 2,250 on at

Like (0)

Report

Re: Excel report permissions

Hi Chuck,

One thing that can potentially speed this up for you is to consider creating Windows groups for various groups of report users. You can then assign your users to these groups, and, in turn, assign these groups to the appropriate reporting roles. You still have to work through the SQL Management Studio windows, but you will, in theory, have far fewer groups to add to the roles than you would users.

Additionally, have you looked into using T-SQL to accomplish what you need? For example, you could create a list in Microsoft Excel and, in the first two columns, list the user name and the report role to which they should be assigned. Then, in the third column, use Excel concatenation to "build out" a SQL query using the sp_addrolemember query to look something like this:

sp_addrolemember 'rpt_accounting manager',':: user or group goes here ::'

msdn.microsoft.com/.../ms187750(v=sql.105).aspx

Autofill this down for every row you have created listing each user/group assignment. This will allow you to rapidly create a set of T-SQL queries that can then be executed against a particular GP database to grant this role assignment.

Please test this out in a test environment before utilizing on Production.

David
Verified answer

Community Member Microsoft Employee on at

Like (0)

Report

Re: Excel report permissions

The best way I've found is to have groups already defined in Active Directory and grant rights to those groups in SQL. When a new windows user is added to those groups they automatically get the appropriate permissions in SQL.