Openrowset and Opendataset Without Sysadmin Rights

OpenRowSet and OpenDataSet without sysadmin rights

You don't necessarily require sysadmin rights, just the ADMINISTER BULK OPERATIONS permission (which is a server level permission but not quite sysadmin).

If this isn't an option, you can try setting up a linked server connection and follow the method in this post.

SQL Server 2008 OPENROWSET permission issue

From books online OPENROWSET (Transact-SQL)

The user requires the ADMINISTER BULK OPERATIONS permission.

And here is the entry for GRANTing it. It is a server-level permission, so yes, it is quite high.

To try lower permissions, you could create a standard linked server connection and add a login using

EXEC sp_addlinkedsrvlogin 'LINKSERVERNAME', 'false',
    'localuser', 'rmtuser', 'rmtpass'

There does not appear to be any specific permissions required to be granted, so if you set up a linked server, it is unwise to set it up with a generic linkedsrvlogin that maps to every local user. Set up specific local-remote mappings to control the access of a local user, through the linked-server, at the remote server (by the rmtuser login).

How to alter database on the linked server WITHOUT SYSADMIN rights?

After much research: This is not possible:(

How can I SELECT the names of all groups a person belongs to using a JOIN instead of IN?

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid = 1

---

Note: This is equivalent to your IN statement, because you specifically filter for only one person. If you filter for multiple persons, things are different. For example, assume that some person with pid=2 is also in group 1 and you do the following SELECT:

SELECT g.gid, g.name 
  FROM groups g INNER JOIN people_groups pg
       ON g.gid = pg.gid
 WHERE pg.pid IN (1, 2)

This would return group 1 twice (in contrast to your IN solution, which would return each group only once). To solve this, you need to add the DISTINCT keyword after the SELECT or add GROUP BY g.gid, g.name at the very end of the SQL. You should keep that in mind if you use this answer as a general rule to convert IN to JOIN.

---

Related Topics