Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65

    Unanswered: [2005 Express]Add SPs to a Schema?

    I have SQL Server 2005 Express set up on a PC. I imported a backup of an MSDE database. All of the database objects are there. However, I need to assign permissions to my Stored Procedures. So, using Management Studio Express, I saw absolutely no way of adding a stored procedure to a schema or to a user. So I downloaded SQL Server 2005 Developer from my MSDN account. I installed the client tools to get the full version of Management Studio. Same thing. No way to add permissions to a SP. Some web articles say to right-click the SP and select Properties. However, there is no "Properties" in the right-click menu!!! There is only Rename, Delete, Modify, etc. How can something so easy with an old product be so difficult with a new one? Does anyone know how to accomplish adding permissions to SPs?

    I can right-click a table or a View and get the popup window with the list view and contents to set permissions to tables and views.
    Last edited by HardCode; 10-30-06 at 16:50.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    On my build of SSMS, I do have a properties context menu off of any sproc in OE, and when I click it I can add perms to the sproc. I have this build: 9.00.2047.00. which do you have?

    I am thinking perhaps this is due to a bad reaction between SSMS and SSMSE perhaps. EDIT: Is that redundant?

    If nothig else works, I would try unistalling them both and the reistalling only SSMS.

  3. #3
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    I have build 9.00.1399.00, so it is an older version. This build was from MSDN Subscription Downloads, on Disk 2 for SQL Server 2005 Developer. Do you know where I could download an update/the latest version?

    UPDATE: I uninstalled and then reinstalled all of the SQL Server Workstation stuff. Then, I installed an instance of SQL Server 2005 Developer on my test PC. Now, with SSMS, I can right-click and get "Properties" for stored Procedures in the SQL Server 2005 instance, but I still can't get the right-click "Properties" in the Express instance. Is this a limitation of Express?
    Last edited by HardCode; 10-31-06 at 12:35.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    Yes, I think this is a limitation of ssmse.

    The sql express GUI is very limited in what it does. it's meant to be a teaser to make it more attractive for people to start using sql server instead of mysql, but then upgrade to a pay version once they are addicted.

    EDIT: I am running sp1 - I think that's the difference between our two version numbers. You can download sp1 from here: http://www.microsoft.com/sql/sp1.mspx

  5. #5
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Oh, I see. Even the Management Studio updates are included in the SQL Server service pack, huh? I'll have to apply that. Thanks.
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    oh yea. UI devs put bugs in their code too.

  7. #7
    Join Date
    Oct 2004
    Location
    New Jersey
    Posts
    65
    Well, overall, I can get the Express instance to run the SPs from my VB.NET code. I used ALTER to put them into a new schema, and gave the user account that connects from my intranet app permissions on that schema. This allows the app to run the SPs. It's odd, what use is setting permissions on the specific SP (in the Developer instance, or course) when just using the schema allows execution? Is it to be more granular with permissions and, say, deny permissions on a specific SP within a schema to a schema user?
    Retired Moderator at Xtreme VB Talk, for all of your Visual Basic needs.

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    that's one of the points of using schemas.

    note that an explicit deny on a sproc should override any indirect grant (thru a schema).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •