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
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.
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: [