|
|
You're in the
sql server new users
group:SQL Server Management
sql server new users:
We've just started migration from SQL Server 2000 to 2005.
One question that's come up, and I can't seem to find the answer.. Using the Management Studio. We can answer the question "What are the user permissions on this table?" by getting the properties of the table and selecting Permissions. How do we do the converse? ie, How do we answer the question "What permisions does this user have?". Selecting the properties of the user either in Database/Security or Server/Security shows a menu item called Securables which is always empty and a geyed-out Effective Permissions box.. On 2000 it was easy to see exactly what a user could see and do. Barry G Freeman DBA
Hi,
On the "General" tab of the user properties, you can see which databases the user has access to and which role the user has in the database. I don't know much about the "Securables" tab since I've never used it. Another way of looking it up, is to go to the Security folder on the server. Here you can drill down to the logins and look up the permissions on each login. You can also look up the Server Roles and check which logins are member of which server role. -- Regards Steen Schlüter Persson Database Administrator / System Administrator [quoted text, click to view] b.freeman@nospam.net wrote:
> We've just started migration from SQL Server 2000 to 2005. > > One question that's come up, and I can't seem to find the answer.. > > Using the Management Studio. > > We can answer the question "What are the user permissions on this > table?" by getting the properties of the table and selecting > Permissions. > > How do we do the converse? ie, How do we answer the question "What > permisions does this user have?". Selecting the properties of the user > either in Database/Security or Server/Security shows a menu item > called Securables which is always empty and a geyed-out Effective > Permissions box.. > > On 2000 it was easy to see exactly what a user could see and do. > > Barry G Freeman > DBA
On Mon, 23 Apr 2007 15:18:19 +0200, "Steen Schlüter Persson (DK)"
[quoted text, click to view] <steen@REMOVE_THIS_asavaenget.dk> wrote: >Hi, > >On the "General" tab of the user properties, you can see which databases >the user has access to and which role the user has in the database. I >don't know much about the "Securables" tab since I've never used it. >Another way of looking it up, is to go to the Security folder on the >server. Here you can drill down to the logins and look up the >permissions on each login. You can also look up the Server Roles and >check which logins are member of which server role. Yep, I can see what Role the user has, but again, I cannot see what permissions the role has. It was so easy in 2000... right-click the user, select Manage Permissions, tick the box "Only show permissions for this user" (or whatever it was called". Doesn't seem to be ANY way in 2005 to do this simple thing. -- Bazz Moonglade - Horde Zunafrex - 65 Troll Hunter Malsis - 66 Orc Warlock Zasufex - 60 Troll Mage Malshakh - 58 Orc Warrior Verdant - 40 Troll Shaman Dhusidhe - 52 Blood Elf Rogue Kaitleen - 27 Blood Elf Hunter Théa - 22 Blood Elf Priest Belafon - 20 Blood Elf Paladin Moodglade - Alliance
Hi, I'm not sure that I follow you. If you right click on the actual database role, and go to securables, you can see which security settings has been defined for this particular role. I can't in details remember how it was in SQL2000, but I don't find more complicated than before. .....well I actually just tried opening Enterprise Manager... It's slightly different in Mangement Studio but I think the number of clicks is more or less the same for the most look-ups. -- Regards Steen Schlüter Persson Database Administrator / System Administrator [quoted text, click to view] Barry Freeman wrote:
> On Mon, 23 Apr 2007 15:18:19 +0200, "Steen Schlüter Persson (DK)" > <steen@REMOVE_THIS_asavaenget.dk> wrote: > >> Hi, >> >> On the "General" tab of the user properties, you can see which databases >> the user has access to and which role the user has in the database. I >> don't know much about the "Securables" tab since I've never used it. >> Another way of looking it up, is to go to the Security folder on the >> server. Here you can drill down to the logins and look up the >> permissions on each login. You can also look up the Server Roles and >> check which logins are member of which server role. > > Yep, I can see what Role the user has, but again, I cannot see what > permissions the role has. > > It was so easy in 2000... right-click the user, select Manage > Permissions, tick the box "Only show permissions for this user" (or > whatever it was called". > > Doesn't seem to be ANY way in 2005 to do this simple thing.
On Mon, 23 Apr 2007 16:02:25 +0200, "Steen Schlüter Persson (DK)"
[quoted text, click to view] <steen@REMOVE_THIS_asavaenget.dk> wrote: >Hi, > >I'm not sure that I follow you. If you right click on the actual >database role, and go to securables, you can see which security settings >has been defined for this particular role. I can't in details remember >how it was in SQL2000, but I don't find more complicated than before. > >....well I actually just tried opening Enterprise Manager... It's >slightly different in Mangement Studio but I think the number of clicks >is more or less the same for the most look-ups. Well, I just tried that, and I get a blank Securables window. Apart from that, we're generally not using Database roles or Schemas. Reason being, we're using Active Directory groups that have been set up to control access to our file system and these translate pretty well into who needs what access to databases. So we add the AD groups as a login to the SQL Server, and grant the AD group permissions on the tables. Using Database roles seems a bit redundant. (Add group to sql server, add group to database role, give role permissions) We do use some roles, but these show a blank secuarables window. Also, we don't use schemas because some of our users use MS Access data projects and these don't work with schema'd databases. Barry G Freeman DBA
Hi, What do you expect to see when you open the Securables Window? I've just tried to check it out on one of my servers and compared it in Enterprise Manager and Management Studio and I can't see the big difference. Of course it appears a little bit different but the info it shows is more or less the same. As an example I have a NT group that is added as a login to the server. This group has access to one database where it is member of the public role and a user defined role. If I check this in EM by going to Database - Users and then open the properties, I can see that the user is member of the 2 roles. If I then click on "Permissions" I can then see all the objects where the user has specific permissions set. By default it shows all objects, but in this case when I click "Show only objects with permissions for this user" I get a blank list because no specific permissions has been set for this user. If I do the same in SSMS by going to Database - Security - Users and then check the properties for the user I get the same thing on the securables tab. The screen is blank because no specific permissions has been assigned to this user. The difference is of course that in EM it by default shows everything even though no specific permissions has been set where it only shows what has been assigned when you look it up in SSMS. I don't know if the above describes what you are seeing or not, but as I see it you only get a blank securables page when no specific permissions has been assigned to the user. -- Regards Steen Schlüter Persson Database Administrator / System Administrator [quoted text, click to view] b.freeman@nospam.net wrote:
> Well, I just tried that, and I get a blank Securables window. > > Apart from that, we're generally not using Database roles or Schemas. > > Reason being, we're using Active Directory groups that have been set > up to control access to our file system and these translate pretty > well into who needs what access to databases. > > So we add the AD groups as a login to the SQL Server, and grant the AD > group permissions on the tables. > > Using Database roles seems a bit redundant. (Add group to sql server, > add group to database role, give role permissions) > > We do use some roles, but these show a blank secuarables window. > > Also, we don't use schemas because some of our users use MS Access > data projects and these don't work with schema'd databases. > Barry G Freeman > DBA
On Tue, 24 Apr 2007 09:24:40 +0200, "Steen Schlüter Persson (DK)"
[quoted text, click to view] <steen@REMOVE_THIS_asavaenget.dk> wrote: >The difference is of course that in EM it by >default shows everything even though no specific permissions has been >set where it only shows what has been assigned when you look it up in SSMS. >I don't know if the above describes what you are seeing or not, but as I >see it you only get a blank securables page when no specific permissions >has been assigned to the user. Ok, I've created a new Active Directory user and added it to the Server Logins with a user mapping to the database, only adding to the public role. No specific permissions at this stage. As expected, we see a blank securables window when viewing the users properties/permissions. Now, On the User properties Securables window, I add one table, and give the user Select permission. Press OK. Looking at the Table properties, I can indeed see that the user has select permission. Going back to the Server/Security/Users list, I right-click the user, select properties, then Securables and it's blank. The user does have an explicit permission on the table but we cannot see this via the User object, only the Table object. This is the same whether I select User via Database/Security/Users or Server/Security Users. My connection to the server is System Admin, so thats not an issue... Also, the Effective Permissions button is either greyed out, or produces an error when clicked for AD users.. works fine for SQL Logins though. Thanks for taking the time to help... We're quite happy with Server 2005, but boy, some of the changes from 2000 are causing some headaches. I feel like a new admin all over again. Barry G Freeman DBA
Hi,
Ok - now I think I see what you mean (...sorry for being a bit slow..:-)...). I just think that SSMS doesn't work as you expect. The securables you see on the user properties (under Database-Security-Users) isn't the same as the properties you see for a Login. If you try to click "Add" in both places, you'll see that it's different objects you can add. I think it will require somebody that has a deeper knowledge in this area to explain why it's different, but maybe it's because it's Logins in one place and users in the other place. If I compare to how it works in EM, I don't see that huge difference. Here you also can't see the "securables" from the Login object, but I agree that you from here, if a user is member of a DatabaseRole, then you can see which permission this Role has which you can't in SSMS. You should also note that what you see in SSMS is depending on which SQL server version you are administering. When connected to a SQL2005 instance you have more options than when connected to a SQL2000 instance. -- Regards Steen Schlüter Persson Database Administrator / System Administrator [quoted text, click to view] b.freeman@nospam.net wrote:
> On Tue, 24 Apr 2007 09:24:40 +0200, "Steen Schlüter Persson (DK)" > <steen@REMOVE_THIS_asavaenget.dk> wrote: > >> The difference is of course that in EM it by >> default shows everything even though no specific permissions has been >> set where it only shows what has been assigned when you look it up in SSMS. >> I don't know if the above describes what you are seeing or not, but as I >> see it you only get a blank securables page when no specific permissions >> has been assigned to the user. > > Ok, I've created a new Active Directory user and added it to the > Server Logins with a user mapping to the database, only adding to the > public role. No specific permissions at this stage. > > As expected, we see a blank securables window when viewing the users > properties/permissions. > > Now, On the User properties Securables window, I add one table, and > give the user Select permission. Press OK. > > Looking at the Table properties, I can indeed see that the user has > select permission. > > Going back to the Server/Security/Users list, I right-click the user, > select properties, then Securables and it's blank. > > The user does have an explicit permission on the table but we cannot > see this via the User object, only the Table object. This is the same > whether I select User via Database/Security/Users or Server/Security > Users. > > My connection to the server is System Admin, so thats not an issue... > > Also, the Effective Permissions button is either greyed out, or > produces an error when clicked for AD users.. works fine for SQL > Logins though. > > Thanks for taking the time to help... We're quite happy with Server > 2005, but boy, some of the changes from 2000 are causing some > headaches. I feel like a new admin all over again. > Barry G Freeman > DBA
On Tue, 24 Apr 2007 12:15:26 +0200, "Steen Schlüter Persson (DK)"
[quoted text, click to view] <steen@REMOVE_THIS_asavaenget.dk> wrote: >Hi, > >Ok - now I think I see what you mean (...sorry for being a bit >slow..:-)...). I just think that SSMS doesn't work as you expect. The >securables you see on the user properties (under >Database-Security-Users) isn't the same as the properties you see for a >Login. If you try to click "Add" in both places, you'll see that it's >different objects you can add. I think it will require somebody that has >a deeper knowledge in this area to explain why it's different, but maybe >it's because it's Logins in one place and users in the other place. >If I compare to how it works in EM, I don't see that huge difference. >Here you also can't see the "securables" from the Login object, but I >agree that you from here, if a user is member of a DatabaseRole, then >you can see which permission this Role has which you can't in SSMS. >You should also note that what you see in SSMS is depending on which SQL >server version you are administering. When connected to a SQL2005 >instance you have more options than when connected to a SQL2000 instance. Yes, we're connecting only to 2005 with SSMS.. (From Database-Security-Users). If we add the table in Securables again, it correctly shows the 'Select' permission granted. But there are hundereds of tables and we'd have to add each one, then slowly step down the list wating for the permissions view to refresh for each one. Hardly ideal. I think I'll post this on the managed newsgroups.. (I have an MSDN subscription), so maybe I can get some sort of definitive answer from an MS person. I'll report back...:) Barry G Freeman DBA
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |