QOD 7th Jan 04
-
Ooh sneaky! I can't find reference to the HAS_DBACCESS call anywhere in the help or books online (SQL 7.0 - still
) How many other 'undocumented' calls are there?
-
There's a lot of undocumented stored procedures and DBCC commands. But what I found interesting about the QOD is that NONE of the answers met the question:
-------------------
What T-SQL could you run while logged in as the user in question to determine if the login has access to the Northwind2 database?
If you receive a 1 as a result when running SELECT HAS_DBACCESS('Northwind2')
If you receive a record from running the following query : SELECT name FROM SYSDATABASES WHERE name = 'Northwind2'
If you receive a 1 as a result when running sp_hasaccess 'Northwind2'
If there is a 1 in the permission column when running the following query:
SELECT permission FROM syspermissions where db_name = 'Northwind2' and username = 'UserName'
None of the above will work.
-------------------------
Notice the question says WHAT TSQL COULD YOU RUN. Not WHAT RESULTS WOULD YOU GET. That makes the answer None of the above since those are results and not TSQL (I know, I'm being picky). The answer REALLY should have been:
F. SELECT HAS_DBACCESS('Northwind2')
-SQLBill
-
HAS_DBACCESS is in the SQL Server 2000 BOL.
-SQLBill
-
SQLBill, The answer is still valid. It asked what SQL you could run. You could run HAS_DBACCESS to determine if you had access to the database, then handle the result as needed. The explanation and each answer jkust went as far as to tell you what result you would be looking for if you had access.
-
Well SELECT HAS_DBACCESS('Northwind2') T-SQL just returns the info of whteher the current user has access to Northwind2 database or not. But the question was asking, if we were a programmer how could we determine whether a specific user has access. First we should have figured out the user info and then check whether user has right or not.
-
I may be missing something here, but I fail to see the utility in this approach. If a user does not have db access, I will know it through error handling; I trap the error on the application level and proceed. Can't do a whole lot with it on the MSSQL side, since the user can't access the db (unless the user has rights to execute sp_adduser to a db, which I suppose could happen). I'm scratching my head on this one.
-
You could do error checking within a stored procedure that is called from an application error. The only time I would see this as being a valid way to check would be if the user was connected to a database, lets say reportingmart1, and they needed additional data.
Try this scenario:
The user is pulling up a static report populated from a table inside of reportingmart1. They would like more detailed information. The detailed information is held in the ReportingDetail database. The user account may or may not have access to the ReportingDetail database, So you would put a check in a stored procedure, let's call it rpt_detail_GetDetailedData, where you would perform a check for permissions:
DECLARE @rtn int
Select @rtn=HAS_DBACCESS('ReportingDetail')
IF @rtn <> 1 ...
BEGIN
...
END
Just one scenario I can see using this. Although I would just do it on the application layer rather than all in SQL.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply