asked
Can we given select permission only inside the procedure,direct select not allow...?
Alter Proc ExecQry (@Qry varchar(max)) as begin if(select CHARINDEX('*',@Qry))=0 begin exec (@Qry) End Print 'Char * is not allowed in selection list' End --ExecQry 'select Name,EmailAddress from Operator' Actuall i want to execute every query from this proc, and want to given only execute rights to users, and also want stop all direct select command. Is it possible, Please help me out this..... should be allowed like :---ExecQry 'select Name,EmailAddress from Operator' Should not be allowed for :- select * from Operator and Eror:-Char * is not allowed in selection list
ASK SQL Server Central runs by you voting. For all helpful answers below, please indicate this by clicking on the thumbs up next to those answers. If any one answer lead to a solution, please indicate this by clicking on the check mark next that answer.
One of the advantages of stored procedures is that you can enable people to read tables without giving them direct access to those tables. But when you use dynamic SQL like this, the caller needs to have permission on the table. PS: This just seems such a bad idea. What do you hope to gain from it? Somebody will have to write the SQL to pass in anyway so you don't save coding time. And you'll have no protection from SQL injection unless you pass in strings that are parameterised queries, which are even harder to write.
Well you could use the string functions to inspect the SQL they pass in but whether it's a "SELECT *" is the least of your worries. You will have to grant the login(s) read permissions on all tables as a minimum. Do any of these users have more than read permissions? You are giving them a tool where they can do anything they want if they can get it past your checks. How would you guard against something like this?: SELECT Name FROM dbo.Customers WHERE Name LIKE 'Micro%'; DROP TABLE dbo.Customers; (see
https://www.xkcd.com/327/)
Actually i want to restrict the user to right query like "Select * from " means any query contains "*" should not be allowed. So to solve this purpose please help or share script to meet this requirement
Hi David Actually i want to restrict the user to right query like "Select * from " means any query contains "*" should not be allowed. So to solve this purpose please help or share script to meet this requirement
i want to stop only queries having * (star),by specifying the column name every one can fetch the data
You can sign stored procedure with a certyficate and that should do the trick, using that i've created a role that can execute a stored procedure but has no visibility on the tables used and no way selecting data themselves using SELECT statements. You can also use the option to impersonate a login and execute stored procedure with specyfic premissions but is less secure that signing with a certyficate. find more here: [
http://sommarskog.se/grantperm.html][1] [1]:
http://sommarskog.se/grantperm.html
If kurmar just want to prevent people writing SELECT *(star) there is another way, not the best and i wouldn't do it myself, but it would work, specyficly if you deny select on at least one table column for a user, that user won't be able to do a SELECT *(star) anymore on that specyfic table.
21 People are following this question.
