This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(214.4, 91%, 30%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHA%3C/text%3E%3C/svg%3E)
Hello!
While creating reports using bcp utility -
*bcp "select Name, physical_name, state_desc, (size)8/1024, ... from msdb.sys.database_files ORDER BY Name" queryout C:\REPORTS\SQ\LUSERDB.txt -c -T
...
*bcp "select Name, physical_name, state_desc, (size)8/1024, ... from USERDB.sys.database_files ORDER BY Name" queryout C:\REPORTS\SQ\LUSERDB.txt -c -T
I noticed that if the account under which the command is run is te member of only the default public role, the command would not produce any output, although it seems the publc role membership should be enough to select from [].sys.database_files:
For selecting from system databases (master, msdb) I had to add the account to severadmin role, for selecting from any user databases - to sysadmin role.
**Q: Doesn't the picture above means the membership of the Public role alone should be sufficient for selecting from [].**sys.database_files ?
Regards,
Michael
When it comes to the catalog views there are several layers of security. Public membership is sufficient to query the view as such, but you need further permissions to see any actual data. Exactly what permissions depend on the view.
As one example run this in a database that has a couple of tables:
CREATE USER tillfällig WITHOUT LOGIN
CREATE TABLE tillfällig (a int NOT NULL)
GRANT SELECT ON tillfällig TO tillfällig
go
EXECUTE AS USER = 'tillfällig'
go
SELECT * FROM sys.tables
go
REVERT
go
DROP USER tillfällig
DROP TABLE tillfällig
The temporary user (tillfällig is Swedish for temporary) can only see the table tillfällig, not the other tables as it lacks permission to them.
Thank you all for your help!
Regards,
Michael
P.S. ...but it still seems strange to me that the odinary user does have (by default - via Public role) the select permission on all system databases (on sys.database_files table), but does not have that permission on user databases...
P.P.S. I only had to explicitly configure the user mapping for the SQL Server login (this is the TEST LAB!) and it worked:
The reason why I didn't do it earlier - I thought that the grey checkbox next to public that is set by default for a login for all databases is sufficient, but I was wrong.
The question that I should have asked in this case should read as follows:
Why does the login (let's say - contoso1\exchadmin) that does not have any explicit user mappings for system databases does nevertheless have select permission on [SYSTEM-DBs].sys.database_files???
Why does the login (let's say - contoso1\exchadmin) that does not have any explicit user mappings for system databases does nevertheless have select permission on [SYSTEM-DBs].sys.database_files???
That is because the guest user is enabled in these databases.
USE master
go
CREATE LOGIN tillfällig WITH PASSWORD = 'Tillfälligt lösenord'
go
EXECUTE AS LOGIN = 'tillfällig'
go
USE msdb
go
SELECT SYSTEM_USER, USER
USE master
go
REVERT
DROP LOGIN tillfällig
SYSTEM_USER returns the login on server level. USER returns the user on database level.
Erland, thank you very much - didn't think about it ("because the guest user is enabled in these databases")!!!
Regards,
Michael Firsov
Hi @Hram Admin
Regarding the Public Role, when a server principal isn't granted or denied specific permissions on a securable object, the user inherits the permissions granted to public on that object.
I noticed that if the account under which the command is run is te member of only the default public role, the command would not produce any output
The reason might be the public role does not include the necessary permissions for exporting data via the bcp utility.
Best regards,
Cosmog
If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".