1

Once a new table is created, we have to execute the same SQL code segment, that grants some users rights to the table and does similar maintenance. This code is identical for all cases, differing only in the table name.

I wanted to write a stored procedure to abstract the entire set of statements into one stored procedure call, but I don't understand how to do it. For example, here is a simple one I created:

CREATE PROCEDURE dbo.spGrantTableRights
       @TableName varchar(255)
AS
BEGIN
    DECLARE @Name varchar(255)
    SELECT @Name = name
      FROM sysobjects
     WHERE name = @TableName

    GRANT SELECT            ON @Name TO mainUserName
    GRANT INSERT            ON @Name TO mainUserName
    GRANT DELETE            ON @Name TO mainUserName
    GRANT UPDATE            ON @Name TO mainUserName
    GRANT DELETE STATISTICS ON @Name TO mainUserName
    GRANT TRUNCATE TABLE    ON @Name TO mainUserName
    GRANT UPDATE STATISTICS ON @Name TO mainUserName
    GRANT TRANSFER TABLE    ON @Name TO mainUserName
END

But passing a string table name does not help -- I don't get a system object back. How do I convert this string into the system object? Or is there a different better way to do this altogether?

Note I understand dynamic SQL will work, but it makes code both ugly and unreadable. Is there a standard way to deal with this problem outside of the dynamic SQL framework?

2

As @Shaulinator stated in his answer; the only way to accomplish this is to use dynamic SQL.

Having said that, I would caution against applying the dont-repeat-yourself mantra too heavily to database code where it doesn't necessarily belong. I realize your question is about Sybase ASE (now SAP ASE); however since SQL Server and ASE share a very common language I would point you to the dynamic SQL "bible" by Erland Sommarskog, The Curse and Blessing of Dynamic SQL

I would recommend templating your permissions code, such that you do a search-and-replace on the object name whenever you add a new object. Retain the modified template code in your version control system; most likely in the same place as the object code itself.

|improve this answer|||||
  • @Shaulinator did not say this is the only way to do it, just that it is a way. If you are saying there is no other way, ok. – gt6989b Apr 3 '17 at 17:12
  • There is no way to specify a string-based argument in a GRANT statement. Please do think about not doing this with dynamic SQL; or at least don't make everything you can into dynamic SQL. I can see this procedure getting out of hand with options and code-paths that do various things that will only make the dynamic SQL incredibly hard to debug. – Max Vernon Apr 3 '17 at 17:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.